MUTATING TABLE

[ekobudy]

I want to automatically insert record which opposite than the new record inserted, My trigger Code is :
Create Or Replace Trigger AutoPartner
AFTER INSERT ON Djisamsoe.Try
For Each Row
Declare
v_send Djisamsoe.Try.send%Type;
v_rec Djisamsoe.Try.receive%Type;
v_tr Djisamsoe.Try.tr_type%Type;
Begin
v_send:=:NEW.send;
v_rec:=:NEW.receive;
v_tr:=:NEW.tr_type;
IF v_tr='0' THEN
INSERT INTO Djisamsoe.Try
(send,receive,tr_type)
Values
(v_rec, v_send, '1');
ELSIF v_tr='1' THEN
INSERT INTO Djisamsoe.Try
(send,receive,tr_type)
Values
(v_rec, v_send, '0');
END IF;
End;
/

But i get a mutating table error...ORA-04091 and ORA-04088; How can I handle this problem?.

 

[jee]

Normally you can get around mutating table problems by using two triggers - one a before row trigger and one an after statement trigger, and by passing data between the two triggers using package variables. You record the data into package variables in the before row trigger, and you use the packge variable values in the select/insert/update in the after statement trigger -

However in your case, you have to be careful because you can get into an infinite loop: A user inserts a row which causes the triggers to fire which insert a row which causes the triggers to fire which insert a row...

You could deal with this by using a flag in a package variable and putting code like this in your after statement trigger:

if pkg_name.flag = 0 then
pkg_name.flag = 1
Insert into ....
pkg_name.flag = 0
end if;

Assumes that the flag is initialized to 0 in the package.

 

[ekobudy]

Thanks jee,