oracle INSERT触发器用于在同一个表中插入记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8167200/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
INSERT trigger for inserting record in same table
提问by Yagnesh Agola
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
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
注意：- 我使用Oracle 作为数据库
回答by Mark J. Bobak
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 sametable, and Oracle gets confused, cause, those inserts into the table due to the trigger, are they subject to the trigger action too?
每当您有一个行级触发器修改您正在触发的表时，就会发生突变。问题在于，Oracle 不知道如何表现。您插入一行，触发器本身向同一个表中插入一行，Oracle 感到困惑，原因是，由于触发器而插入表中的那些，它们是否也受触发器操作的影响？
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.
1.) 触发器之前的语句级别，它实例化一个包，该包将跟踪插入的行。
2.) Row-level before or after trigger that saves that row info into the package variables that were instantiated in the previous step.
2.) 行级之前或之后的触发器，用于将该行信息保存到在上一步中实例化的包变量中。
3.) Statement level after trigger that inserts into the table, all the rows that are saved in the package variable.
3.) 插入表的触发器之后的语句级别，所有保存在包变量中的行。
An example of this can be found here:
可以在此处找到一个示例：
http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936
http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936
Hope that helps.
希望有帮助。
回答by Mike Meyers
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.
我会说你应该以其他方式看待触发器来实现这一目标。正如 Mark Bobak 的回答中提到的，触发器插入一行，然后对于触发器插入的每一行，然后需要调用触发器来插入更多行。
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 questionposted by APC and also these this articlefrom Tom Kyte. BTW, the article is also referenced in the duplicate question but the link is now out of date.
值得阅读APC 发布的这个重复问题的答案以及Tom Kyte 的这篇文章。顺便说一句，这篇文章也在重复的问题中被引用，但链接现在已经过时了。
回答by Mike Meyers
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_tabletable as you currently do. But instead of having the trigger insert additional rows into the test_tabletable, have a detail table with the data. The trigger can then insert all the required rows into the detail table.
也许你需要看看有两张桌子。test_table像当前一样将数据插入表中。但不是让触发器在test_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.
如果您在两个表之间有删除级联外键关系，您可能会再次遇到变异触发器错误，因此最好避免这种情况。
