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

问题描述

我有一个触发器，它会在表中插入新记录时触发，因为我想在同一个表中插入新记录.
我的触发器是:

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;

它给出了一个像

ORA-04091: 表 TEST_TABLE 正在发生变化，触发器/函数可能看不到它

我认为它阻止我插入同一个表.
那么如何将这条新记录插入到同一个表中.

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

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

推荐答案

每当您有一个行级触发器修改您正在触发的表时，就会发生突变.问题是 Oracle 不知道如何表现.您插入一行，触发器本身向同表中插入一行，而 Oracle 感到困惑，因为，由于触发器而插入表中的那些插入，它们是否也受触发器操作的影响?

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.) 触发器之前的语句级别，用于实例化一个包，该包将跟踪插入的行.

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

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

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

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

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

希望有所帮助.

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