Oracle 19中AFTER STATEMENT触发器仍触发Mutating Table Error原因咨询

问题背景 #

在Oracle 19c环境下，DEPT表与EMP表存在触发器依赖：DEPT表的行级触发器DEPT_TRG1更新时会修改EMP表数据，EMP表的复合触发器EMP_TRG2在AFTER STATEMENT阶段读取DEPT表。执行UPDATE DEPT SET SAL_FACTOR = 4 WHERE DEPTNO = 40;时，仍触发ORA-04091（变异表错误），即使EMP_TRG2使用了AFTER STATEMENT阶段也无法规避。

相关代码与报错信息 #

DEPT表及触发器DDL #

-- DEPT表
CREATE TABLE "DEPT" 
("DEPTNO" NUMBER(2,0), 
 "DNAME" VARCHAR2(14), 
 "LOC" VARCHAR2(13), 
 "SAL_FACTOR" NUMBER(4,0)
 );

-- DEPT唯一索引
CREATE UNIQUE INDEX "SYS_C0031557" ON "DEPT" ("DEPTNO");

-- DEPT_TRG1触发器
CREATE OR REPLACE EDITIONABLE TRIGGER "DEPT_TRG1" 
before insert or update on dept
for each row
begin
    if :new.deptno is null then
        select dept_seq.nextval into :new.deptno from sys.dual;
    end if;

    if :new.sal_factor is not null then
        update emp set new_sal = sal * :new.sal_factor where deptno = :new.deptno;
    else
        update emp set new_sal = null where deptno = :new.deptno;
    end if;
end;
/
ALTER TRIGGER "DEPT_TRG1" ENABLE;

-- DEPT主键约束
ALTER TABLE "DEPT" ADD PRIMARY KEY ("DEPTNO") USING INDEX ENABLE;

EMP表及触发器DDL #

-- EMP表
CREATE TABLE "EMP" 
("EMPNO" NUMBER(4,0), 
 "ENAME" VARCHAR2(10), 
 "JOB" VARCHAR2(9), 
 "MGR" NUMBER(4,0), 
 "HIREDATE" DATE, 
 "SAL" NUMBER(7,2), 
 "COMM" NUMBER(7,2), 
 "DEPTNO" NUMBER(2,0), 
 "NEW_SAL" NUMBER(7,0), 
 "DEPT_SAL_FACTOR" NUMBER(4,0)
 );

-- EMP唯一索引
CREATE UNIQUE INDEX "SYS_C0031556" ON "EMP" ("EMPNO");

-- EMP_TRG2复合触发器
CREATE OR REPLACE EDITIONABLE TRIGGER "EMP_TRG2" 
for insert or update on emp
compound trigger
    deptno# dept.deptno%type;

    after each row is
    begin
        deptno# := :new.deptno;
    end after each row;

    after statement is
        factor# number(4);
    begin
         select sal_factor
           into factor#
          from dept where deptno = deptno#;
    end after statement;
end;
/
ALTER TRIGGER "EMP_TRG2" ENABLE;

-- EMP_TRG1触发器
CREATE OR REPLACE EDITIONABLE TRIGGER "EMP_TRG1" 
before insert or update on emp
for each row
begin
    if :new.empno is null then
        select emp_seq.nextval into :new.empno from sys.dual;
    end if;
end;
/
ALTER TRIGGER "EMP_TRG1" ENABLE;

-- EMP约束
ALTER TABLE "EMP" MODIFY ("EMPNO" NOT NULL ENABLE);
ALTER TABLE "EMP" ADD PRIMARY KEY ("EMPNO") USING INDEX ENABLE;

-- EMP外键约束
ALTER TABLE "EMP" ADD FOREIGN KEY ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE;
ALTER TABLE "EMP" ADD FOREIGN KEY ("MGR") REFERENCES "EMP" ("EMPNO") ENABLE;

执行的DML语句 #

update dept set sal_factor = 4 where deptno = 40;

报错信息 #

Error starting at line : 1 in command -
update dept set sal_factor = 4 where deptno = 40
Error at Command Line : 1 Column : 47
Error report -
SQL Error: ORA-04091: table SETICIS.DEPT is mutating, trigger/function may not see it
ORA-06512: at "SETICIS.EMP_TRG2", line 20
ORA-04088: error during execution of trigger 'SETICIS.EMP_TRG2'
ORA-06512: at "SETICIS.DEPT_TRG1", line 8
ORA-04088: error during execution of trigger 'SETICIS.DEPT_TRG1'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
       this statement) attempted to look at (or modify) a table that was
       in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

