Compiler error for Trigger Statement in Oracle

Question

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?

user330315 · Accepted Answer · 2013-10-10 18:06:54Z

2

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

answered Oct 10, 2013 at 18:06

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

zaloo Over a year ago

I had the BEGIN/END earlier but i got rid of it when I looked at another reference. However, this code doesn't work because it still gets errors. Also, it doesn't do what I'm trying to accomplish, which is set all the rows with FK_EMPLOYEEEMPLOYEEID = OldRow.EmployeeID to have FK_EMPLOYEEEMPLOYEEID = null.

user330315 Over a year ago

@user2756569 Then you are doing something else wrong, because the code itself is correct: sqlfiddle.com/#!4/5e64d/1 You might want to edit your question and describe your real problem. Sounds to me as if you simply want a foreign key constraint with an on update set null condition. But without the definition of all tables involved and a clear description on what you want to achieve this is all just a wild guess.

zaloo Over a year ago

Okay, I'll clarify my problem first! EDIT: Just updated the OP

Sean Dulin · Accepted Answer · 2013-10-10 18:20:31Z

0

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;

edited Oct 10, 2013 at 18:20

user330315

answered Oct 10, 2013 at 18:15

Sean Dulin

512 bronze badges

3 Comments

zaloo Over a year ago

I got a compilation error for your code as well. Error(2,10): PLS-00103: Encountered the symbol "ROW" when expecting one of the following: in

user330315 Over a year ago

The trigger won't work even if the syntax is correct. You can not update the table on which the trigger has been fired.

Sean Dulin Over a year ago

sorry, but the BEGIN down one line, after the FOR EACH ROW. a_horse_with_no_name is right, you can't update the old or new values of the record. See: techonthenet.com/oracle/triggers/after_delete.php

Collectives™ on Stack Overflow

Compiler error for Trigger Statement in Oracle

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related