Oracle触发器触发应用报错时如何实现操作日志插入不回滚

• 执行顺序错误：原触发器先判断时间，符合拦截条件时直接抛出错误，后续的日志写入逻辑根本不会执行
• 事务绑定问题：触发器运行在触发它的DML操作所在的主事务中，RAISE_APPLICATION_ERROR会触发整个主事务回滚，就算日志写入逻辑执行了，也会跟着主事务一起被回滚
• 逻辑配置错误：原代码判断的是周二（Tuesday）和周日，不符合你要限制周六周日的需求；同时周几判断依赖数据库语言环境，中文环境下TO_CHAR(sysdate,'Day')返回中文会导致判断失效；日志中标记操作类型为updation（更新）和实际插入操作不符

核心使用Oracle自治事务实现日志独立存储，自治事务和主事务完全隔离，主事务回滚不会影响自治事务的提交结果。

步骤1：创建自治事务日志存储过程 #

create or replace procedure p_log_weekend_action(
    p_dept_no number, -- 可根据你的weekend_actions表实际字段类型调整
    p_op_type varchar2,
    p_op_desc varchar2
) as
-- 声明为自治事务
pragma autonomous_transaction;
begin
    insert into user_admin.weekend_actions values (p_dept_no, p_op_type, p_op_desc);
    -- 独立提交日志事务
    commit;
end;
/

步骤2：修改触发器逻辑 #

create or replace trigger tgr_wkd_action
before insert
on tbl_39_dept_k 
for each row
declare
    v_week_day varchar2(10);
begin
    -- 指定英文语言环境获取周几标识，避免多语言环境兼容问题
    v_week_day := trim(TO_CHAR(sysdate,'DY','NLS_DATE_LANGUAGE=AMERICAN'));
    -- 先写入操作日志，调用自治事务存储过程，日志不会被主事务回滚
    p_log_weekend_action(
        :NEW.Dept_no,
        'insert',
        '用户'||user||' 尝试在 '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||' 向表 tbl_39_dept_k 插入数据'
    );
    -- 拦截周六、周日的插入操作
    IF v_week_day IN ('SAT','SUN') then 
        RAISE_APPLICATION_ERROR (-20000,'禁止在周末执行数据插入操作');
    end if;
end tgr_wkd_action;
/

补充说明 #

如果你原本的需求是限制所有DDL操作而非单表的INSERT操作，原触发器类型错误，需要替换为数据库级DDL触发器，逻辑和上述方案一致，同样调用自治事务存储过程写入日志即可。

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