如何用SQL触发器维护历史版本数据-SQL-PHP中文网

before触发器在dml前执行，可校验、修改new值或用old取变更前数据；after在dml后执行，适合日志、统计等事后操作，但不可修改new，且old可能失真。

如何用sql触发器维护历史版本数据

触发器能维护行级历史版本，但不能自动补旧数据、不能捕获DDL变更、也不能替代应用层的业务逻辑控制——它只是一段在DML执行时自动跑的SQL逻辑，用错地方反而拖垮性能。

BEFORE还是AFTER？关键看你要存哪一刻的数据

想存变更前的快照（比如UPDATE前的整行），必须用BEFORE UPDATE或BEFORE DELETE，此时OLD是唯一可靠来源；AFTER里OLD虽仍可用，但若业务逻辑在UPDATE中又改了字段，可能和原始值不一致。别在AFTER里写SELECT * FROM main WHERE id = NEW.id来“找回旧值”——读到的是刚改完还没提交的行，不是真旧值。

BEFORE INSERT适合记录创建上下文（如生成trace_id），但拿不到自增主键的真实值
AFTER INSERT才能拿到真实NEW.id，适合做关联写入（如往日志表写id+时间）
BEFORE UPDATE/DELETE必须直接用OLD.*插入历史表，不能查原表

INSERT/UPDATE/DELETE必须拆成三个独立触发器

MySQL和PostgreSQL虽语法上支持单触发器响应多种DML，但混写极易出错：UPDATE触发器里误判DELETE逻辑、DELETE时查不到关联数据静默失败、字段变更判断漏掉NULL……实际维护中，三个操作必须分三张触发器建。

orders_after_insert：只存NEW.*，op_type = 'I'
orders_after_update：存OLD.*（旧快照）+ NEW.*（新值），建议全量落库，避免漏字段
orders_after_delete：只存OLD.*，且必须立刻写入——行已物理删除，再查就没了

历史表字段和索引不提前规划，触发器就是定时炸弹

触发器同步执行，主表UPDATE卡住，90%是因为历史表没索引或引擎不对。最常被忽略的两点：source_id（原表主键）和op_time必须建联合索引；历史表不能设外键指向原表，否则触发MySQL的ERROR 1442。

历史表字段要显式列出，尤其当比原表多op_type、op_time等字段时，INSERT INTO hist SELECT OLD.*会因列数不匹配报错
op_type用ENUM('I','U','D')或CHAR(1)，别用INT——查日志时WHERE op_type = 'U'比= 2直观且安全
高并发下慎用(SELECT COALESCE(MAX(version), 0) + 1 FROM hist WHERE user_id = OLD.id)，可能重复；更稳的是让应用层生成version（如雪花ID）

NULL比较必须用安全语法，否则条件永远失效

判断字段是否真变了，不能写OLD.col != NEW.col——遇到NULL时整个表达式结果为UNKNOWN，条件不成立，导致该记的日志没记。MySQL 8.0.22+用NOT (OLD.col IS NOT DISTINCT FROM NEW.col)，老版本得手写(OLD.col != NEW.col) OR (OLD.col IS NULL AND NEW.col IS NOT NULL) OR (OLD.col IS NOT NULL AND NEW.col IS NULL)。