INSERT触发器，用于在同一表中插入记录 [英] INSERT trigger for inserting record in same table

问题描述

我有一个触发器，可以在表中插入新记录时触发，因为我想在同一张表中插入新记录.
我的触发条件是:

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;

它给出类似

的错误

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

我认为这阻止了我插入同一张表.
所以我怎么能将此新记录插入同一张表.

注意:-我正在使用 Oracle作为数据库

解决方案

只要您有行级触发器来修改要在其上触发的表，便会发生突变.问题在于，Oracle不知道如何表现.您插入一行，触发器本身在 same 表中插入一行，Oracle感到困惑，因为那些由于触发器而插入表中的对象，它们是否也受触发器动作的影响? /p>

解决方案是一个三步过程.

1.)触发器之前的语句级别，该语句实例化一个程序包，该程序包将跟踪要插入的行.

2.)行级触发器之前或之后，将行信息保存到上一步中实例化的包变量中.

3.)插入表之后的触发器之后的语句级别，所有行都保存在package变量中.

可以在这里找到一个示例:

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

希望有帮助.

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

解决方案

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.

这篇关于INSERT触发器，用于在同一表中插入记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！