Re: Triggers in SQL plus

<absriram_at_gmail.com> a écrit dans le message de news:1101633430.239860.207720_at_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
>

Have a look at "Avoid mutating" paper from Tom Kyte at: http://osi.oracle.com/~tkyte/Mutate/index.html

You can also create a view your table and use and "instead of" trigger.

-- 
Regards
Michel Cadot