触发器在SQL数据库中的最佳实践是什么

触发器只该用于强制跨表约束、维护审计字段、写不可绕过的日志；其他逻辑优先用应用层、CHECK约束、DEFAULT或存储过程替代，因其强一致性保障不可被绕过且必须由数据库原子性执行。

触发器不是“自动补丁”，它只该在数据库层做三件事：强制跨表约束、维护审计字段、写不可绕过的日志。其他所有逻辑，优先用应用层、CHECK 约束、DEFAULT 或存储过程替代。

什么时候必须用触发器？

只有当业务规则无法被声明式语法覆盖，且必须由数据库强保证时才考虑：

• 订单插入时同步扣减库存，并原子性校验余额（FOREIGN KEY 无法表达“余额 ≥ 订单金额”）
• 用户表 UPDATE 时自动更新 updated_at 和 updated_by，且不允许应用层绕过
• 敏感表（如 salary）每次变更都写入带 OLD/NEW 值的审计日志，且日志表必须与主事务一致回滚

注意：ON UPDATE CURRENT_TIMESTAMP 比触发器更轻量；CHECK 约束比触发器报错更清晰；批量导入时，触发器会为每一行执行一次——10万行就是10万次调用。

BEFORE vs AFTER：别拿错 NEW/OLD

MySQL 和 PostgreSQL 中，NEW 和 OLD 的可用性严格依赖触发时机：

• BEFORE INSERT：可读写 NEW，但 NEW.id 是 NULL（即使字段是 AUTO_INCREMENT）
• AFTER INSERT：NEW.id 才可用，适合写关联日志或调用函数
• UPDATE 触发器里判断字段是否真改了？别用 OLD.col != NEW.col —— NULL 会让整个条件失效。MySQL 用 NOT (OLD.col NEW.col)，PostgreSQL 用 OLD.col IS DISTINCT FROM NEW.col
• BEFORE DELETE 只能读 OLD；AFTER DELETE 同样，但不能对原表再做 DML（MySQL 直接报 Can't update table 'xxx' in stored function/trigger）

触发器里最常踩的性能坑

它不是异步钩子，而是同步阻塞执行，每行都走一遍，锁和资源开销直接叠加：

• 在 AFTER INSERT 里写 INSERT INTO log SELECT ... FROM big_table WHERE user_id = NEW.user_id？查大表 + 锁等待，极易触发 Lock wait timeout exceeded
• 用触发器实时更新汇总字段（如部门总薪资）？每次改一个员工就扫全树，数据量一涨就雪崩
• 多个触发器监听同一事件（比如两个 AFTER UPDATE）？执行顺序按创建时间定，没显式依赖就等于埋雷
• 触发器里调 NOW() 或自定义函数？MySQL 主从复制可能不一致，除非函数声明为 DETERMINISTIC 或切到 ROW 格式 binlog

压测时一定要对比开启/关闭触发器的 QPS 和锁等待次数——真实批量操作下，触发器开销远超单行测试。

怎么让触发器不至于失控？

生产环境里，触发器得像开关一样可控、可观测、可熔断：

• 开头加短路判断：IF @trigger_disabled = 1 THEN RETURN; END IF;，配合配置表或会话变量动态停用
• 所有副作用操作（发通知、调外部服务）必须抽离：触发器只写一条记录到 notify_queue 表，由独立作业消费
• 禁止在触发器里写 COMMIT、ROLLBACK 或 SAVEPOINT ——它天然属于宿主事务，出错就该让整个语句失败
• 每个触发器开头加注释说明作用、影响范围、是否可跳过，并记录到数据库文档表中（很多团队连这个都没有）

最容易被忽略的点是：触发器没有“版本管理”。上线后没人记得它存在，改表结构时可能意外破坏触发逻辑，而错误表现只是某类更新变慢或静默丢数据——它不像应用代码能被 Git 跟踪。