用trigger自动化任务

想象一下，你是个大型数据库的管理员。突然有人把表里的重要信息删了，大家都在喊：“是谁干的？！” 为了避免这种事，数据库给了我们记录变更和追踪数据历史的能力。这就是log和audit的用武之地。

• 变更日志能保存发生了什么：哪条记录被改了，怎么改的，啥时候改的。
• 数据审计用来更深入地检查，包括记录是谁发起的变更等信息。

现在你知道“为什么”要这么做了，咱们来学学“怎么做”。

创建日志表

在我们开始配置trigger之前，得先有个表来存变更日志。比如这样：

-- 创建变更日志表
CREATE TABLE change_logs (
    log_id SERIAL PRIMARY KEY,       -- 日志唯一ID
    table_name TEXT NOT NULL,        -- 发生变更的表名
    operation TEXT NOT NULL,         -- 操作类型: INSERT, UPDATE, DELETE
    change_time TIMESTAMP DEFAULT NOW(), -- 变更时间
    old_data JSONB,                  -- 变更前的数据（用于UPDATE/DELETE）
    new_data JSONB                   -- 变更后的数据（用于INSERT/UPDATE）
);

这里都干了啥？

1. log_id — 每条日志的唯一ID。
2. table_name — 记录是哪个表被改了。
3. operation — 操作类型：INSERT、UPDATE或DELETE。
4. change_time — 记录变更的准确时间。
5. old_data 和 new_data — 变更前后的数据，JSON格式存。

用trigger记录变更

现在有了日志表，咱们来给某个表，比如students，加个trigger。它会记录所有变更：新增学生、更新或删除。 我们要做这些：

1. 写个PL/pgSQL function，往日志表插记录。
2. 在students表上建trigger。

function会拿到操作类型（INSERT、UPDATE、DELETE），还有变更的数据（OLD和NEW）。

-- 记录变更到日志表的function
CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- 记录INSERT操作
    IF TG_OP = 'INSERT' THEN
        INSERT INTO change_logs (table_name, operation, new_data)
        VALUES ('students', 'INSERT', row_to_json(NEW));

    -- 记录DELETE操作
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO change_logs (table_name, operation, old_data)
        VALUES ('students', 'DELETE', row_to_json(OLD));

    -- 记录UPDATE操作
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO change_logs (table_name, operation, old_data, new_data)
        VALUES ('students', 'UPDATE', row_to_json(OLD), row_to_json(NEW));
    END IF;

    RETURN NULL; -- 返回NULL，因为这是AFTER trigger
END;
$$ LANGUAGE plpgsql;

这里：

• TG_OP — 特殊变量，当前操作类型：INSERT、UPDATE、DELETE。
• row_to_json(OLD) 和 row_to_json(NEW) — 把行数据转成JSON，方便存。
• RETURN NULL — 因为是AFTER trigger，不用返回变更后的数据。

现在把function和students表绑一起。

-- 创建记录变更的trigger
CREATE TRIGGER students_log_trigger
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_changes();

这里都干了啥？

• AFTER INSERT OR UPDATE OR DELETE — 这些操作后，trigger就会触发。
• FOR EACH ROW — 每条被改的记录都会触发一次。
• EXECUTE FUNCTION log_student_changes() — 调用我们写的日志function。

测试trigger

现在来试试trigger好不好使。

1. 插入新记录

INSERT INTO students (name, age, grade)
VALUES ('奥托 林', 20, 'A');

看看日志表里有啥：

SELECT * FROM change_logs;

比如结果：

2. 更新记录

UPDATE students
SET grade = 'B'
WHERE name = '奥托 林';

再查查日志表：

SELECT * FROM change_logs ORDER BY change_time DESC;

结果：

3. 删除记录

DELETE FROM students
WHERE name = '奥托 林';

再查查日志：

实际用例举例

1. 关键表的操作日志：比如银行账户表，必须记录所有变更，防止作弊。
2. 系统审计：你可以存这些记录，满足合规要求或分析用户行为。
3. 数据恢复保障：有人误删数据时，可以从日志表恢复回来。

注意点和小坑

用trigger做日志时，性能一定要注意。如果trigger触发太频繁，会让数据库压力变大。所以：

• 只在关键表上用日志trigger。
• 日志太多时，记得做归档策略。

trigger就像吉他上的弦：要调得准，才能弹出好听的声音。它能帮你自动化日常操作，还能让你牢牢掌控数据。