如何使用SQL触发器自动记录数据变更日志-SQL-PHP中文网

mysql中通过old和new伪记录获取变更值：update触发器可同时访问old（变更前）和new（变更后）；insert仅可用new；delete仅可用old；二者均为只读，字段需显式引用，如old.name、new.email，且必须匹配触发器类型，否则报错。

如何使用sql触发器自动记录数据变更日志

触发器里怎么拿到变更前后的值

MySQL 的 BEFORE 和 AFTER 触发器能分别访问 OLD 和 NEW 伪记录，但必须匹配操作类型：UPDATE 触发器才能同时用两者；INSERT 只有 NEW；DELETE 只有 OLD。漏判操作类型会导致 Unknown column 'OLD.xxx' in 'field list' 错误。

常见写法是先判断操作类型再拼日志内容，比如：

IF TG_OP = 'UPDATE' THEN
  INSERT INTO audit_log (...) VALUES (... , OLD.name, NEW.name, ...);
ELSIF TG_OP = 'INSERT' THEN
  INSERT INTO audit_log (...) VALUES (... , NULL, NEW.name, ...);
END IF;

OLD 和 NEW 是只读的，不能在触发器里修改它们
PostgreSQL 用 TG_OP 判断操作类型；MySQL 没有内置变量，得靠触发器类型（BEFORE INSERT / AFTER UPDATE）隐式区分
字段名必须显式写出，不能用 OLD.* —— 多数数据库不支持这种语法

日志表设计要避开主键冲突和性能陷阱

日志表不该有业务主键，推荐用自增 id + 时间戳 created_at + 操作类型 action（'INSERT'/'UPDATE'/'DELETE'）+ 关联表名 table_name + 主键值 row_id 组合。别把原表主键直接当日志主键，否则同一行反复更新会覆盖日志。

如果原表主键是复合键，row_id 字段得改成 row_id_json 或拆成多个字段（如 user_id, order_no），否则无法准确还原上下文。

日志表加索引时优先考虑 (table_name, row_id, created_at)，方便按业务对象查变更历史
避免在触发器里做耗时操作，比如调用外部 HTTP、写文件、或连表 JOIN 复杂查询 —— 这会让事务变慢甚至卡死
MySQL 5.7+ 支持触发器内写入其他表，但事务失败时日志也会回滚；PostgreSQL 的触发器默认在事务内执行，同样受回滚影响

INSERT/UPDATE/DELETE 触发器必须分开建

没有数据库支持一个触发器响应多种操作。你必须为每张需要审计的表，分别创建三个触发器：audit_table_name_insert、audit_table_name_update、audit_table_name_delete。少建一个，对应操作就留白。

PostgreSQL 示例（函数封装后复用）：

CREATE OR REPLACE FUNCTION log_changes() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, action, row_id, old_data, new_data, changed_by)
  VALUES (TG_TABLE_NAME, TG_OP, NEW.id, row_to_json(OLD), row_to_json(NEW), current_user);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER t_audit_users_insert AFTER INSERT ON users
  FOR EACH ROW EXECUTE FUNCTION log_changes();

MySQL 不支持函数参数传 OLD/NEW，所以每个触发器都得手写 INSERT 语句，没法复用逻辑
触发器命名要有规律，否则后期排查哪个表漏了审计很难定位
触发器对 DML 效率有硬性损耗，高并发写入场景下，建议异步落日志（比如通过消息队列），而不是强依赖触发器同步写

权限和调试时最容易忽略的点

触发器以定义者权限（DEFINER）运行，不是调用者权限。如果创建触发器的账号没日志表的 INSERT 权限，所有相关 DML 都会报错：ERROR 1142 (42000): INSERT command denied to user。

调试时别只看主表操作是否成功，要主动查日志表有没有新记录，并检查 old_data 和 new_data 字段是否为空或结构异常 —— 常见原因是 JSON 序列化时遇到大字段（如 TEXT 超长）、NULL 值未处理、或字段权限被限制导致读不到。

MySQL 8.0+ 的 JSON_OBJECT() 可以安全拼字段，但注意它不包含 NULL 字段；要用 JSON_OBJECTAGG() + 子查询才能保全
PostgreSQL 的 row_to_json() 默认忽略不可见列（如生成列），需确认目标字段是否实际可读
上线前务必在测试环境模拟批量 UPDATE 场景，观察锁表现和日志延迟 —— 单条没问题，批量可能暴露出隐式锁竞争