Re: Triggers in SQL plus

absriram_at_gmail.com wrote in message 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.

that logic doesn't sound right to me....

think about it "when I update an employee record, it should DISAPPEAR"

???

if you are trying to "version" the data, audit the changes, the update trigger need only do the INSERT. The delete would make the current live record "disappear", "go away"

Let the updated row stay in EMPLOYEE.

Or use a stored procedure if you really mean to archive this record. your stored procedure would:

1. insert into emp_hist select from emp
2. delete from emp

using triggers to do automagical things in the background will get you into trouble real quick. hidden side effects (the automagical part) will cause all kinds of issues eventually (the system becomes incredibly hard to understand. the stored procedure approach -- very straight forward)

>
> 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 - 10:49:05 CST