原因分析 #

复合触发器的AFTER STATEMENT阶段仅能避免触发器所在表自身的变异问题，但此场景是跨表嵌套触发，核心逻辑链如下：

1. 顶层语句UPDATE DEPT启动，DEPT表进入被修改状态（事务未完成）
2. 触发DEPT_TRG1（行级BEFORE触发器），在该触发器内部执行UPDATE EMP
3. UPDATE EMP触发EMP_TRG2，此时顶层的UPDATE DEPT语句仍未执行完毕，DEPT表处于变异状态（被当前顶层事务修改中）
4. EMP_TRG2的AFTER STATEMENT阶段尝试读取DEPT表，Oracle判定该表正被顶层语句修改，因此抛出ORA-04091错误

简言之：只要处于顶层修改语句的执行上下文内，任何嵌套触发器都无法访问被顶层语句修改的表，与自身触发器阶段无关。

解决方案 #

1. 使用包变量传递数据 #

在DEPT_TRG1中把需要的SAL_FACTOR存入包变量，EMP_TRG2直接读取包变量而非查询DEPT表：

-- 创建存储包
CREATE OR REPLACE PACKAGE DEPT_EMP_DATA_PKG IS
    g_sal_factor NUMBER(4);
    g_deptno DEPT.DEPTNO%TYPE;
END DEPT_EMP_DATA_PKG;
/

-- 修改DEPT_TRG1
CREATE OR REPLACE EDITIONABLE TRIGGER "DEPT_TRG1" 
before insert or update on dept
for each row
begin
    if :new.deptno is null then
        select dept_seq.nextval into :new.deptno from sys.dual;
    end if;

    DEPT_EMP_DATA_PKG.g_sal_factor := :new.sal_factor;
    DEPT_EMP_DATA_PKG.g_deptno := :new.deptno;

    if :new.sal_factor is not null then
        update emp set new_sal = sal * :new.sal_factor where deptno = :new.deptno;
    else
        update emp set new_sal = null where deptno = :new.deptno;
    end if;
end;
/

-- 修改EMP_TRG2
CREATE OR REPLACE EDITIONABLE TRIGGER "EMP_TRG2" 
for insert or update on emp
compound trigger
    deptno# dept.deptno%type;

    after each row is
    begin
        deptno# := :new.deptno;
    end after each row;

    after statement is
        factor# number(4);
    begin
         -- 直接读取包变量，避免查询DEPT表
         factor# := DEPT_EMP_DATA_PKG.g_sal_factor;
    end after statement;
end;
/

2. 使用自治事务（需注意数据一致性） #

在EMP_TRG2的AFTER STATEMENT阶段使用自治事务，使其脱离顶层事务上下文读取DEPT表，但自治事务无法看到顶层事务未提交的修改，需根据业务场景判断是否适用：

CREATE OR REPLACE EDITIONABLE TRIGGER "EMP_TRG2" 
for insert or update on emp
compound trigger
    deptno# dept.deptno%type;

    after each row is
    begin
        deptno# := :new.deptno;
    end after each row;

    after statement is
        factor# number(4);
        PRAGMA AUTONOMOUS_TRANSACTION; -- 声明自治事务
    begin
         select sal_factor
           into factor#
          from dept where deptno = deptno#;
         COMMIT; -- 自治事务必须提交/回滚
    end after statement;
end;
/

3. 重构触发器逻辑 #

将读取DEPT表的逻辑移到DEPT表的AFTER STATEMENT触发器中，避免跨表嵌套触发时的访问问题：

-- 删除EMP_TRG2中读取DEPT的逻辑，改为在DEPT的AFTER STATEMENT触发器中处理
CREATE OR REPLACE EDITIONABLE TRIGGER "DEPT_TRG_AFTER_STMT" 
after update on dept
for each row
declare
    factor# NUMBER(4);
begin
    select sal_factor into factor# from dept where deptno = :new.deptno;
    -- 这里处理原本EMP_TRG2中需要的逻辑
end;
/
ALTER TRIGGER "DEPT_TRG_AFTER_STMT" ENABLE;

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