Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Triggers in SQL plus
Hi all,
I have two tables emp and empHist. I need to create a trigger for
Update of emp. When emp is updated the updated record should be copied
to empHist and that record should be deleted from emp. But I get a
mutating table error.
Here's the trigger that i created.
CREATE TRIGGER DEL
AFTER UPDATE OF VET ON EMPLOYEE
FOR EACH ROW
WHEN(NEW.VET IS NOT NULL)
BEGIN
INSERT INTO EMP_HIST1
VALUES(:OLD.LNAME,:OLD.SSN,:OLD.BDATE,:OLD.SALARY,:OLD.DNO,:OLD.VST,:NEW.VET);
DELETE FROM EMPLOYEE WHERE VET = :NEW.VET;
END;
and the query was
UPDATE EMPLOYEE SET VET= DATE '2004-01-04' WHERE SSN='888665555'; I even tried using a temp table, including the delete query in a separate trigger that will be fired when the record is inserted into the temp table. But the mutating problem still exists.
What should I do to avoid the mutating table problem? If I need to use package variable, how should I do that in this case?
Thank you,
Sriram Received on Sun Nov 28 2004 - 03:17:10 CST