Oracle PL/SQL触发器触发变异表错误，求非自治事务解决方案

你的触发器触发变异表错误的核心原因是行级触发器中直接访问了触发表X2（删除分支的SELECT COUNT(1) FROM X2）——Oracle行级触发器执行时，触发表处于未提交的中间状态，禁止查询/修改自身，这就是变异表错误的根源。同时原触发器的逻辑存在两处问题：

• E1是主键，不需要比较:NEW.E1和X1现有E1的大小，二者必然一致
• 更新逻辑仅和当前修改行比较，未考虑X2中同一E1下的其他行，无法保证X1存储的是全局最大值

下面提供两种无需自治事务的解决方案：

方案1：语句级触发器+MERGE（简洁高效，适合中小数据量） #

改用语句级触发器，在整个DML操作完成后执行，此时X2状态稳定，不会触发变异表错误。用MERGE语句一次性完成插入、更新，同时清理X1中X2已不存在的E1记录：

CREATE OR REPLACE TRIGGER TR_UPDATE_ON_X2
AFTER INSERT OR UPDATE OR DELETE ON X2
BEGIN
    -- 第一步：删除X1中X2已无对应E1的记录
    DELETE FROM X1 x1
    WHERE NOT EXISTS (SELECT 1 FROM X2 x2 WHERE x2.E1 = x1.E1);
    
    -- 第二步：同步X2中各E1的最大E2、E3到X1
    MERGE INTO X1 x1
    USING (
        SELECT E1, MAX(E2) AS MAX_E2, MAX(E3) AS MAX_E3
        FROM X2
        GROUP BY E1
    ) x2
    ON (x1.E1 = x2.E1)
    WHEN MATCHED THEN
        UPDATE SET x1.E2 = x2.MAX_E2, x1.E3 = x2.MAX_E3
    WHEN NOT MATCHED THEN
        INSERT (E1, E2, E3) VALUES (x2.E1, x2.MAX_E2, x2.MAX_E3);
END;
/

方案2：复合触发器（适合大数据量，仅处理变化的E1） #

如果X2数据量较大，全表扫描会影响性能，可使用复合触发器：行级收集所有发生变化的E1，语句级仅针对这些E1做同步，减少不必要的计算：

CREATE OR REPLACE TRIGGER TR_UPDATE_ON_X2
FOR INSERT OR UPDATE OR DELETE ON X2
COMPOUND TRIGGER
    -- 定义存储变化E1的集合
    TYPE t_e1_list IS TABLE OF X2.E1%TYPE;
    v_e1_list t_e1_list := t_e1_list();
    
    -- 行级逻辑：收集所有被修改的E1
    AFTER EACH ROW IS
    BEGIN
        IF INSERTING OR UPDATING THEN
            v_e1_list.EXTEND;
            v_e1_list(v_e1_list.LAST) := :NEW.E1;
        END IF;
        IF DELETING THEN
            v_e1_list.EXTEND;
            v_e1_list(v_e1_list.LAST) := :OLD.E1;
        END IF;
    END AFTER EACH ROW;
    
    -- 语句级逻辑：仅处理收集到的E1
    AFTER STATEMENT IS
    BEGIN
        -- 清理X1中对应E1已在X2中消失的记录
        FORALL i IN 1..v_e1_list.COUNT
            DELETE FROM X1 x1
            WHERE x1.E1 = v_e1_list(i)
            AND NOT EXISTS (SELECT 1 FROM X2 x2 WHERE x2.E1 = x1.E1);
        
        -- 同步变化E1的最大E2、E3到X1
        MERGE INTO X1 x1
        USING (
            SELECT E1, MAX(E2) AS MAX_E2, MAX(E3) AS MAX_E3
            FROM X2
            WHERE E1 IN (SELECT COLUMN_VALUE FROM TABLE(v_e1_list))
            GROUP BY E1
        ) x2
        ON (x1.E1 = x2.E1)
        WHEN MATCHED THEN
            UPDATE SET x1.E2 = x2.MAX_E2, x1.E3 = x2.MAX_E3
        WHEN NOT MATCHED THEN
            INSERT (E1, E2, E3) VALUES (x2.E1, x2.MAX_E2, x2.MAX_E3);
    END AFTER STATEMENT;
END TR_UPDATE_ON_X2;
/

关键说明 #

• 两种方案均避免了行级触发器访问触发表的问题，语句级操作时X2已完成DML，状态稳定
• MERGE语句替代了原触发器中繁琐的存在性判断和逐行更新，逻辑更简洁可靠
• 复合触发器通过仅处理变化的E1，大幅降低了大数据量场景下的性能开销

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