INSERT trigger for inserting record in same table

Question

I have a trigger that is fire on inserting a new record in table in that i want to insert new record in the same table.
My trigger is :

create or replace trigger inst_table
after insert on test_table referencing new as new old as old  
for each row
declare 
      df_name varchar2(500);
      df_desc varchar2(2000);

begin
      df_name := :new.name;
      df_desc := :new.description;

     if inserting then
          FOR item IN (SELECT pid FROM tbl2 where pid not in(1))
             LOOP
                 insert into test_table (name,description,pid) values(df_name,df_desc,item.pid); 
             END LOOP;    
     end if; 
end;

its give a error like

ORA-04091: table TEST_TABLE is mutating, trigger/function may not see it

i think it is preventing me to insert into same table.
so how can i insert this new record in to same table.

Note :- I am using Oracle as database

Answer 1

Although after complaining about how bad triggers are, here is another solution.

Maybe you need to look at having two tables. Insert the data into the test_table table as you currently do. But instead of having the trigger insert additional rows into the test_table table, have a detail table with the data. The trigger can then insert all the required rows into the detail table.

You may again encounter the mutating trigger error if you have a delete cascade foreign key relationship between the two tables so it might be best to avoid that.

Answer 2

I'd say that you should look at any way OTHER than triggers to achieve this. As mentioned in the answer from Mark Bobak, the trigger is inserting a row and then for each row inserted by the trigger, that then needs to call the trigger to insert more rows.

I'd look at either writing a stored procedure to create the insert or just insert via a sub-query rather than by values.

Triggers can be used to solve simple problems but when solving more complicated problems they will just cause headaches.

It would be worth reading through the answers to this duplicate question posted by APC and also these this article from Tom Kyte. BTW, the article is also referenced in the duplicate question but the link is now out of date.

Answer 3

Mutation happens any time you have a row-level trigger that modifies the table that you're triggering on. The problem, is that Oracle can't know how to behave. You insert a row, the trigger itself inserts a row into the same table, and Oracle gets confused, cause, those inserts into the table due to the trigger, are they subject to the trigger action too?

The solution is a three-step process.

1.) Statement level before trigger that instantiates a package that will keep track of the rows being inserted.

2.) Row-level before or after trigger that saves that row info into the package variables that were instantiated in the previous step.

3.) Statement level after trigger that inserts into the table, all the rows that are saved in the package variable.

An example of this can be found here:

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936

Hope that helps.