怎么在PostgreSQL中使用SQL触发器实现审计追踪表？-SQL-PHP中文网

触发器函数必须返回TRIGGER类型，不能用SELECT直接返回值；INSERT/UPDATE触发器通常RETURN NEW，DELETE用RETURN OLD；中止操作需RETURN NULL；TG_OP、OLD、NEW仅在触发器函数内有效。

怎么在postgresql中使用sql触发器实现审计追踪表？

触发器函数必须返回`TRIGGER`类型且不能用`SELECT`直接返回值

PostgreSQL要求触发器函数的返回类型必须是TRIGGER，常见错误是写成RETURNS void或RETURNS text，导致创建触发器时报错ERROR: function must return type trigger。函数体里也不能用RETURN (SELECT ...)这种写法——RETURN NEW或RETURN OLD才是合法响应。

实操建议：

函数声明必须写RETURNS TRIGGER
INSERT/UPDATE触发器通常RETURN NEW，DELETE用RETURN OLD
若需中止操作（比如校验失败），用RETURN NULL，此时原SQL不会生效
避免在函数里调用pg_sleep()或长事务操作，否则会阻塞主表DML

CREATE OR REPLACE FUNCTION log_user_changes()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO user_audit_log (
    table_name, operation, row_id, old_data, new_data, changed_by, changed_at
  ) VALUES (
    'users',
    TG_OP,
    COALESCE(NEW.id, OLD.id),
    TO_JSONB(OLD),
    TO_JSONB(NEW),
    current_user,
    NOW()
  );
  RETURN NEW; -- INSERT/UPDATE走这里；DELETE需单独分支处理
END;
$$ LANGUAGE plpgsql;

`tg_op`和`old`/`new`变量只在触发器上下文中有效

不能在普通SQL里引用TG_OP、OLD或NEW——它们是PL/pgSQL触发器函数的隐式变量，仅在CREATE FUNCTION ... RETURNS TRIGGER定义的函数体内可用。误以为能用在视图或CTE里，会导致column "tg_op" does not exist之类报错。

使用场景和注意事项：

TG_OP值为'INSERT'、'UPDATE'、'DELETE'或'TRUNCATE'（仅支持行级触发器）
OLD在UPDATE/DELETE中存在，INSERT中为NULL；NEW在INSERT/UPDATE中存在，DELETE中为NULL
对JSONB字段存变更数据时，用TO_JSONB(OLD)比手拼字符串更安全，自动处理NULL、特殊字符和类型转换
若审计表字段少于主表（比如不存大文本字段），建议显式列出需要审计的列，而非无脑TO_JSONB(OLD)，避免冗余存储和权限泄露

触发器定义时`FOR EACH ROW`不能省略，且`WHEN`条件有语法限制

PostgreSQL不支持语句级（FOR EACH STATEMENT）的行变更审计，因为无法获取单行的OLD/NEW。漏写FOR EACH ROW会默认创建语句级触发器，导致OLD和NEW不可用，运行时报错record "old" is not assigned。

关于WHEN子句：

只能用WHEN (condition)，括号不能少，且condition里只能访问OLD和NEW字段，不能调用函数或子查询
例如WHEN (NEW.email IS DISTINCT FROM OLD.email)可只审计邮箱变更；但WHEN (is_sensitive_column_updated())会报错
如果逻辑复杂，应移到触发器函数内部判断，而非强塞进WHEN
注意IS DISTINCT FROM比!=更安全，能正确处理NULL比较

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

审计表设计要预留扩展性，别直接复制主表结构

审计表不是主表镜像。硬搬主表结构（比如带serial主键、UNIQUE约束、索引）会拖慢写入，还可能因主表DDL变更导致审计失败。典型坑是给old_data/new_data字段加NOT NULL，结果INSERT时OLD为空就报错。

合理设计要点：

主键用bigserial或gen_random_uuid()（需pgcrypto），别复用业务主键
old_data和new_data用JSONB，NOT NULL设为FALSE，允许NULL
加created_at（非updated_at），审计记录本身不该被更新
关键查询字段如table_name、operation、row_id建复合索引，例如ON user_audit_log(table_name, operation, row_id, created_at)
定期按时间分区（PARTITION BY RANGE (created_at)），不然单表超亿行后COUNT(*)都卡