Home » SQL & PL/SQL » SQL & PL/SQL » Looping Insert Trigger (Oracle, 9i, XP)
Looping Insert Trigger [message #391762] Fri, 13 March 2009 06:52 Go to next message
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 Go to previous messageGo to next message
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 #391767 is a reply to message #391762] Fri, 13 March 2009 07:06 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Crystal ball /forum/fa/1659/0/ suggests that you should perhaps post trigger code.
Re: Looping Insert Trigger [message #391768 is a reply to message #391762] Fri, 13 March 2009 07:06 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #391771 is a reply to message #391762] Fri, 13 March 2009 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can test and set a package variable that indicates "in_trigger" if the trigger is fired by itself.

Regards
Michel
Re: Looping Insert Trigger [message #391773 is a reply to message #391762] Fri, 13 March 2009 07:19 Go to previous messageGo to next message
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 Smile

[Updated on: Fri, 13 March 2009 07:20]

Report message to a moderator

Re: Looping Insert Trigger [message #391785 is a reply to message #391762] Fri, 13 March 2009 08:17 Go to previous messageGo to next message
abis123
Messages: 31
Registered: February 2007
Member
Thanks so much for your help. I will try out the solutions and see which gives me best results. Thanks again!
Re: Looping Insert Trigger [message #391788 is a reply to message #391762] Fri, 13 March 2009 08:39 Go to previous messageGo to next message
abis123
Messages: 31
Registered: February 2007
Member
Michel, can you tell me a little more about the in_trigger variable please or point me in the right direction? I cant find much on Google about it.
Re: Looping Insert Trigger [message #391789 is a reply to message #391788] Fri, 13 March 2009 08:58 Go to previous messageGo to next message
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 #391801 is a reply to message #391762] Fri, 13 March 2009 10:10 Go to previous messageGo to next message
abis123
Messages: 31
Registered: February 2007
Member
That's fantastic, many many thanks!
Re: Looping Insert Trigger [message #391802 is a reply to message #391801] Fri, 13 March 2009 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I think it is better to:
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>
    mypkg.in_trigger := false;
  end if;
end;
/

But still have to verify it works as expected, especially when inserting more than one row in a single insert.

Regards
Michel
Re: Looping Insert Trigger [message #391805 is a reply to message #391788] Fri, 13 March 2009 10:49 Go to previous messageGo to next message
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 #391812 is a reply to message #391762] Fri, 13 March 2009 12:35 Go to previous messageGo to next message
vaibhavk
Messages: 7
Registered: March 2009
Junior Member
Very good example, S.Rajaram thank you.
Re: Looping Insert Trigger [message #391854 is a reply to message #391802] Sat, 14 March 2009 00:54 Go to previous messageGo to next message
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.
Re: Looping Insert Trigger [message #391861 is a reply to message #391854] Sat, 14 March 2009 01:56 Go to previous message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Right. Maybe adding an exception block which reset the variable and reraise the error will work.

Regards
Michel
Previous Topic: Transaction log
Next Topic: Creating Procedure
Goto Forum:
  


Current Time: Sat Nov 09 16:47:16 CST 2024