sql - oracle触发器上的编译错误

Question

我有一个具有雇员的表(oracle SQL)，其中每个雇员都有一个经理(表示为另一名雇员的fk)。我想创建一个触发器，当删除雇员时，在其经理字段中具有其键的所有雇员都将其经理更改为null(或-1，或某些保留值)。我在弄清楚当前代码出了什么问题时遇到了一些麻烦:

编辑:
我修复了我的大部分代码，但使用的方式却是错误的。我使用了建议的ON DELETE选项，现在一切正常。这是我的代码:

CREATE TABLE EmployeeA
(
    employeeID integer,
    firstName varchar (255),
    lastName varchar (255),
    phone integer,
    jobTitle varchar (255),
    payGrade integer,
    fk_EmployeeemployeeID integer,
    PRIMARY KEY(employeeID),
    FOREIGN KEY(fk_EmployeeemployeeID) REFERENCES EmployeeA (employeeID) ON DELETE SET NULL
);
INSERT INTO EMPLOYEEA VALUES (1, null, 'Powell', 0, 'President', 100, 1);
INSERT INTO EMPLOYEEA VALUES (2, null, 'Hicke', 0, 'Dean (Natural Science)', 100, 1);
INSERT INTO EMPLOYEEA VALUES (3, null, 'Fenves', 0, 'Dean (Engineering)', 100, 1);
INSERT INTO EMPLOYEEA VALUES (4, null, 'Porter', 0, 'Chairman (Computer Science)', 100, 2);
INSERT INTO EMPLOYEEA VALUES (5, null, 'Beckner', 0, 'Chairman (Mathematics)', 100, 2);
INSERT INTO EMPLOYEEA VALUES (6, null, 'Miranker', 0, 'Professor (Computer Science)', 100, 4);
INSERT INTO EMPLOYEEA VALUES (7, null, 'Mok', 0, 'Professor (Computer Science)', 100, 4);
DELETE FROM employeeA WHERE lastName = 'Porter'; 

Answer 1

您不想在这里使用触发器。使用外键的on delete属性。定义外键约束时，可以指定要在删除父行时将子行设置为NULL。

SQL> ed
Wrote file afiedt.buf

  1  create table employee2(
  2    employee_id number primary key,
  3    manager_id  number,
  4    employee_first_name varchar(30),
  5    constraint fk_manager_emp
  6      foreign key( manager_id )
  7      references employee2( employee_id )
  8      on delete set null
  9* )
SQL> /

Table created.

如果添加老板，经理(向老板报告)和员工(向经理报告)

SQL> insert into employee2( employee_id, manager_id, employee_first_name )
  2    values( 1, null, 'Boss' );

1 row created.

SQL> ed
Wrote file afiedt.buf

  1  insert into employee2( employee_id, manager_id, employee_first_name )
  2*   values( 2, 1, 'Emp1' )
SQL> /

1 row created.

SQL> ed
Wrote file afiedt.buf

  1  insert into employee2( employee_id, manager_id, employee_first_name )
  2*   values( 3, 2, 'Emp2' )
SQL> /

1 row created.

然后当我们删除经理时，员工的 manager_id自动设置为 NULL

SQL> delete from employee2
  2   where employee_first_name = 'Emp1';

1 row deleted.

SQL> select *
  2    from employee2
  3   where employee_first_name = 'Emp2';

EMPLOYEE_ID MANAGER_ID EMPLOYEE_FIRST_NAME
----------- ---------- ------------------------------
          3            Emp2