避免触发器互更新引发的循环及ORA-04091数据变异问题

架构老林

2026-7-4

问题描述

需要实现两个表字段的近实时同步：udt_dfuview表的REVIEWED_THIS_MONTH字段与dfuexception表的u_review字段，通过主键dmdunit和loc关联。业务逻辑如下：

当物料在udt_dfuview中被审核（REVIEWED_THIS_MONTH=1），若该物料存在于dfuexception且异常类型为20000，则同步标记u_review=1；反向操作同理。
最初使用两个独立触发器实现，但同时启用时触发ORA-04091数据变异错误，原因是触发器循环调用导致表处于变异状态（触发器执行期间无法读取正在修改的表）。

原触发器代码

触发器1（UDT_DFUVIEW更新触发）

CREATE OR REPLACE TRIGGER SCPOMGR.TRG_UDTDFUVIEW_RVIEWD_MNTH
AFTER UPDATE
OF REVIEWED_THIS_MONTH ON SCPOMGR.UDT_DFUVIEW
FOR EACH ROW
BEGIN
IF (:NEW.REVIEWED_THIS_MONTH = 1 )
THEN 
update scpomgr.dfuexception
set dfuexception.u_review  = 1 
where dfuexception.dmdunit=:old.dmdunit and dfuexception.loc=:old.loc
and dfuexception.exception = 20000 and dfuexception.u_review = 0;
ELSE 
update scpomgr.dfuexception
set dfuexception.u_review  = 0 
where dfuexception.dmdunit=:old.dmdunit and dfuexception.loc=:old.loc
and dfuexception.exception = 20000 and dfuexception.u_review = 1
;
 END IF;
END;
/

触发器2（DFUEXCEPTION更新触发）

CREATE OR REPLACE TRIGGER SCPOMGR.TRG_DFUEXCEPTION_RVIEWD
AFTER UPDATE
OF u_review ON SCPOMGR.DFUEXCEPTION
FOR EACH ROW
BEGIN
IF ( :NEW.u_review = 1 and :new.exception = 20000 )
THEN 
update scpomgr.udt_dfuview
set udt_dfuview.REVIEWED_THIS_MONTH  = 1 
where udt_dfuview.dmdunit=:old.dmdunit and udt_dfuview.loc=:old.loc
and udt_dfuview.REVIEWED_THIS_MONTH = 0
;
ELSE 
update scpomgr.udt_dfuview
set udt_dfuview.REVIEWED_THIS_MONTH  = 0 
where udt_dfuview.dmdunit=:old.dmdunit and udt_dfuview.loc=:old.loc
and udt_dfuview.REVIEWED_THIS_MONTH = 1
;
 END IF;

END;
/

错误信息

ORA-04091: table SCPOMGR.UDT_DFUVIEW is mutating, trigger/function may not see it
ORA-06512: at "SCPOMGR.TRG_DFUEXCEPTION_RVIEWD", line 4
ORA-04088: error during execution of trigger 'SCPOMGR.TRG_DFUEXCEPTION_RVIEWD'
ORA-06512: at "SCPOMGR.TRG_UDTDFUVIEW_RVIEWD_MNTH", line 4
ORA-04088: error during execution of trigger 'SCPOMGR.TRG_UDTDFUVIEW_RVIEWD_MNTH'

解决方案

针对触发器循环和数据变异问题，推荐三种可行的近实时同步方案：

方案1：添加会话级触发标记，打破循环调用

通过定义会话级变量标记当前触发来源，在触发器中判断标记，跳过反向触发逻辑，避免循环。

步骤1：创建会话级包存储标记

CREATE OR REPLACE PACKAGE SCPOMGR.SYNC_TRIGGER_FLAGS IS
  g_sync_in_progress BOOLEAN := FALSE;
END SYNC_TRIGGER_FLAGS;
/

步骤2：修改两个触发器，加入标记判断

修改后的触发器1

