如何捕获Oracle触发器内部的各类错误并存储至日志表

现有代码问题 #

• 语法错误：
  • 变量l_err声明的类型拼写错误，应为varchar2而非varcha2
  • 开头的SELECT INTO语句末尾缺失分号
  • 两个INSERT语句的列声明末尾多了多余逗号（column4、column8后面的逗号），会直接触发编译报错
  • 触发器声明为AFTER UPDATE事件，内部写了IF INSERTING的判断逻辑完全无效，该触发器永远不会触发INSERT分支
• 功能问题：
  • 日志写入未使用自治事务，主事务回滚时日志也会被同步回滚，导致错误记录丢失
  • 日志仅记录了错误栈，未提取表名、错误行号、关联操作列信息
  • 无法识别触发错误的具体DML操作类型

修正后代码 #

如果你的需求是同时支持UPDATE和INSERT触发，触发器事件要改成AFTER INSERT OR UPDATE，修正后代码如下：

create or replace TRIGGER user_name.sample_trg 
AFTER INSERT OR UPDATE ON user_name.transaction_tb
FOR EACH ROW
DECLARE
    variable_ln  number;
    l_err        varchar2(4000);
    l_table_name varchar2(100) := 'transaction_tb';
    l_col_name   varchar2(100);
    l_err_line   number;
    -- 声明自治事务，保证日志不随主事务回滚
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    select column_value
    into variable_ln
    from tb1
    where colum_1 = :NEW.colum_1;

    IF UPDATING THEN
        INSERT INTO hisotry_tb
        (
            column1,
            column2,
            column3,
            column4
        )
        VALUES
        (
            :NEW.column1,
            :NEW.column2,
            :NEW.column3,
            :NEW.column4
        );
    END IF; 

    IF INSERTING THEN
        INSERT INTO hisotry_tb
        (
            column5,
            column6,
            column7,
            column8
        )
        VALUES
        (
            :NEW.column5,
            :NEW.column6,
            :NEW.column7,
            :NEW.column8
        );
    END IF;     
        
EXCEPTION
  WHEN OTHERS THEN
    l_err := DBMS_UTILITY.FORMAT_ERROR_STACK || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
    -- 提取错误行号
    l_err_line := REGEXP_SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 'line (\d+)', 1, 1, NULL, 1);
    -- 提取错误对应的列名（以常见的ORA-01400非空约束错误、ORA-00904无效列错误为例，可根据需要扩展匹配规则）
    l_col_name := REGEXP_SUBSTR(SQLERRM, '\((.*?)\)', 1, 1, NULL, 1);
    
    INSERT INTO log (erro_msg, trigger_name, table_name, column_name, err_line, operate_type) 
    VALUES (l_err, 'sample_trg', l_table_name, l_col_name, l_err_line, CASE WHEN INSERTING THEN 'INSERT' WHEN UPDATING THEN 'UPDATE' END);
    -- 自治事务需要主动提交
    COMMIT;
    DBMS_OUTPUT.put_line (l_err);
END;
/

额外说明 #

• 错误栈DBMS_UTILITY.FORMAT_ERROR_STACK本身已经包含错误类型、关联的表名、列名信息，比如非空约束错误、列不存在错误都会在错误信息里打印对应的表和列名，不需要额外硬编码匹配
• 如果仅需要触发器支持UPDATE事件，删除INSERTING分支和触发器声明里的INSERT事件即可
• 如果不需要日志独立于主事务，删除自治事务声明和COMMIT即可，但出错回滚时日志会丢失

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