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

我最近开始研究一个大型的复杂应用程序,我刚刚因为这个错误而被分配了一个错误:

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之上,所以我甚至无法弄清楚它实际上在哪里写出来,所以我希望有一种方法可以使这个触发器工作.在那儿？

我想我不同意你对触发器试图做什么的描述.它看起来像是为了强制执行此业务规则:对于给定的t1_appnt_event值,一次只能有一行非空值为t1_prnt_t1_pk.(如果它们在第二列中具有相同的值,则无关紧要.)

有趣的是,它是为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值.

这可能过于"聪明",但它可能会帮助您解决问题.

保罗·汤姆林(Pau​​l 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);