CREATE OR REPLACE TRIGGER SCPOMGR.TRG_UDTDFUVIEW_RVIEWD_MNTH
AFTER UPDATE
OF REVIEWED_THIS_MONTH ON SCPOMGR.UDT_DFUVIEW
FOR EACH ROW
BEGIN
  -- 若当前是反向触发，跳过执行
  IF SCPOMGR.SYNC_TRIGGER_FLAGS.g_sync_in_progress THEN
    RETURN;
  END IF;

  SCPOMGR.SYNC_TRIGGER_FLAGS.g_sync_in_progress := TRUE;
  BEGIN
    IF (:NEW.REVIEWED_THIS_MONTH = 1 ) THEN 
      UPDATE scpomgr.dfuexception
      SET u_review = 1 
      WHERE dmdunit = :OLD.dmdunit 
        AND loc = :OLD.loc
        AND exception = 20000 
        AND u_review = 0;
    ELSE 
      UPDATE scpomgr.dfuexception
      SET u_review = 0 
      WHERE dmdunit = :OLD.dmdunit 
        AND loc = :OLD.loc
        AND exception = 20000 
        AND u_review = 1;
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      SCPOMGR.SYNC_TRIGGER_FLAGS.g_sync_in_progress := FALSE;
      RAISE;
  END;
  SCPOMGR.SYNC_TRIGGER_FLAGS.g_sync_in_progress := FALSE;
END;
/

修改后的触发器2

CREATE OR REPLACE TRIGGER SCPOMGR.TRG_DFUEXCEPTION_RVIEWD
AFTER UPDATE
OF u_review ON SCPOMGR.DFUEXCEPTION
FOR EACH ROW
BEGIN
  -- 若当前是反向触发，跳过执行
  IF SCPOMGR.SYNC_TRIGGER_FLAGS.g_sync_in_progress THEN
    RETURN;
  END IF;

  SCPOMGR.SYNC_TRIGGER_FLAGS.g_sync_in_progress := TRUE;
  BEGIN
    IF (:NEW.u_review = 1 AND :NEW.exception = 20000 ) THEN 
      UPDATE scpomgr.udt_dfuview
      SET REVIEWED_THIS_MONTH = 1 
      WHERE dmdunit = :OLD.dmdunit 
        AND loc = :OLD.loc
        AND REVIEWED_THIS_MONTH = 0;
    ELSE 
      UPDATE scpomgr.udt_dfuview
      SET REVIEWED_THIS_MONTH = 0 
      WHERE dmdunit = :OLD.dmdunit 
        AND loc = :OLD.loc
        AND REVIEWED_THIS_MONTH = 1;
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      SCPOMGR.SYNC_TRIGGER_FLAGS.g_sync_in_progress := FALSE;
      RAISE;
  END;
  SCPOMGR.SYNC_TRIGGER_FLAGS.g_sync_in_progress := FALSE;
END;
/

原理：会话级变量g_sync_in_progress标记当前是否处于同步触发流程中，当触发器A执行时设置标记为TRUE，此时触发器B被触发会检测到标记并直接返回，避免循环调用；执行完成后重置标记，不影响正常的手动更新操作。

方案2：改用复合触发器（Oracle 11g及以上支持）

复合触发器可以在触发的不同阶段（BEFORE STATEMENT、AFTER EACH ROW等）执行逻辑，避免行级触发器中读取变异表的问题，同时通过收集变更数据在语句级统一处理，打破循环。

复合触发器实现（双向同步）

针对UDT_DFUVIEW的复合触发器

CREATE OR REPLACE TRIGGER SCPOMGR.TRG_DFU_SYNC_COMPOUND
FOR UPDATE OF REVIEWED_THIS_MONTH ON SCPOMGR.UDT_DFUVIEW
COMPOUND TRIGGER
  -- 收集需要同步的行数据
  TYPE t_sync_row IS RECORD (
    dmdunit UDT_DFUVIEW.dmdunit%TYPE,
    loc UDT_DFUVIEW.loc%TYPE,
    new_reviewed NUMBER
  );
  TYPE t_sync_table IS TABLE OF t_sync_row;
  g_sync_data t_sync_table := t_sync_table();

  -- BEFORE STATEMENT阶段初始化
  BEFORE STATEMENT IS
  BEGIN
    g_sync_data.DELETE;
  END BEFORE STATEMENT;

  -- AFTER EACH ROW阶段收集变更
  AFTER EACH ROW IS
  BEGIN
    g_sync_data.EXTEND;
    g_sync_data(g_sync_data.LAST).dmdunit := :OLD.dmdunit;
    g_sync_data(g_sync_data.LAST).loc := :OLD.loc;
    g_sync_data(g_sync_data.LAST).new_reviewed := :NEW.REVIEWED_THIS_MONTH;
  END AFTER EACH ROW;

  -- AFTER STATEMENT阶段批量同步
  AFTER STATEMENT IS
  BEGIN
    FORALL i IN g_sync_data.FIRST .. g_sync_data.LAST
      UPDATE scpomgr.dfuexception
      SET u_review = g_sync_data(i).new_reviewed
      WHERE dmdunit = g_sync_data(i).dmdunit
        AND loc = g_sync_data(i).loc
        AND exception = 20000
        AND u_review != g_sync_data(i).new_reviewed;
  END AFTER STATEMENT;
