Home » SQL & PL/SQL » SQL & PL/SQL » mutating table error with an extra trigger (10g Release 10.2.0.3.0)
mutating table error with an extra trigger [message #359964] Wed, 19 November 2008 00:34 Go to next message
smora
Messages: 59
Registered: May 2006
Member
I currently have 2 tables (TableA and TableB), and a trigger (trig1) that fires when an insert takes place on Table B. This trigger does a lookup on TableA.

CREATE OR REPLACE TRIGGER trig1
AFTER INSERT ON TableB
FOR EACH ROW

BEGIN

select xxx into yyy from TableA where ...;


This setup works just fine. Now I need to create a new trigger:


create or replace TRIGGER trig2
AFTER INSERT ON TableA
FOR EACH ROW

BEGIN

insert into TableB values(some values);


Now the insert on TableA causes an insert on TableB, which fires trig1, which does a lookup on TableA. This is causing a mutating table error.

I have looked into the solutions offered, the most commonly recommended solution involving a package and 3 triggers wont work here, as trig2 cannot access the bind variables from the insert into TableA.

I also tried to use the INSTEAD OF trigger solution, but I am not clear how introducing a view into this situation will help, as I still need the insert into TableA to take place, whereas the INSTEAD OF trigger prevents the triggering action from taking place.

Any ideas would be appreciated.
Thanks
SM
Re: mutating table error with an extra trigger [message #359965 is a reply to message #359964] Wed, 19 November 2008 00:38 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
Sorry I meant trig1 cannot access the bind variables from the insert into TableA. trig2 can access those, of course.
Re: mutating table error with an extra trigger [message #359966 is a reply to message #359964] Wed, 19 November 2008 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you have such trouble triggers it is the sign that should alert to give up from triggers and try something else.
Indeed triggers are NOT the way to make complex things.
Read Tom Kyte The Trouble with Triggers.

Regards
Michel
Re: mutating table error with an extra trigger [message #360057 is a reply to message #359964] Wed, 19 November 2008 07:07 Go to previous messageGo to next message
mail2das.ashok
Messages: 10
Registered: May 2008
Junior Member
use statement level trigger or autonomous transaction
Re: mutating table error with an extra trigger [message #360066 is a reply to message #360057] Wed, 19 November 2008 07:45 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I bet inserted values should be known and in this statement level trigger is useless.

Regards
Michel
Previous Topic: Query with NOT IN
Next Topic: dbms_output.put_line(boolean)
Goto Forum:
  


Current Time: Thu Dec 08 04:12:46 CST 2016

Total time taken to generate the page: 0.07727 seconds