怎样在触发器中记录数据变更日志？

wen IT资讯 2026-06-03 242

本文目录导读：

怎样在触发器中记录数据变更日志？

设计审计日志表
MySQL 触发器示例
PostgreSQL 触发器示例
关键注意事项
更通用的方案（动态获取字段）

在数据库触发器中记录数据变更日志（审计日志）是常见的需求，以下以 MySQL 和 PostgreSQL 为例,说明具体实现方法。

设计审计日志表

首先需要一个专门的表来存储变更记录。

CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100),          -- 被修改的表名
    operation_type VARCHAR(10),       -- INSERT / UPDATE / DELETE
    old_data JSON,                    -- 修改前的数据（可选）
    new_data JSON,                    -- 修改后的数据（可选）
    changed_by VARCHAR(100),          -- 操作人（需应用传入）
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

注意：old_data 和 new_data 建议使用 JSON 类型（MySQL 5.7+ / PostgreSQL 9.4+）,便于存储不规则字段。

MySQL 触发器示例

假设有一个 users 表：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT 触发器

DELIMITER //
CREATE TRIGGER trg_users_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, operation_type, new_data, changed_by)
    VALUES (
        'users',
        'INSERT',
        JSON_OBJECT(
            'id', NEW.id,
            'name', NEW.name,
            'email', NEW.email
        ),
        @current_user   -- 应用层设置的会话变量
    );
END//
DELIMITER ;

UPDATE 触发器

DELIMITER //
CREATE TRIGGER trg_users_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, operation_type, old_data, new_data, changed_by)
    VALUES (
        'users',
        'UPDATE',
        JSON_OBJECT(
            'id', OLD.id,
            'name', OLD.name,
            'email', OLD.email
        ),
        JSON_OBJECT(
            'id', NEW.id,
            'name', NEW.name,
            'email', NEW.email
        ),
        @current_user
    );
END//
DELIMITER ;

DELETE 触发器

DELIMITER //
CREATE TRIGGER trg_users_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, operation_type, old_data, changed_by)
    VALUES (
        'users',
        'DELETE',
        JSON_OBJECT(
            'id', OLD.id,
            'name', OLD.name,
            'email', OLD.email
        ),
        @current_user
    );
END//
DELIMITER ;

PostgreSQL 触发器示例

PostgreSQL 中语法略有不同,但逻辑一致。

-- 创建审计表（使用 JSONB）
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name TEXT,
    operation_type TEXT,
    old_data JSONB,
    new_data JSONB,
    changed_by TEXT,
    changed_at TIMESTAMP DEFAULT NOW()
);
-- 创建审计函数（返回 TRIGGER）
CREATE OR REPLACE FUNCTION audit_users_func()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, operation_type, new_data, changed_by)
        VALUES (TG_TABLE_NAME, TG_OP, row_to_json(NEW)::jsonb, current_user);
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation_type, old_data, new_data, changed_by)
        VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb, current_user);
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation_type, old_data, changed_by)
        VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD)::jsonb, current_user);
    END IF;
    RETURN NEW;  -- 对 AFTER 触发器无影响
END;
$$ LANGUAGE plpgsql;
-- 创建触发器（支持 INSERT/UPDATE/DELETE 合并在一个触发器中）
CREATE TRIGGER trg_users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_users_func();

优势：PostgreSQL 可以使用 TG_OP 判断操作类型，row_to_json() 自动生成 JSON 数据,代码更简洁。

关键注意事项

性能影响：每个 DML 操作都会额外写一次审计表，高频表需考虑采用异步写入（如消息队列）或批量归档。
事务一致性：触发器与主操作在同一事务内，主操作回滚时审计日志也会回滚,数据一致性好。
记录操作人：MySQL 无法直接获取应用层的用户,通常通过会话变量传递：
```
SET @current_user = 'username';
```
PostgreSQL 可使用 current_user 获取数据库用户，如需业务用户需应用层设置 SET session "app.user" = 'xxx'。
大字段处理：若表包含 TEXT / BLOB 等大字段，建议只记录关键字段,或压缩后存储。
维护索引：审计表通常按 table_name, changed_at 查询,建议建组合索引：
```
CREATE INDEX idx_audit_table_time ON audit_log (table_name, changed_at);
```
数据归档：审计日志增长快,建议定期归档到历史表或冷存储。
避免循环触发：若审计表本身也有触发器,需禁用或注意死循环。

更通用的方案（动态获取字段）

如果需要为多个表写审计，可以使用动态 SQL 或事件触发器（PostgreSQL 支持）。

示例：通用 PostgreSQL 审计函数（简化版）

CREATE OR REPLACE FUNCTION audit_generic_func()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation_type, old_data, new_data, changed_by)
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN row_to_json(OLD)::jsonb ELSE NULL END,
        CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN row_to_json(NEW)::jsonb ELSE NULL END,
        current_user
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

然后将该函数绑定到需要的表：

CREATE TRIGGER trg_users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_generic_func();
CREATE TRIGGER trg_orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_generic_func();