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` - 事务一致: 审计与主写入同事务提交 - 压力控制: 审计表归档与分区以控制体量 ## 示例/配置/实现 ```sql 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