Re: Mutating problem when delete

"Zhiping Nie" <nie_at_mail.med.upenn.edu> wrote in message news:slrn3vsavhivd.9th3.nie_at_pobox.upenn.edu...
> My environment:Win2k & Oracle 9i2.
>
> There are two tables T1, T2 which have similar structure.
> T1 T2
> ------- -------------
> c1 c1
> c2 c2
> c3
>
> What I want to do is:
> whenever T1 gets a row in,
> T2 will get same row, and then the row in T1 will be deleted.
>
> Here is the example code:
> create trigger t1_trigger
> before insert on t1
> on each row
> referencing new as new old as old
> begin
> insert into t2 values(:new.c1, :new.c2);
> end;
>
> create trigger t2_trigger
> after insert on t2
> on each row
> referencing new as new old as old
> begin
> delete from t1 where c1=:new.c1;
> end;
>
> Is it possible to get it done?
> Thanks in advance!
>

try reworking the code/trigger to run at the statement level rather than for each row. You'll need to trawk through a record set rather than have the record to hand but will get rid of the problems.

Also, read the other posts to this problem. The way you have described the scenario, it's a very questionable design! Received on Fri Dec 13 2002 - 04:10:41 CST