执行MERGE插入MST_PRO时触发ORA-04091错误，如何解决？

错误原因 #

ORA-04091的本质是：行级触发器TGR_MST_PRO执行时，MST_PRO表正被MERGE语句修改（处于变异状态），此时触发器内部查询MST_PRO的MAX(ID)违反了Oracle限制——行级触发器不允许在DML操作期间读取正在被修改的表。

解决方案一：使用序列生成自增ID（推荐） #

Oracle序列是官方推荐的自增ID生成方式，性能远优于触发器查询MAX(ID)，且能彻底避免表变异问题。

1. 创建序列（初始值设为当前MST_PRO的最大ID+1）：

CREATE SEQUENCE SEQ_MST_PRO_ID
START WITH 1 -- 替换为实际当前最大ID+1
INCREMENT BY 1
NOCACHE
NOCYCLE;

2. 修改原触发器，用序列替代查询逻辑：

CREATE OR REPLACE TRIGGER TGR_MST_PRO
BEFORE INSERT ON MST_PRO
FOR EACH ROW WHEN (NEW.ID IS NULL)
BEGIN
  :NEW.ID := SEQ_MST_PRO_ID.NEXTVAL;
END TGR_MST_PRO;

或者直接删除触发器，在MERGE语句中直接指定ID：

MERGE INTO MST_PRO tm
USING (SELECT SEQ_MST_PRO_ID.NEXTVAL AS ID, t.* FROM MST_PRO_TMP t) ts
ON (tm.P_NAME = ts.P_NAME)
WHEN NOT MATCHED THEN
INSERT(tm.ID, tm.P_TEAM, tm.P_NUMB, tm.P_NAME, tm.P_REQ, tm.P_DATE, tm.P_TYPE)
VALUES(ts.ID, ts.P_TEAM, ts.P_NUMB, ts.P_NAME, ts.P_REQ, ts.P_DATE, 'EXP');

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

复合触发器可在语句级提前获取表的最大ID，行级直接赋值，避免直接查询变异表：

CREATE OR REPLACE TRIGGER TGR_MST_PRO_COMPOUND
FOR INSERT ON MST_PRO
COMPOUND TRIGGER
  v_max_id NUMBER;

BEFORE STATEMENT IS
BEGIN
  SELECT NVL(MAX(ID), 0) INTO v_max_id FROM MST_PRO;
END BEFORE STATEMENT;

BEFORE EACH ROW WHEN (NEW.ID IS NULL)
BEGIN
  v_max_id := v_max_id + 1;
  :NEW.ID := v_max_id;
END BEFORE EACH ROW;
END TGR_MST_PRO_COMPOUND;

删除原行级触发器后，执行原MERGE语句即可。

解决方案三：提前计算插入ID，绕过触发器 #

先获取当前表的最大ID，预先生成待插入记录的ID序列，再执行MERGE：

DECLARE
  v_current_max_id NUMBER;
BEGIN
  SELECT NVL(MAX(ID), 0) INTO v_current_max_id FROM MST_PRO;
  
  MERGE INTO MST_PRO tm
  USING (
    SELECT v_current_max_id + ROWNUM AS ID, t.* 
    FROM MST_PRO_TMP t
    WHERE NOT EXISTS (SELECT 1 FROM MST_PRO tm2 WHERE tm2.P_NAME = t.P_NAME)
  ) ts
  ON (tm.P_NAME = ts.P_NAME)
  WHEN NOT MATCHED THEN
  INSERT(tm.ID, tm.P_TEAM, tm.P_NUMB, tm.P_NAME, tm.P_REQ, tm.P_DATE, tm.P_TYPE)
  VALUES(ts.ID, ts.P_TEAM, ts.P_NUMB, ts.P_NAME, ts.P_REQ, ts.P_DATE, 'EXP');
END;
/

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