ORA-04091: table USR.MYTAB1 is mutating, trigger/function may not see it

[gsprince]

ORA-04091: table USR.MYTAB1 is mutating, trigger/function may not see it

I have the following three tables,

myTab1:
----------
keycol1# number(10),
othercol1 number(10)
PRIMARY KEY(keycol1) ;

myTab2:
-------
keycol1# number(10),
keycol2# number(10),
othercol1# number(10),
othercol2# number(10),
PRIMARY KEY(keycol1# , keycol2# ),
FOREIGN KEY(keycol1#) REFERENCES Tabl(keycol1#) ON DELETE CASCADE DEFERRABLE) ;

myTab3:
-------
keycol1# number(10),
keycol2# number(10),
keycol3# number(10),
keycol4# number(10),
othercol1# number(10),
othercol2# number(10),
PRIMARY KEY(keycol1# , keycol2# ,keycol3# , keycol4#),
FOREIGN KEY(keycol1#) REFERENCES Tabl(keycol1#) ON DELETE CASCADE DEFERRABLE)

And, I have a trigger on myTab2, which has to populate the myTab3
whenever some insert/update/delete operation is done on myTab2.

The trigger looks like the following:
-------------------------------------
create or replace trigger myTrg1
after delete on myTab2
for each row
my_othercol2 myTab2.othercol2%type default 100;
begin
my_othercol2 := :old.othercol2 ;

update myTab3 set othercol2# = othercol2# + my_othercol2 ;
where keycol1# = :old.keycol1#
and othercol1# = :old.othercol1# ;
end ;

During deletion, I get the following. How do I avoid this?

ERROR at line 1:
ORA-04091: table USR.MYTAB3 is mutating, trigger/function may not see it
ORA-06512: at "USR.MYTRG1", line 7
ORA-04088: error during execution of trigger 'USR.MYTRG1',

Summary:
--------
myTab2, myTab3 are children to myTab1 and are connected by keycol1# as the key.

When I delete the keycol1# entry from myTab1, I want to delete the entry from both myTab2, myTab3. but, if I am deleting from myTab2, I want to keep the summarised info on the myTab3 table

Any simple way to do this, without disabling the constraints?.
Also, I prefer not to delete the child records then delete from the parents (unless thats the only way).

Keycol1, is the key which links many other tables, when I delete from myTab1, I am deleting from many other related tables, all connected by cascade rules


Thanks,

[vinit]

Hi,
Try making it a statement level trigger instead of row level. This should solve your problem.

[LND]

usual tachnique is
1) to keep all id's of records touched by row level trigger into some colletion: pl/sql array(vararray or table) or db table(for example, temporary table is very good for this)
2) use statement level trigger to process this temporary colletion and update everything.

NOTE: I never tried to find out why oracle has this mutating error, but I think about 2 reasons:
a) if it is only about performace (for example, paralelising queries) then nothing to worry.
b) if it is about data consistency (for example, assume in your case oracle can not guarantee that f.k does ok when triggers mess things) then it would be interesting to know about those side effects and how to avoid them.

[marist89]

For an excellent discussion on mutating triggers visit [url]http://osi.oracle.com/wa/ask/owa/ask_tom_pkg.display?p_dispid=290416059674[/url]

[gsprince]

Thank you guys, for all ur responses.