Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Triggers in SQL plus

Triggers in SQL plus

From: <absriram_at_gmail.com>
Date: 28 Nov 2004 01:17:10 -0800
Message-ID: <1101633430.239860.207720@f14g2000cwb.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US