Nirmala -- Thanks for the question regarding "Need to stop recursiveness in trigger", version 8.1.7

You Asked

Hello Tom ...

I have a need to create a record for FREIGHT in RA_INTERFACE_LINES_ALL table, one per order. I created an AFTER INSERT statement trigger on the RA_INTERFACE_LINES_ALL table (avoiding the table mutating on row level triggers). The trigger on RA_INTERFACE_LINES_ALL table finds all orders in RA_INTERFACE_LINES_ALL with no FREIGHT lines and inserts the FREIGHT line in RA_INTERFACE_LINES_ALL table. The insert for FREIGHT line is causing the trigger to be invoked recursively. To avoid this ... I put the insert statement after passing a condition that checks for FREIGHT records. This condition stopped the trigger from being called recursively n times, but it seems like some of the FREIGHT line inserts are not visibile while the trigger is still progressing. Although the event is completes successfully, it ends up with more than 1 FREIGHT line per order.

I tried using AUTONOMOUS_TRANSACTION ... did not work.
I tried using GLOBAL TEMPORARY Tables ... same result as above.

Could you suggest another way to handle this situation ?

and we said...

this is wrong

it seems the record you want to create in FREIGHT should just be attributes in your RA_INTERFACE_LINES_ALL table (eg: this one mandatory record could just be part of the parent)

anyway....

ops$tkyte%ORA10GR2> create table p ( x int primary key, y int );

Table created.

ops$tkyte%ORA10GR2> create table c ( x references p, y int, z int, primary key(x,y) );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package state
  2  as
  3      type array is table of p.x%type index by binary_integer;
  4      g_data array;
  5  end;
  6  /

Package created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace trigger p_bi before insert on p
  2  begin
  3      state.g_data.delete;
  4  end;
  5  /

Trigger created.

ops$tkyte%ORA10GR2> create or replace trigger p_aifer after insert on p for each row
  2  begin
  3      state.g_data(:new.x) := 1;
  4  end;
  5  /

Trigger created.

ops$tkyte%ORA10GR2> create or replace trigger p_ai after insert on p
  2  declare
  3      l_idx number;
  4  begin
  5      l_idx := state.g_data.first;
  6      while (l_idx is not null)
  7      loop
  8          insert into c values ( l_idx, 1, 42 );
  9          l_idx := state.g_data.next(l_idx);
 10      end loop;
 11  end;
 12  /

Trigger created.

ops$tkyte%ORA10GR2> insert into p values ( 0, 0 );

1 row created.

ops$tkyte%ORA10GR2> insert into p select rownum, rownum from all_users where rownum <= 5;

5 rows created.

ops$tkyte%ORA10GR2> select * from c;

         X          Y          Z
---------- ---------- ----------
         0          1         42
         1          1         42
         2          1         42
         3          1         42
         4          1         42
         5          1         42

6 rows selected.

Reviews

Clarification   April 9, 2007 - 8am Central time zone Bookmark | Bottom | Top Reviewer: Maniappan from Bangalore, India I think the insert should also happen in the primary table only, not to a different one. Hence this causes issue for the questioner. You suggestion helps to insert into another child table, but not parent one. May be we can create a script that will disable the triggers and then insert into parent from child table then enable back, this might need to be scheduled when users dont do insert into parent table. Mine appears a complex one, but as usual Tom will provide a simple answer to this seemingly complex issue. Thanks Mani Followup   April 9, 2007 - 10am Central time zone: I did not follow you here at all.   April 10, 2007 - 2am Central time zone Bookmark | Bottom | Top Reviewer: Alen Oblak from Koper, Slovenija I think he wants to insert a row in the same table as the original insert - RA_INTERFACE_LINES_ALL. He is having one row for FREIGHT and one for ORDER. I suggest you dont run the logic on the trigger when you encounter the FREIGHT row. That is, when an insert is done, the trigger runs, and it checks if it is a ORDER row. Only in this case, run your logic of inserting another row for FREIGHT. This time, the trigger runs again, but it checks for the type of row, it finds it is a FREIGHT row and just exits.

---

Write a Review