ORA-04091: 表 [blah] 正在发生变化，触发器/函数可能看不到它

2021-12-08 00:00:00 oracle triggers Hibernate ora-04091

我最近开始开发一个大型复杂应用程序，但由于此错误，我刚刚被分配了一个错误:

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;

该表有一个主键t1_pk"，一个约会事件ID"t1_appnt_evnt_id 和另一列t1_prnt_t1_pk"可能或可能不包含另一行的t1_pk.

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 may not contain another row's t1_pk.

似乎触发器试图确保没有其他人使用相同的 t1_appnt_evnt_id 已经引用了该行所引用的同一行 引用另一行，如果该行引用另一行.

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

DBA 对 bug 报告的评论说删除触发器，并在代码中执行检查"，但不幸的是他们有一个专有的代码生成框架，位于 Hibernate 之上，所以我什至想不通它实际上被写出来的地方，所以我希望有一种方法可以使这个触发器起作用.有吗?

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?

推荐答案

我想我不同意你对触发器试图做什么的描述做.在我看来，它旨在强制执行此业务规则:对于给定 t1_appnt_event 的值，只有一行可以具有非 NULL 值t1_prnt_t1_pk 一次.(它们在第二列中是否具有相同的值并不重要.)

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

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

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:

  • 该表有一个数字主键
  • 主键和 t1_prnt_t1_pk 都是正数

如果这些假设成立，您可以创建一个这样的函数:

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;

和这样的索引:

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

因此 PMNT 列为 NULL 的行将出现在索引中，主键的倒数作为第二个值，因此它们永远不会相互冲突.非 NULL 的行将使用列的实际(正)值.如果两行在两列中具有相同的非 NULL 值，那么您可能会违反约束的唯一方法是.

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.

来自 Paul Tomblin 的更新:我对伊戈尔在评论中提出的原始想法进行了更新:

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

相关文章