如何解决Oracle触发器触发ORA-04091（mutating table）错误问题

ORA-04091错误的核心原因是行级触发器运行过程中，Oracle不允许直接读取正在被修改的触发基表，避免读取到不一致的中间状态数据。
由于仅能在数据库侧做优化，可根据数据库版本选择以下两种可行方案：

方案1：复合触发器（推荐，适用于Oracle 11g及以上版本） #

复合触发器支持拆分触发执行阶段，我们将数据收集逻辑放在行级触发阶段，校验逻辑放在语句级执行完成后的阶段，此时表的变动已经落地，不会触发变异表限制，同时仅校验本次修改涉及的员工编号，性能损耗极低。
完整代码如下：

CREATE OR REPLACE TRIGGER trg_tablea_exp_check
FOR INSERT OR UPDATE ON TableA
COMPOUND TRIGGER
  -- 定义存储本次修改涉及员工编号的集合
  TYPE empno_arr IS TABLE OF TableA.EMPNO%TYPE INDEX BY PLS_INTEGER;
  g_modified_empnos empno_arr;
  g_index PLS_INTEGER := 0;

  -- 行级触发阶段：收集所有涉及EXP1/EXP4变动的员工编号
  BEFORE EACH ROW IS
  BEGIN
    IF :NEW.EXPCODE IN ('EXP1', 'EXP4') OR :OLD.EXPCODE IN ('EXP1', 'EXP4') THEN
      g_index := g_index + 1;
      g_modified_empnos(g_index) := :NEW.EMPNO;
    END IF;
  END BEFORE EACH ROW;

  -- 语句级执行完成后阶段：统一校验规则
  AFTER STATEMENT IS
    v_exp1_years NUMBER(7,2);
    v_exp4_years NUMBER(7,2);
  BEGIN
    -- 遍历所有本次涉及的员工编号去重校验
    FOR i IN 1..g_modified_empnos.COUNT LOOP
      SELECT NVL(MAX(CASE WHEN EXPCODE = 'EXP1' THEN EXPYEARS END), 0),
             NVL(MAX(CASE WHEN EXPCODE = 'EXP4' THEN EXPYEARS END), 0)
      INTO v_exp1_years, v_exp4_years
      FROM TableA
      WHERE EMPNO = g_modified_empnos(i);
      
      -- 触发校验规则
      IF v_exp4_years < v_exp1_years THEN
        raise_application_error(-20010, '员工'||g_modified_empnos(i)||'的EXP4经验年限不能小于EXP1经验年限');
      END IF;
    END LOOP;
  END AFTER STATEMENT;
END trg_tablea_exp_check;
/

该方案支持单条、批量插入/更新操作，执行操作时即时返回错误提示，无需等到事务提交。

方案2：物化视图+CHECK约束（适用于Oracle 11g以下版本） #

如果数据库版本不支持复合触发器，可以通过快速刷新物化视图加约束的方式实现规则校验，校验逻辑在事务提交时触发：

1. 先为TableA创建物化视图日志，支持快速刷新：

CREATE MATERIALIZED VIEW LOG ON TableA 
WITH ROWID, PRIMARY KEY (EMPNO, EXPCODE, EXPYEARS) 
INCLUDING NEW VALUES;

2. 创建聚合物化视图，按员工维度聚合EXP1、EXP4的年限值：

CREATE MATERIALIZED VIEW mv_tablea_exp_check
REFRESH FAST ON COMMIT
AS
SELECT EMPNO,
       NVL(MAX(CASE WHEN EXPCODE='EXP1' THEN EXPYEARS END), 0) exp1_years,
       NVL(MAX(CASE WHEN EXPCODE='EXP4' THEN EXPYEARS END), 0) exp4_years
FROM TableA
GROUP BY EMPNO;

3. 为物化视图添加校验约束：

ALTER TABLE mv_tablea_exp_check 
ADD CONSTRAINT chk_exp4_ge_exp1 CHECK (exp4_years >= exp1_years);

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