END TRG_DFU_SYNC_COMPOUND;
/

针对DFUEXCEPTION的复合触发器

CREATE OR REPLACE TRIGGER SCPOMGR.TRG_DFUEXCEPTION_SYNC_COMPOUND
FOR UPDATE OF u_review ON SCPOMGR.DFUEXCEPTION
COMPOUND TRIGGER
  TYPE t_sync_row IS RECORD (
    dmdunit DFUEXCEPTION.dmdunit%TYPE,
    loc DFUEXCEPTION.loc%TYPE,
    new_review NUMBER
  );
  TYPE t_sync_table IS TABLE OF t_sync_row;
  g_sync_data t_sync_table := t_sync_table();

  BEFORE STATEMENT IS
  BEGIN
    g_sync_data.DELETE;
  END BEFORE STATEMENT;

  AFTER EACH ROW IS
  BEGIN
    -- 仅处理异常类型20000的行
    IF :NEW.exception = 20000 THEN
      g_sync_data.EXTEND;
      g_sync_data(g_sync_data.LAST).dmdunit := :OLD.dmdunit;
      g_sync_data(g_sync_data.LAST).loc := :OLD.loc;
      g_sync_data(g_sync_data.LAST).new_review := :NEW.u_review;
    END IF;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    FORALL i IN g_sync_data.FIRST .. g_sync_data.LAST
      UPDATE scpomgr.udt_dfuview
      SET REVIEWED_THIS_MONTH = g_sync_data(i).new_review
      WHERE dmdunit = g_sync_data(i).dmdunit
        AND loc = g_sync_data(i).loc
        AND REVIEWED_THIS_MONTH != g_sync_data(i).new_review;
  END AFTER STATEMENT;
END TRG_DFUEXCEPTION_SYNC_COMPOUND;
/

原理：复合触发器在AFTER EACH ROW阶段收集所有变更行的数据，然后在AFTER STATEMENT阶段批量执行更新操作。此时原表的修改已经完成，不再处于变异状态，同时批量操作效率更高；另外，因为是语句级执行，不会触发行级触发器的循环调用问题。

方案3：改用存储过程统一处理更新逻辑（推荐用于复杂业务）

放弃触发器，改用存储过程封装两个表的更新逻辑，所有对这两个字段的修改都通过存储过程执行，从根源避免循环触发。

存储过程实现

CREATE OR REPLACE PROCEDURE SCPOMGR.SET_DFU_REVIEW_STATUS(
  p_dmdunit IN VARCHAR2,
  p_loc IN VARCHAR2,
  p_reviewed IN NUMBER
) IS
BEGIN
  -- 先更新UDT_DFUVIEW
  UPDATE scpomgr.udt_dfuview
  SET REVIEWED_THIS_MONTH = p_reviewed
  WHERE dmdunit = p_dmdunit
    AND loc = p_loc
    AND REVIEWED_THIS_MONTH != p_reviewed;

  -- 再同步更新DFUEXCEPTION（仅异常类型20000）
  UPDATE scpomgr.dfuexception
  SET u_review = p_reviewed
  WHERE dmdunit = p_dmdunit
    AND loc = p_loc
    AND exception = 20000
    AND u_review != p_reviewed;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END SET_DFU_REVIEW_STATUS;
/

使用方式：所有需要修改REVIEWED_THIS_MONTH或u_review的操作，都调用这个存储过程，而不是直接执行UPDATE语句。这种方式完全避免了触发器的循环问题，同时业务逻辑更加集中可控。

内容的提问来源于stack exchange，提问作者khris jones

避免触发器互更新引发的循环及ORA-04091数据变异问题

原触发器代码 #

触发器1（UDT_DFUVIEW更新触发） #

触发器2（DFUEXCEPTION更新触发） #

错误信息 #

方案1：添加会话级触发标记，打破循环调用 #

步骤1：创建会话级包存储标记 #

步骤2：修改两个触发器，加入标记判断 #