如何通过Oracle触发器实现user表更新时自动写入CLOB类型的user_log日志

前置注意事项 #

Oracle 内置关键字USER不能直接作为表名使用，如果你实际的表名确实为user，所有SQL操作中都需要用双引号包裹为"user"避免语法报错，以下代码均已适配该场景。你示例中的UPDATE语句语法存在错误，SET多个字段需要用逗号分隔，不能用AND，正确写法参考下文测试用例。

1. 创建行级UPDATE触发器 #

在user表上创建AFTER UPDATE行级触发器，利用:old和:new关键字获取每行变更前后的数值，拼接后写入日志表：

CREATE OR REPLACE TRIGGER trg_user_after_update
AFTER UPDATE ON "user"
FOR EACH ROW -- 行级触发，每更新1行执行1次
DECLARE
    v_details CLOB;
BEGIN
    -- 按要求拼接变更详情内容
    v_details := 'new values (statut = ' || :new.statut || ' and name = ''' || :new.name || ''') old values (statut = ' || :old.statut || ' and name = ''' || :old.name || ''') for id_user= ' || :old.id_user;

    -- 写入日志表
    INSERT INTO user_log ("date", nb_rows_affected, details)
    VALUES (SYSDATE, 1, v_details);
END;
/

2. 可选优化 #

如果需要过滤无实际字段变更的无效日志（比如执行UPDATE语句但所有字段值和原值一致的场景），可以在触发器中增加判断逻辑：

CREATE OR REPLACE TRIGGER trg_user_after_update
AFTER UPDATE ON "user"
FOR EACH ROW
DECLARE
    v_details CLOB;
BEGIN
    -- 仅在字段值发生变化时记录日志
    IF :new.statut != :old.statut OR :new.name != :old.name THEN
        v_details := 'new values (statut = ' || :new.statut || ' and name = ''' || :new.name || ''') old values (statut = ' || :old.statut || ' and name = ''' || :old.name || ''') for id_user= ' || :old.id_user;
        INSERT INTO user_log ("date", nb_rows_affected, details)
        VALUES (SYSDATE, 1, v_details);
    END IF;
END;
/

3. 功能验证 #

执行测试UPDATE语句验证效果：

-- 执行更新操作
UPDATE "user" SET statut = 2, name = 'ccc' WHERE id_user = 1;
-- 提交事务
COMMIT;
-- 查询日志表查看生成的记录
SELECT * FROM user_log;

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