Oracle复合触发器创建报错PLS-00679、ORA-04091问题求助

报错原因 #

• PLS-00679错误的核心原因是：语句级触发器块（BEFORE STATEMENT/AFTER STATEMENT）作用于整次SQL执行周期，不存在行级上下文，因此不允许使用行级绑定变量:NEW、:OLD，你当前将行级变量放在了BEFORE STATEMENT块中执行，直接触发该报错。
• 你之前遇到的ORA-04091变异表错误，是因为普通行级触发器中不允许读取当前正在被DML操作修改的表，使用复合触发器的思路是正确的，只是使用方式不符合规则。

正确解决方法 #

实现思路 #

利用复合触发器的多阶段执行特性，分三步完成校验：

1. 触发器全局声明区定义集合类型，存储每行需要校验的关联字段值
2. 行级执行阶段（AFTER EACH ROW）将每行的关联参数存入集合，此时不做跨表查询，规避变异表问题
3. 语句级结束阶段（AFTER STATEMENT）统一遍历集合，关联operation表计算sold值，不符合规则直接抛错

修正后代码 #

create or replace TRIGGER CHECK_SOLD_OPERATION_UPDATE
FOR UPDATE OF ENGAGED ON FICHE_ENGAGE_DEP 
COMPOUND TRIGGER
-- 全局声明：定义存储行校验参数的集合类型
TYPE check_rec IS RECORD(
    id_op operation.id_operation%TYPE,
    new_engaged FICHE_ENGAGE_DEP.ENGAGED%TYPE,
    old_engaged FICHE_ENGAGE_DEP.ENGAGED%TYPE,
    new_type_fiche FICHE_ENGAGE_DEP.type_fiche%TYPE
);
TYPE check_list IS TABLE OF check_rec INDEX BY PLS_INTEGER;
g_check_list check_list;

AFTER EACH ROW IS
BEGIN
    -- 行级阶段仅存储参数，不查询关联表
    g_check_list(g_check_list.COUNT + 1).id_op := :NEW.id_operation;
    g_check_list(g_check_list.COUNT).new_engaged := :NEW.engaged;
    g_check_list(g_check_list.COUNT).old_engaged := NVL(:OLD.engaged, 0);
    g_check_list(g_check_list.COUNT).new_type_fiche := :NEW.type_fiche;
END AFTER EACH ROW;

AFTER STATEMENT IS
    v_sold NUMBER;
BEGIN
    -- 语句级阶段统一遍历校验，规避变异表问题
    FOR i IN 1..g_check_list.COUNT LOOP
        select sold_operation(g_check_list(i).id_op) - g_check_list(i).new_engaged + g_check_list(i).old_engaged 
        into v_sold
        from operation
        where id_operation = g_check_list(i).id_op;
        
        IF v_sold < 0 AND g_check_list(i).new_type_fiche > 2 THEN
            RAISE_APPLICATION_ERROR(-20101, 'ERROR: 操作余额不足，无法更新');
        END IF;
    END LOOP;
END AFTER STATEMENT;
END CHECK_SOLD_OPERATION_UPDATE;
/

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