ORACLE更新后触发器：解决ORA-04091突变表错误
我正在尝试创建一个触发器：
create or replace trigger NAME_OF_TRIGGER
after insert or update on table1
REFERENCING OLD AS OLD NEW AS NEW
for each row
在表上更新/插入时自动填写几个非必填字段。
这要求我使用从 table2 和 table1 （触发器的主题）中进行选择的游标。
有没有办法在不使用值临时表或自治事务的情况下避免变异表错误？
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).
Is there any way to avoid the mutating table error without using a temporary table for values or an autonomous transaction?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
tl;dr 否。
变异表错误是由查询拥有触发器的表或与拥有触发器有外键关系的表引起的表（至少在旧版本的数据库中，不确定它是否仍然可以获得）。
这就是为什么许多人将变异表视为数据建模不佳的指标。通常与标准化不足有关。
套用 Jamie Zawinski 的话说：有些人在遇到变异表异常时会想“我知道，我会使用自主事务。”
有时，只需修改 :NEW 中的值就可以避免该错误 。在 INSERT OR UPDATE 触发器之前或使用虚拟列，但您需要发布更多详细信息以查看这些是否适用，
但最好的解决方法是不需要任何其他类型。
tl;dr no.
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).
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.
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.
But the best workaround is not to need any other kind.