ORACLE After update trigger:解决ORA-04091变异表错误 [英] ORACLE After update trigger: solving ORA-04091 mutating table error

问题描述

我正在尝试创建触发器:

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.

这需要我使用从 table2 和 table1(触发器的主题)中进行选择的游标.

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?"

tl;dr 没有.

mutating table 错误是由于查询了拥有触发器的表，或者与所属表存在外键关系的表(至少在旧版本的数据库中，不确定是否仍然获得)引起的.

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.

套用 Jamie Zawinski 的话说:有些人在遇到变异表异常时，会想我知道，我将使用自主事务."现在他们有两个问题.

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.

有时可以通过简单地修改 BEFORE INSERT OR UPDATE 触发器中的 :NEW 值或使用虚拟列来避免错误.但是您需要发布更多详细信息以查看这些信息是否适用.

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.

这篇关于ORACLE After update trigger:解决ORA-04091变异表错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！