第 14 行错误：PL/SQL：忽略 SQL 语句

Question

我不断收到下面的错误，但我不明白为什么。我已将表格包含在代码中，然后还包含了出现错误的代码部分。

代码中的表格：

CREATE TABLE Employee (
    emp_id number(8) primary key,
    f_name VarChar2(20),
    l_name VarChar2(20),
    address VarChar2(40),
    city VarChar2(20),
    state Char(2),
    zip Number(5),
    phone_Number Number(10),
    email_address VarChar2(30),
    dept_id number(8) references department(Dept_ID),
    office_location VarChar2(30)
);

CREATE TABLE audit_detail (
    audit_detail_id number(8) primary key,
    field varchar2(30),
    old_value varchar2(30),
    new_value varchar2(30)
);

CREATE TABLE audit_trail (
    audit_trail_id number(8) primary key,
    user_id number(8)references user_id_table(user_id),
    table_name VarChar2(25),
    process VarChar2(25),
    emp_id number(8)references employee(emp_id),
    timestamp Date
);

Create table Department (
    dept_id number(8) primary key,
    dept_name VarChar2(20) not null,
    dept_start_date date
);

create table user_id_table(
    user_id number(8) primary key,
    emp_id number(8) references Employee(emp_id)
);

CREATE SEQUENCE audit_seq;

这是出现错误的部分，特别是 elsif 删除部分

CREATE OR REPLACE TRIGGER audit_employee 
AFTER INSERT OR UPDATE OR DELETE ON Employee
FOR EACH ROW
DECLARE
    timestamp DATE;
    session_user number(8); 
BEGIN
    timestamp := SYSDATE;
    session_user := USERENV('SESSION_USER');
    IF INSERTING THEN
        INSERT INTO audit_trail VALUES
        (Audit_seq.NEXTVAL, session_user, 'Employee', 'INSERT', :new.emp_id, timestamp);
    ElSIF DELETING THEN
        INSERT INTO audit_trail Values
        (Audit_seq.NEXTVAL, session_user, 'Employee', 'DELETE', :old.emp_id, timestamp);
    Else
        INSERT INTO audit_trail
        Values(Audit_seq.NEXTVAL, session_user, 'Employee', 'UPDATE', :old.emp_id);
        IF UPDATING ('f_name') THEN
            INSERT INTO audit_detail VALUES
            (Audit_seq.CURRVAL, 'f_name', :old.f_name, :new.f_name);
        ELSIF UPDATING('l_name') THEN
            INSERT INTO audit_detail VALUES
            (Audit_seq.CURRVAL, 'l_name', :old.l_name, :new.l_name);
        ELSIF UPDATING('address') THEN
            INSERT INTO audit_detail VALUES
            (Audit_seq.CURRVAL, 'address', :old.address, :new.address);
        ELSIF UPDATING('city') THEN
            INSERT INTO audit_detail VALUES
            (Audit_seq.CURRVAL, 'city', :old.city, :new.city);
        ELSIF UPDATING('state') THEN
            INSERT INTO audit_detail VALUES
            (Audit_seq.CURRVAL, 'state', :old.state, :new.state);
        ELSIF UPDATING('zip') THEN
            INSERT INTO audit_detail VALUES
            (Audit_seq.CURRVAL, 'zip', :old.zip, :new.zip);
        ELSIF UPDATING('phone_Number') THEN
            INSERT INTO Audit_detail VALUES
            (Audit_seq.CURRVAL, 'phone_Number', :old.phone_number, :new.phone_number);
        ELSIF UPDATING('email_address') THEN
            INSERT INTO audit_detail VALUES
            (Audit_seq.CURRVAL, 'email_address', :old.email_address, :new.email_address);
        ELSIF UPDATING('dept_id') THEN
            INSERT INTO audit_detail VALUES
            (Audit_seq.CURRVAL, 'dept_id', :old.dept_id, :new.dept_id);
        ELSIF UPDATING('office_location') THEN
            INSERT INTO audit_detail VALUES
            (Audit_seq.CURRVAL, 'office_location', :old.office_location, :new.office_location);

        End IF;
    End IF;
End;
/ 

Answer 1

删除后的 else 语句有 5 列，而audit_trail 有 6 列。

ElSIF DELETING THEN
    INSERT INTO audit_trail 
    Values ( Audit_seq.NEXTVAL, session_user, 'Employee'
           , 'DELETE', :old.emp_id, timestamp);
Else
    INSERT INTO audit_trail
    Values (Audit_seq.NEXTVAL, session_user, 'Employee'
           , 'UPDATE', :old.emp_id );

您还引用小写而不是大写的列，UPDATING ('L_NAME')。我不确定这是否会引起问题。

您正在执行大量隐式数字到字符的转换，这最终会让您出局，并且audit_trail 中的table_name 应该是30 个字符而不是25 个。为什么要冒险呢？