如何用 TRIGGER 记录 INSERT/UPDATE/DELETE 前后值到审计表

mysql中BEforE触发器OLD仅用于UPDATE/delete、NEW仅用于INSERT/UPDATE；postgresql统一用OLD/NEW（INSERT时OLD为NULL，DELETE时NEW为NULL）；审计字段需适配NULL及大文本，避免截断；应使用jsON_OBJECT或COALESCE处理空值；触发器与主事务强耦合，失败将导致原操作回滚。

TRIGGER 中如何获取 INSERT/UPDATE/DELETE 的旧值和新值

MySQL 和 PostgreSQL 的触发器机制差异较大，不能一概而论。MySQL 的 BEFORE 触发器里，OLD 仅对 UPDATE 和 DELETE 可用，NEW 仅对 INSERT 和 UPDATE 可用；PostgreSQL 则统一通过 OLD 和 NEW 访问（INSERT 时 OLD 为 NULL，DELETE 时 NEW 为 NULL）。关键点是：别在 BEFORE DELETE 里读 NEW.column_name，会报错 column "xxx" does not exist。

常见错误是写成：if TG_OP = 'UPDATE' THEN INSERT INTO audit_log ... VALUES (OLD.id, NEW.name); END IF; —— 看似合理，但漏了 OLD.name 和 NEW.name 都可能为 NULL，直接拼进日志会导致审计字段丢失。应显式用 COALESCE(OLD.name, '(null)') 或单独判断。

审计表设计要支持 NULL、TEXT 和大字段变更

如果源表某列是 TEXT 或 json，审计表对应字段必须同类型或更大（比如用 LONGTEXT），否则插入时被截断不报错，只丢数据。另外，OLD.value 和 NEW.value 字段建议设为 TEXT，别用 VARchar(255) —— 否则更新一个长地址字段，审计记录就只剩前 255 字符。

• 主键字段（如 id）建议存为 VARCHAR(100)，兼容整型、UUID、字符串主键
• 操作类型字段用 enum('INSERT','UPDATE','DELETE') 或 CHAR(7)，避免用 TEXT 增加索引开销
• 加 created_at timestamp default CURRENT_TIMESTAMP，别依赖应用层传时间，防止时钟不一致

MySQL 中用 BEFORE 触发器 + JSON_OBJECT 拼变更字段

MySQL 5.7+ 支持 JSON_OBJECT()，比手拼字符串更安全。例如记录哪些字段变了：

SET @old_data = JSON_OBJECT('name', OLD.name, 'status', OLD.status); SET @new_data = JSON_OBJECT('name', NEW.name, 'status', NEW.status); INSERT INTO audit_log (table_name, op_type, pk_id, old_data, new_data, created_at) VALUES ('users', 'UPDATE', OLD.id, @old_data, @new_data, NOW());

注意：不能在 BEFORE INSERT 中读 OLD.id，也不能在 BEFORE DELETE 中读 NEW.updated_at。PostgreSQL 更灵活，可用 ROW(OLD.*)::TEXT 整行转字符串，但可读性差，调试困难。

触发器性能与事务一致性风险

触发器在主 DML 语句同一事务中执行，所以审计失败（比如磁盘满、审计表被锁）会导致原操作回滚 —— 这既是保障，也是隐患。高并发写入场景下，审计表容易成为瓶颈。

规避方式有二：

• 审计逻辑尽量轻量：只存关键字段，不查关联表，不调函数
• 必要时改用异步方案（如 MySQL 的 BINLOG 解析 + kafka + flink），但失去事务原子性

最易被忽略的是：触发器里不能用 select ... FOR UPDATE 锁审计表自身，否则极易死锁；也不建议在触发器里写多条 INSERT 到不同审计表，分散写入反而增加事务膨胀风险。