Oracle触发器报错致主事务回滚，如何绕过错误保留主事务？

Oracle默认情况下，触发器属于主事务的一部分，触发器执行失败会直接导致主事务回滚。要实现主事务正常执行、仅记录审计错误的需求，需通过自治事务隔离错误日志操作，同时在触发器异常块中不抛出异常。

修正后的代码实现 #

1. 错误日志存储过程（自治事务） #

CREATE OR REPLACE PROCEDURE PR_erlog(
  p_sqlcode IN NUMBER,
  p_sqlerm IN VARCHAR2
) IS 
  PRAGMA AUTONOMOUS_TRANSACTION; -- 标记为自治事务，独立于主事务
BEGIN
  INSERT INTO txn_log (sqlcode, sqlerm) VALUES(p_sqlcode, p_sqlerm);
  COMMIT; -- 自治事务必须显式提交
END PR_erlog;
/

2. 修正后的审计触发器 #

CREATE OR REPLACE TRIGGER audit_txn_cust 
AFTER UPDATE OR DELETE OR INSERT ON txn_cust
FOR EACH ROW
DECLARE
  v_sqlcode NUMBER;
  v_sqlerm VARCHAR2(4000);
BEGIN
  -- 执行审计插入逻辑
  IF INSERTING THEN 
    INSERT INTO txn_audit (txn_id, txn_number, txn_desc, type) 
    VALUES (:new.txn_id, :new.txn_number, :new.txn_desc, 'I');
  ELSIF UPDATING THEN 
    INSERT INTO txn_audit (txn_id, txn_number, txn_desc, type) 
    VALUES (:new.txn_id, :new.txn_number, :new.txn_desc, 'U');
  ELSIF DELETING THEN 
    INSERT INTO txn_audit (txn_id, txn_number, txn_desc, type) 
    VALUES (:OLD.txn_id, :OLD.txn_number, :OLD.txn_desc, 'D');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    -- 捕获错误信息
    v_sqlcode := SQLCODE;
    v_sqlerm := SUBSTR(SQLERRM, 1, 4000); -- 截取过长的错误信息
    -- 调用自治事务存储过程记录错误
    PR_erlog(v_sqlcode, v_sqlerm);
    -- 关键：不重新抛出异常，主事务继续执行
END audit_txn_cust;
/

关键修改说明 #

• 存储过程修正：
  • 补全参数定义，原代码缺少输入参数的语法结构，且VARCHAR2未指定长度。
  • 自治事务语法位置正确，确保日志操作在独立事务中执行，不影响主事务。
• 触发器修正：
  • 修复原代码中INSERT语句缺少分号的语法错误。
  • 异常块中正确捕获并传递错误信息，移除原调用存储过程时多余的逗号。
  • 异常处理后不抛出异常，这是主事务能继续执行的核心——若抛出异常，主事务仍会回滚。
• 业务注意点：
  • 此方案会导致审计数据可能缺失（主事务成功但审计失败），需根据业务对审计完整性的要求评估是否适用。
  • 确保txn_log表的字段能容纳错误信息，sqlerm建议设为VARCHAR2(4000)。

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