SQL Academy · 课时

使用触发器审计表

Q: 「使用触发器审计表」这节课中我会学到什么？

使用将记录写入 audit_log 表的 AFTER INSERT/UPDATE/DELETE 触发器构建审计轨迹 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

使用将记录写入 audit_log 表的 AFTER INSERT/UPDATE/DELETE 触发器构建审计轨迹

使用触发器审计表 是 CoddyKit 上的免费 SQL Academy 课时。这是第 4 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

为什么需要审计

审计日志可以回答“谁在什么时候更改了什么”。合规要求（HIPAA、SOX、GDPR 的被遗忘权调查）和运营取证都需要它。

审计表

一个中心表记录每项更改：

CREATE TABLE audit_log (
  id BIGSERIAL PRIMARY KEY,
  ts TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  user_name TEXT NOT NULL DEFAULT CURRENT_USER,
  table_name TEXT NOT NULL,
  action TEXT NOT NULL,             -- INSERT, UPDATE, DELETE
  row_id TEXT,
  old_data JSONB,
  new_data JSONB
);

审计触发器函数

一个函数即可在多个表之间重复使用：

CREATE OR REPLACE FUNCTION audit_row()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, action, row_id, old_data, new_data)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    COALESCE(NEW.id::TEXT, OLD.id::TEXT),
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

附加到表

在 AFTER INSERT/UPDATE/DELETE 时触发：

CREATE TRIGGER trg_audit_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_row();

to_jsonb(NEW)

to_jsonb 技巧可以通用地捕获整行数据——无需为每一列编写代码。它适用于任何包含 id 列的表。

跟踪操作者

如果应用使用单个数据库用户运行，仅靠 CURRENT_USER 并不足够。请通过会话 GUC 传递应用用户：

-- App sets:
SET LOCAL app.user_id = '42';

-- Trigger reads:
INSERT INTO audit_log (... actor_id ...) VALUES (..., current_setting('app.user_id', true)::BIGINT);

审计特定列

使用触发器 WHEN 子句进行选择性审计：

CREATE TRIGGER trg_audit_role_change
AFTER UPDATE OF role ON users
FOR EACH ROW WHEN (OLD.role IS DISTINCT FROM NEW.role)
EXECUTE FUNCTION audit_role_change();

每表一个审计表

另一种方案是为每个实际表使用一个审计表（例如 users_audit），并保留相同的架构及审计元数据。这样更容易查询，但需要维护更多 DDL。

仅审计差异

仅存储发生更改的列：

INSERT INTO audit_log (table_name, action, changes)
VALUES (
  TG_TABLE_NAME, TG_OP,
  (SELECT jsonb_object_agg(key, value)
   FROM jsonb_each(to_jsonb(NEW))
   WHERE NEW.* IS DISTINCT FROM OLD.* AND to_jsonb(NEW)->key IS DISTINCT FROM to_jsonb(OLD)->key)
);

审计表性能

在繁忙系统中，审计表会快速增长。请按时间进行分区：

CREATE TABLE audit_log (...) PARTITION BY RANGE (ts);
CREATE TABLE audit_log_2024_q1 PARTITION OF audit_log
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

性能权衡

现在每次插入、更新或删除都会额外写入一行。对于写入极其频繁的表，这可能会使输入/输出翻倍。请进行测试和监控，然后决定这种权衡是否值得。

何时不应使用数据库触发器

如果您需要与事件总线集成，请使用 NOTIFY 或 LISTEN 将事件排入队列，或者使用逻辑复制 / CDC 工具（Debezium），而不是触发器。

回顾

审计触发器是捕获历史记录的最简单方式。

使用 to_jsonb(NEW/OLD) 的通用函数
附加到每个需要审计的表
按时间对审计表进行分区
通过 GUC 显式跟踪应用操作者

快速检查

在审计触发器函数中，如何通用地将新行捕获为 JSONB？

常见问题解答

「使用触发器审计表」课时是免费的吗？

是的 — 「使用触发器审计表」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「使用触发器审计表」这节课中我会学到什么？

使用将记录写入 audit_log 表的 AFTER INSERT/UPDATE/DELETE 触发器构建审计轨迹你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 4 节课，共 4 节。

「使用触发器审计表」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Academy