CODEWITHSUNDEEP
sqloracle-databasetriggerscompiler-errors
zaloo
zaloo

Reputation: 919

Compiler error for Trigger Statement in Oracle

I've written a CREATE TRIGGER but for some reason, I can't get it to not have a complier error. Any ideas? Here's the code and the error message:

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;

The error message is:

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.

EDIT: Here's a clarification of my problem. I'm creating a table with the following statements. Each employee has a manager (which is represented by the 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)
);

I then want to create a trigger that whenever an employee A changes his jobTitle, it finds all employees that had A as their manager and sets the manager field to null. Does this make any sense?

Upvotes: 0

Views: 498

Answers (2)

Sean Dulin
Sean Dulin

Reputation: 51

Try wrapping it in a begin and end clause.

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

Upvotes: 0

user330315
user330315

Reputation:

You are missing the BEGIN ... END block around the actual trigger code. Please check the manual, the complete syntax is documented there.

The next error is that you cannot UPDATE the table that is being updated. You simply assign the new value to the column in question:

Another problem is that an AFTER trigger cannot change any value, you will need to use a BEFORE trigger.

And finally, why are you changing the value in a DELETE trigger? The row will be gone after deleting anyway, so there is no need to change the value. You probably wanted to use an UPDATE trigger:

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;
/

Upvotes: 2

Related Questions