ORACLE更新触发后:解决ORA-04091变异表错误

问题描述

我正在尝试创建触发器:

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).

是否可以在不使用临时表存储值或自主事务的情况下避免变异表错误?

推荐答案

tl; dr 否

变异表错误是由于查询拥有触发器的表或与拥有表具有外键关系的表(至少在较旧的数据库版本中，不确定是否仍可获取)引起的.

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.

有时可以通过简单地在INSERT或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更新触发后:解决ORA-04091变异表错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！