Oracle中如何实现提交后触发的SQL触发器？

原代码存在的问题 #

• 行级AFTER触发器会在每行插入/更新后立即触发，无法满足提交后触发的需求
• 语法错误：:NEW.T.var1写法错误，:NEW已代表当前触发行，应直接使用:NEW.var1
• 缺少WHERE子句，原UPDATE语句会修改整张表，而非目标行

实现思路（基于Oracle） #

标准SQL无直接支持提交后触发的触发器，但可通过行级触发器+临时表+系统事件触发器组合实现：

1. 用临时表记录提交前需要修改的行ID
2. 行级触发器在数据变更时标记符合条件的行
3. 提交后触发系统触发器，批量更新标记行并清空临时表

具体代码 #

1. 创建会话级临时表

CREATE GLOBAL TEMPORARY TABLE temp_need_update (
    row_id ROWID
) ON COMMIT PRESERVE ROWS;

2. 行级触发器：标记需要修改的行

CREATE OR REPLACE TRIGGER tr_mark_er_encours
AFTER INSERT OR UPDATE ON T
FOR EACH ROW
BEGIN
    IF :NEW.var1 = 0 THEN
        INSERT INTO temp_need_update (row_id) VALUES (:NEW.ROWID);
    END IF;
END;
/

3. 提交后系统触发器：执行批量更新

CREATE OR REPLACE TRIGGER tr_reset_er_encours_after_commit
AFTER COMMIT ON DATABASE
BEGIN
    -- 更新标记的行
    UPDATE T
    SET var1 = NULL
    WHERE ROWID IN (SELECT row_id FROM temp_need_update);
    
    -- 清空临时表，避免影响下一次提交
    DELETE FROM temp_need_update;
END;
/

注意事项 #

• 临时表为会话隔离，每个会话仅处理自身提交的变更
• 系统触发器需ADMINISTER DATABASE TRIGGER权限，若仅需针对特定用户，可在触发器中加入IF USER = '目标用户' THEN ... END IF判断
• 确保逻辑仅处理符合条件的行，避免不必要的性能损耗

内容的提问来源于stack exchange，提问作者Валентин Д.