用于在同一个表中插入记录的 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就搞糊涂了，原因，那些由于触发器而插入到表中的，它们是否也受到触发器动作的影响?

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?

解决方案分为三步.

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屋！