Looping Insert Trigger [message #391762] |
Fri, 13 March 2009 06:52 |
abis123
Messages: 31 Registered: February 2007
|
Member |
|
|
I am trying to create a trigger that inserts an extra row when certain criteria are met. So if I have a trigger to catch either of the words 'Ric' or 'Bob' being inserted into a certain column, it creates the other name e.g If I add Ric, it adds Bob and vice versa (a simplified explanation).
The issue created is the trigger is looping through the table and gets stuck in this loop. I'm not sure why it is, I would assume this trigger would be executed just once. Any help would be much appreciated! Thanks in advance.
|
|
|
Re: Looping Insert Trigger [message #391766 is a reply to message #391762] |
Fri, 13 March 2009 07:06 |
dr.s.raghunathan
Messages: 540 Registered: February 2008
|
Senior Member |
|
|
since you have said vice versa, when ric is inserted bob will be inserted through trigger. what so ever mean when bob is inserted ric will be inserted. where is the end? what sort of break you have incorporated in insert trigger. Really puzzling.
yours
dr.s.raghunathan
|
|
|
|
Re: Looping Insert Trigger [message #391768 is a reply to message #391762] |
Fri, 13 March 2009 07:06 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You have a trigger that fires on Insert
1) A row is inserted into the table, making the trigger fire
2) This trigger inserts a row.
3) Goto 1.
|
|
|
Re: Looping Insert Trigger [message #391769 is a reply to message #391762] |
Fri, 13 March 2009 07:12 |
abis123
Messages: 31 Registered: February 2007
|
Member |
|
|
Sorry, that simplified version was a little too simplified. I have also got criteria that checks row count = 0, so if I insert Ric it checks if Ric has been inserted before, if not then it inserts Bob and vice versa.
|
|
|
|
Re: Looping Insert Trigger [message #391773 is a reply to message #391762] |
Fri, 13 March 2009 07:19 |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Let me guess what you would have done.
a) you would have either created a before insert or after insert trigger on a table and try to insert into the same table based on a condition.
b) When you executed the code you would have hit the error ora-4091 table is mutating
c) To solve the mutating issue you would have made the triggers as an autonomous transaction
d) When you executed the code again you will be hitting the error maximum number of recursive sql level exceeded.
If my assumption is correct then you have an option to fix the issue. By declaring a dummy package and have a global variable initialised to zero and check within the trigger code and set it to something else and re-initiliase it the next time.
Hope it helps.
But on a side note please please please think n number of times before using autonomous transaction. Search in asktom or on this site about the side effects of autonomous transaction.
Regards
Raj
[Edit: ] @Michael, I was busy compiling my assumption and didn't see your post at that time
[Updated on: Fri, 13 March 2009 07:20] Report message to a moderator
|
|
|
|
|
Re: Looping Insert Trigger [message #391789 is a reply to message #391788] |
Fri, 13 March 2009 08:58 |
|
Michel Cadot
Messages: 68711 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Something like:
create or replace package mypkg is
in_trigger boolean := false;
end;
/
create or replace trigger mytrg1 before insert on mytable
for each row
begin
if not mypkg.in_trigger then
mypkg.in_trigger := true;
<processing>
end if;
end;
/
create or replace trigger mytrg2 after insert on mytable
begin
mypkg.in_trigger := false;
end;
/
This is the idea, you have to verify it works as expected.
Regards
Michel
[Updated on: Fri, 13 March 2009 09:10] Report message to a moderator
|
|
|
|
|
Re: Looping Insert Trigger [message #391805 is a reply to message #391788] |
Fri, 13 March 2009 10:49 |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
create or replace triggers test_trig
before insert on <table_name>
for each row
declare
pragma autonomous_transaction;
begin
If pkg.in_trigger
then
pkg.in_trigger := FALSE;
else
pkg.in_trigger := TRUE;
insert <statement>
End If;
commit;
End;
Regards
Raj
[Edit: ] @Michael ignore me, I don't know how I missed your latest post. I need a coffee
[Updated on: Fri, 13 March 2009 10:59] Report message to a moderator
|
|
|
|
Re: Looping Insert Trigger [message #391854 is a reply to message #391802] |
Sat, 14 March 2009 00:54 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Note however that whenever your trigger fails, the package variable will not be reset, causing the trigger not to fire for the rest of your session.
|
|
|
|