ORACLE After update trigger：解决ORA-04091变异表错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6915325/
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
ORACLE After update trigger: solving ORA-04091 mutating table error
提问by JoséNunoFerreira
I am trying to create a trigger:
我正在尝试创建一个触发器：
create or replace trigger NAME_OF_TRIGGER
after insert or update on table1
REFERENCING OLD AS OLD NEW AS NEW
for each row
to fill in automatically a couple of non obligatory fields when updating/inserting on a table.
在更新/插入表格时自动填写几个非必填字段。
This requires me to use a cursor that selects from table2 and also table1 (the subject of the trigger).
这要求我使用从 table2 和 table1（触发器的主题）中选择的游标。
Is there any way to avoid the mutating table error without using a temporary table for values or an autonomous transaction?
有没有办法在不使用值或自治事务的临时表的情况下避免变异表错误？
回答by APC
"Is there any way to avoid the mutating table error without using a temporary table for values or an autonomous transaction?"
“有没有办法在不使用临时表的值或自治事务的情况下避免变异表错误？”
tl;drno.
tl;博士没有。
The mutating table error is caused by querying the table which owns the trigger, or tables which are involved in a foreign key relationship with the owning table (at least in older versions of the database, not sure whether it still obtains).
mutating table 错误是由于查询拥有触发器的表，或与所属表存在外键关系的表（至少在旧版本的数据库中，不确定是否仍然获得）引起的。
In a properly designed application this should not be necessary. This is why many people regard mutating tables as an indicator of poor data modelling. For instance, mutation is often associated with insufficient normalisation.
在适当设计的应用程序中，这应该不是必需的。这就是为什么许多人将变异表视为数据建模不佳的指标。例如，突变通常与归一化不足有关。
To paraphrase Jamie Zawinski: Some people, when confronted with a mutating table exception, think "I know, I'll use autonomous transactions." Now they have two problems.
套用 Jamie Zawinski 的话：有些人在遇到变异表异常时，会想“我知道，我会使用自主事务。” 现在他们有两个问题。
Sometimes the error can be avoided by simply modifying the :NEW values in a BEFORE INSERT OR UPDATE trigger or by using virtual columns. But you'll need to post more details to see whether these apply.
有时可以通过简单地修改 BEFORE INSERT OR UPDATE 触发器中的 :NEW 值或使用虚拟列来避免错误。但是您需要发布更多详细信息以查看这些信息是否适用。
But the best workaround is not to need any other kind.
但最好的解决方法是不需要任何其他类型的。
