PostgreSQL 触发器与变更审计（AFTER/BEFORE、Audit 表与验证）

---

title: PostgreSQL 触发器与变更审计（AFTER/BEFORE、Audit 表与验证）

date: 2025-11-26

keywords:

• 触发器
• 审计
• 变更日志
• plpgsql
• 事务

description: 使用AFTER/BEFORE触发器记录数据变更到审计表，包含变更前后数据、操作者与时间，提供实现与一致性验证，满足合规与追踪需求。

tags:

• PostgreSQL
• plpgsql
• 事务
• 变更日志
• 审计
• 数据与存储
• 数据库
• 触发器

categories:

• 文章资讯
• 编程技术

---

概述

触发器可在写操作发生时记录审计信息，包括旧值、新值与操作者。通过审计表与统一触发函数，在不影响主业务的前提下实现可追溯与合规。

关键实践与参数

• 审计范围: INSERT/UPDATE/DELETE全面覆盖
• 字段记录: op old new actor ts
• 事务一致: 审计与主写入同事务提交
• 压力控制: 审计表归档与分区以控制体量

示例/配置/实现

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  amount NUMERIC(12,2) NOT NULL,
  status TEXT NOT NULL,
  updated_by TEXT
);
CREATE TABLE orders_audit (
  id BIGSERIAL PRIMARY KEY,
  order_id BIGINT NOT NULL,
  op TEXT NOT NULL,
  old JSONB,
  new JSONB,
  actor TEXT,
  ts TIMESTAMPTZ DEFAULT now()
);

CREATE OR REPLACE FUNCTION fn_orders_audit() RETURNS trigger AS $$
BEGIN
  IF (TG_OP = 'INSERT') THEN
    INSERT INTO orders_audit(order_id, op, old, new, actor) VALUES (NEW.id, 'INSERT', NULL, to_jsonb(NEW), NEW.updated_by);
    RETURN NEW;
  ELSIF (TG_OP = 'UPDATE') THEN
    INSERT INTO orders_audit(order_id, op, old, new, actor) VALUES (NEW.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW), NEW.updated_by);
    RETURN NEW;
  ELSIF (TG_OP = 'DELETE') THEN
    INSERT INTO orders_audit(order_id, op, old, new, actor) VALUES (OLD.id, 'DELETE', to_jsonb(OLD), NULL, OLD.updated_by);
    RETURN OLD;
  END IF;
  RETURN NULL;
END; $$ LANGUAGE plpgsql;

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

验证

• 全量覆盖: 对三类操作均产生审计记录
• 一致性: 审计与主数据在同事务内写入，无漏记
• 可追溯: 审计表包含操作者与时间，查询可检索
• 体量控制: 分区与归档策略有效，查询性能稳定

注意事项

• 审计表增长需归档与分区
• 在高写入场景评估性能影响
• 注意敏感字段的脱敏与访问控制
• 结合应用层记录来源IP与请求ID