Oracle如何通过触发器将user表更新明细写入带CLOB字段的日志表

核心思路 #

使用行级 AFTER UPDATE 触发器，每次user表有更新操作时，逐行捕获字段变更前后的值，拼接为要求的明细格式后插入user_log表。

步骤1：确认表结构 #

你提到的两张表结构示例如下，可根据实际业务调整：

-- user 表
CREATE TABLE "user" (
    id_user NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    statut NUMBER
);
-- 插入测试数据
INSERT INTO "user" VALUES (1, 'aaa', 1);
INSERT INTO "user" VALUES (2, 'bbb', 1);
COMMIT;

-- user_log 表
CREATE TABLE user_log (
    "date" DATE,
    nb_rows_affected NUMBER,
    details CLOB
);

注意：user和date都是 Oracle 保留关键字，建表时建议加双引号，或者更换为非关键字的表名/字段名。

步骤2：创建更新触发器 #

CREATE OR REPLACE TRIGGER trg_user_after_update
AFTER UPDATE ON "user"
FOR EACH ROW -- 行级触发，每更新一行执行一次
DECLARE
    v_details CLOB;
BEGIN
    -- 初始化明细内容
    v_details := '';
    -- 判断 statut 字段是否变更，变更则记录明细
    IF :OLD.statut != :NEW.statut OR (:OLD.statut IS NULL AND :NEW.statut IS NOT NULL) OR (:OLD.statut IS NOT NULL AND :NEW.statut IS NULL) THEN
        v_details := v_details || 'on table user ; id = ' || :OLD.id_user || ' , previous value of user.statut = ''' || :OLD.statut || ''' actual value of user.statut = ''' || :NEW.statut || '''' || CHR(10);
    END IF;
    -- 判断 name 字段是否变更，变更则记录明细
    IF :OLD.name != :NEW.name OR (:OLD.name IS NULL AND :NEW.name IS NOT NULL) OR (:OLD.name IS NOT NULL AND :NEW.name IS NULL) THEN
        v_details := v_details || 'on table user ; id = ' || :OLD.id_user || ' , previous value of user.name = ''' || :OLD.name || ''' actual value of user.name = ''' || :NEW.name || '''';
    END IF;
    -- 插入日志表
    INSERT INTO user_log ("date", nb_rows_affected, details)
    VALUES (CURRENT_DATE, 1, v_details);
END;
/

测试验证 #

执行你提到的更新操作（注意原 SQL 的and要改成逗号，符合 UPDATE 语法规范）：

UPDATE "user" SET statut = 2, name = 'ccc' WHERE id_user = 1;
COMMIT;

查询user_log表即可得到符合要求的日志记录。 #

MySQL 的行触发器语法略有差异，NEW/OLD 不需要加:前缀，CLOB 对应为 TEXT 或 LONGTEXT 类型：

-- 先确认表结构，关键字用反引号包裹
CREATE TABLE `user` (
    id_user INT PRIMARY KEY,
    name VARCHAR(50),
    statut TINYINT
);
INSERT INTO `user` VALUES (1, 'aaa', 1), (2, 'bbb', 1);

CREATE TABLE user_log (
    `date` DATE,
    nb_rows_affected INT,
    details LONGTEXT
);

-- 创建触发器
DELIMITER //
CREATE TRIGGER trg_user_after_update
AFTER UPDATE ON `user`
FOR EACH ROW
BEGIN
    DECLARE v_details LONGTEXT DEFAULT '';
    -- 记录 statut 变更
    IF OLD.statut <> NEW.statut OR (OLD.statut IS NULL XOR NEW.statut IS NULL) THEN
        SET v_details = CONCAT(v_details, 'on table user ; id = ', OLD.id_user, ' , previous value of user.statut = ''', OLD.statut, ''' actual value of user.statut = ''', NEW.statut, '''\n');
    END IF;
    -- 记录 name 变更
    IF OLD.name <> NEW.name OR (OLD.name IS NULL XOR NEW.name IS NULL) THEN
        SET v_details = CONCAT(v_details, 'on table user ; id = ', OLD.id_user, ' , previous value of user.name = ''', OLD.name, ''' actual value of user.name = ''', NEW.name, '''');
    END IF;
    -- 插入日志
    INSERT INTO user_log (`date`, nb_rows_affected, details)
    VALUES (CURDATE(), 1, v_details);
END //
DELIMITER ;

补充说明 #

如果需要统计单次 UPDATE 语句影响的总行数而不是每行记1，可以使用语句级触发器配合临时表实现，常规单条更新场景下上述行级触发器完全满足需求。代码中已经兼容字段为 NULL 的变更场景，避免 NULL 值比较时的逻辑遗漏。

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