sql - Oracle中触发器语句的编译器错误

Question

我已经编写了CREATE TRIGGER，但是由于某种原因，我无法使它没有编译器错误。有任何想法吗？这是代码和错误消息:

CREATE OR REPLACE TRIGGER  ManagerDeleteTrigger
AFTER DELETE ON employee1
REFERENCING
OLD AS OldRow
NEW AS NewRow
FOR EACH ROW
WHEN(OldRow.FK_EMPLOYEEEMPLOYEEID != NewRow.FK_EMPLOYEEEMPLOYEEID)
UPDATE employee1 SET FK_EMPLOYEEEMPLOYEEID = null WHERE FK_EMPLOYEEEMPLOYEEID = OldRow.employeeID;

错误消息是:

Error(1,105): PLS-00103: Encountered the symbol ";" when expecting one of the following:
( begin case declare end exception exit for goto if loop mod    null pragma raise return select update while with    <an identifier> <a double-quoted delimited-identifier> <a bind variable>
 << continue close current delete fetch lock insert open rollback savepoint set sql execute 
commit forall    merge pipe purge The symbol "exit" was substituted for ";" to continue. 

编辑:这是我的问题的澄清。我正在使用以下语句创建表。每个员工都有一个经理(由FK代表)。

CREATE TABLE Employee1
(
    employeeID integer,
    firstName varchar (255),
    lastName varchar (255),
    phone integer,
    jobTitle varchar (255),
    payGrade integer,
    fk_EmployeeemployeeID integer NOT NULL,
    PRIMARY KEY(employeeID),
    FOREIGN KEY(fk_EmployeeemployeeID) REFERENCES Employee1 (employeeID)
);

然后，我想创建一个触发器，每当雇员A更改其工作标题时，它就会找到所有以A为经理的雇员，并将manager字段设置为null。这有道理吗？

Answer 1

您缺少实际触发代码周围的BEGIN ... END块。请检查手册，完整的语法在此处记录。

下一个错误是您无法UPDATE正在更新的表。您只需将新值分配给相关列:

另一个问题是AFTER触发器不能更改任何值，您将需要使用BEFORE触发器。

最后，为什么要在DELETE触发器中更改值？删除后该行将消失，因此无需更改该值。您可能想使用UPDATE触发器:

CREATE OR REPLACE TRIGGER  ManagerDeleteTrigger
  BEFORE UPDATE ON employee1
  REFERENCING OLD AS OldRow
  NEW AS NewRow
  FOR EACH ROW
  WHEN (OldRow.FK_EMPLOYEEEMPLOYEEID != NewRow.FK_EMPLOYEEEMPLOYEEID)
BEGIN
  :NewRow.FK_EMPLOYEEEMPLOYEEID := null;
END;
/