ORA-04091：table [blah]正在变异，触发器/函数可能看不到它

问题描述

我最近开始研究大型复杂应用程序，并且由于此错误而刚刚分配了一个错误：

  ORA-04091：表SCMA.TBL1正在变异，触发器/函数可能看不到它
 ORA-06512：在SCMA.TRG_T1_TBL1_COL1，第4行
 ORA-04088：执行触发器期间出错' SCMA.TRG_T1_TBL1_COL1'
  

有问题的触发器看起来像

 创建或替换TRIGGER TRG_T1_TBL1_COL1 
在插入或更新t1_appnt_evnt_id之前TBL1 
对于每行
当（NEW.t1_prnt_t1_pk不为空） 
 DECLARE 
 v_reassign_count number（20）; 
 BEGIN 
 select count（t1_pk）INTO v_reassign_count from TBL1 
其中t1_appnt_evnt_id =：new.t1_appnt_evnt_id和t1_prnt_t1_pk不为空; 
 IF（v_reassign_count> 0）THEN 
 RAISE_APPLICATION_ERROR（-20013，'Multiple reassignments not allowed'）; 
 END IF; 
 END; 
  

表中有一个主键 t1_pk ，约会事件ID
t1_appnt_evnt_id 和另一列 t1_prnt_t1_pk ，这可能或可能是
不包含另一行的 t1_pk 。

看起来触发器试图确保没有其他人
相同 t1_appnt_evnt_id 有转介到另一行，如果这个指的是另一行。

DBA对错误报告的评论说：删除触发器并在代码中执行检查，但不幸的是，它们拥有专有的代码生成框架分层在Hibernate之上，所以我甚至不知道它在哪里写出来，所以我希望有一种方法可以使这个触发器工作。有没有？

解决方案

我想我不同意你的描述触发器试图
做什么。在我看来，它是为了强制执行此业务规则：对于给定值为t1_appnt_event的
，一次只有一行可以具有非空值
t1_prnt_t1_pk。 （不管它们在第二列中的值是否相同）。

有趣的是，它被定义为UPDATE OF t1_appnt_event而不是另一列，所以我认为有人可以通过更新第二列来破坏规则，除非该列有单独的触发器。

可能有一种方法可以创建一个函数基于强制执行此规则的索引，以便您完全摆脱触发器。我想出了一种方法，但它需要一些假设：

• 表中有一个数字主键


• 主键和t1_prnt_t1_pk都是正数

如果这些假设是真的，您可以创建一个如下所示的函数：

  dev>创建或替换函数f（数字，b数字）返回数确定性为
 2 begin 
 3如果a为null，则返回0-b;否则返回一个;万一; 
 4结束; 
  

以及像这样的索引：

  CREATE UNIQUE INDEX my_index ON my_table 
（t1_appnt_event，f（t1_prnt_t1_pk，primary_key_column））; 
  

因此，PMNT列为NULL的行将出现在索引中，主键的倒数与第二个价值，所以他们永远不会相互冲突。行不是NULL的行将使用该列的实际（正）值。如果两行在两列中都有相同的非NULL值，那么你可以得到违反约束条件的唯一方法就是如果这两列的值非常巧妙可能会帮助你解决你的问题。

Paul Tomblin的更新：我更新了原始想法，即igor放入评论：

  CREATE UNIQUE INDEX cappec_ccip_uniq_idx 
 ON tbl1（t1_appnt_event，
 CASE WHEN t1_prnt_t1_pk IS NOT NULL THEN 1 ELSE t1_pk END）; 
  

I recently started working on a large complex application, and I've just been assigned a bug due to this error:

ORA-04091: table SCMA.TBL1 is mutating, trigger/function may not see it
ORA-06512: at "SCMA.TRG_T1_TBL1_COL1", line 4
ORA-04088: error during execution of trigger 'SCMA.TRG_T1_TBL1_COL1'

The trigger in question looks like

    create or replace TRIGGER TRG_T1_TBL1_COL1
   BEFORE  INSERT OR UPDATE OF t1_appnt_evnt_id ON TBL1
   FOR EACH ROW
   WHEN (NEW.t1_prnt_t1_pk is not  null)
   DECLARE
        v_reassign_count number(20);
   BEGIN
       select count(t1_pk) INTO v_reassign_count from TBL1
              where  t1_appnt_evnt_id=:new.t1_appnt_evnt_id and t1_prnt_t1_pk is not null;
       IF (v_reassign_count > 0) THEN
           RAISE_APPLICATION_ERROR(-20013, 'Multiple reassignments not allowed');
       END IF;
   END;

The table has a primary key "t1_pk", an "appointment event id"t1_appnt_evnt_id and another column "t1_prnt_t1_pk" which may or maynot contain another row's t1_pk.

It appears the trigger is trying to make sure that nobody else with thesame t1_appnt_evnt_id has a referral to another row, if this one is referring to another row.

The comment on the bug report from the DBA says "remove the trigger, and perform the check in the code", but unfortunately they have a proprietary code generation framework layered on top of Hibernate, so I can't even figure out where it actually gets written out, so I'm hoping that there is a way to make this trigger work. Is there?

解决方案

I think I disagree with your description of what the trigger is trying todo. It looks to me like it is meant to enforce this business rule: For agiven value of t1_appnt_event, only one row can have a non-NULL value oft1_prnt_t1_pk at a time. (It doesn't matter if they have the same value in the second column or not.)

Interestingly, it is defined for UPDATE OF t1_appnt_event but not for the other column, so I think someone could break the rule by updating the second column, unless there is a separate trigger for that column.

There might be a way you could create a function-based index that enforces this rule so you can get rid of the trigger entirely. I came up with one way but it requires some assumptions:

• The table has a numeric primary key
• The primary key and the t1_prnt_t1_pk are both always positive numbers

If these assumptions are true, you could create a function like this:

dev> create or replace function f( a number, b number ) return number deterministic as
  2  begin
  3    if a is null then return 0-b; else return a; end if;
  4  end;

and an index like this:

CREATE UNIQUE INDEX my_index ON my_table
  ( t1_appnt_event, f( t1_prnt_t1_pk, primary_key_column) );

So rows where the PMNT column is NULL would appear in the index with the inverse of the primary key as the second value, so they would never conflict with each other. Rows where it is not NULL would use the actual (positive) value of the column. The only way you could get a constraint violation would be if two rows had the same non-NULL values in both columns.

This is perhaps overly "clever", but it might help you get around your problem.

Update from Paul Tomblin: I went with the update to the original idea that igor put in the comments:

 CREATE UNIQUE INDEX cappec_ccip_uniq_idx
 ON tbl1 (t1_appnt_event,
    CASE WHEN t1_prnt_t1_pk IS NOT NULL THEN 1 ELSE t1_pk END);

这篇关于ORA-04091：table [blah]正在变异，触发器/函数可能看不到它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！