ORA-04091：表[blah]正在突变，触发器/功能可能看不到它

Question

我最近开始处理大型复杂的应用程序，由于这个错误，我刚刚被分配了一个错误：

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'

有问题的触发器看起来像

    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;

该表具有主键“ t1_pk”，“约会事件ID” t1_appnt_evnt_id和另一列“
t1_prnt_t1_pk”，该列可能包含也可能不包含另一行的t1_pk。

这似乎触发正试图确保以相同的其他人t1_appnt_evnt_id也 提到了同样一个该行指的是 一个转诊到另一行，如果这个人是指到另一行。

DBA对错误报告的评论是“删除触发器，然后执行代码中的检查”，但不幸的是，他们在Hibernate之上有一个专有的代码生成框架，因此我什至无法弄清楚它的实际位置被写出来，所以我希望有一种方法可以使此触发器起作用。在那儿？

Answer 1

我认为我不同意您对触发器要执行的操作的描述。在我看来，它旨在执行此业务规则：对于给定的t1_appnt_event值，一次只有一行可以具有t1_prnt_t1_pk的非NULL值。（它们是否在第二列中具有相同的值都没有关系。）

有趣的是，它是为UPDATE OF t1_appnt_event而不是其他列定义的，因此我认为有人可以通过更新第二列来打破规则，除非该列有单独的触发器。

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

• 该表具有数字主键
• 主键和t1_prnt_t1_pk始终都是正数

如果这些假设成立，则可以创建如下函数：

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;

和这样的索引：

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