| mutating table error with an extra trigger [message #359964] |
Wed, 19 November 2008 00:34  |
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
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
