Oracle触发器中PRAGMA AUTONOMOUS_TRANSACTION不生效问题排查

你的触发器触发失败时无法写入sales_log，核心是三个问题：

• 自治事务声明位置错误：PRAGMA AUTONOMOUS_TRANSACTION必须放在触发器的DECLARE段，不能放在EXCEPTION块里。放在EXCEPTION块里会导致自治事务不生效，异常块的操作仍属于主事务，主事务回滚时日志也会跟着回滚。
• 变量引用错误：插入sales_log时用了salesid，但你声明的变量是v_salesid，会触发未定义变量的错误，导致日志插入失败。
• 绑定变量遗漏冒号：EXCEPTION块里的NEW.salesid/OLD.salesid必须加冒号，写成:NEW.salesid/:OLD.salesid，否则Oracle无法识别这是行级触发器的绑定变量。

以下是修正后的完整代码：

create or replace trigger sales_trg_all
after insert or update or delete on sales_tbl
for each row
declare 
    action varchar2(10);
    v_salesid number;
    -- 将自治事务声明放在DECLARE段
    PRAGMA AUTONOMOUS_TRANSACTION;
begin    
    if inserting then 
        insert into sales_audit (salesid,salesdt,type) values (:NEW.salesid,:NEW.salesdt,'I');
    ELSIF updating then 
        insert into sales_audit (salesid,salesdt,type) values (:NEW.salesid,:NEW.salesdt,'U');
    ELSIF deleting then 
        insert into sales_audit (salesid,salesdt,type) values (:OLD.salesid,:OLD.salesdt,'D');
    END IF;
EXCEPTION 
    WHEN OTHERS THEN
        IF inserting then 
            action :='I';
            v_salesid := :NEW.salesid; -- 绑定变量加冒号
        ELSIF updating then 
            action :='U';
            v_salesid := :NEW.salesid; -- 绑定变量加冒号
        ELSIF deleting then 
            action :='D';
            v_salesid := :OLD.salesid; -- 绑定变量加冒号
        END IF;
        
        -- 引用正确的变量v_salesid
        insert into sales_log (salesID,type) values (v_salesid,action);
        commit;
END;

额外建议：可以给sales_log表增加error_code和error_msg字段，在EXCEPTION块里记录具体错误信息，方便排查失败原因，示例插入语句：

insert into sales_log (salesID,type, error_code, error_msg) 
values (v_salesid,action, SQLCODE, SQLERRM);

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