如何用SQL触发器实现订单状态变更审计-SQL-PHP中文网

触发器必须建在orders表上，因审计核心是捕获其status字段更新；需用after update配合old/new值比对，仅当status真实变更时写入审计记录，并存order_id、old_status、new_status、updated_at及上下文字段。

如何用sql触发器实现订单状态变更审计

触发器该建在哪个表上？

订单状态变更审计的核心是捕获 orders 表中 status 字段的更新，所以触发器必须建在 orders 表上。不要试图在日志表或中间表上建——那会漏掉原始变更源头。MySQL 和 PostgreSQL 都支持 AFTER UPDATE 触发器，但注意：SQL Server 要求用 INSTEAD OF 或配合 UPDATE() 函数判断字段，而 SQLite 不支持行级触发器（除非用 WAL 模式 + 特定编译选项，实际项目中基本不可靠）。

怎么只记录真正的状态变更？

直接写 INSERT INTO audit_log ... 会把所有 UPDATE 都记下来，哪怕 status 没变。必须显式比较新旧值：

IF OLD.status != NEW.status THEN
  INSERT INTO order_status_audit (order_id, old_status, new_status, updated_at, updated_by)
  VALUES (NEW.id, OLD.status, NEW.status, NOW(), NEW.updated_by);
END IF;

注意三点：
• PostgreSQL 用 OLD/NEW，MySQL 同样；SQL Server 用 deleted/inserted 表，需 JOIN 判断
• != 在 NULL 场景下失效，MySQL 推荐用（NULL 安全等于），PostgreSQL 用 IS DISTINCT FROM
• updated_by 若来自应用层传入，务必确认该字段在 UPDATE 语句中被显式设置，否则触发器读到的是 NULL 或默认值

审计字段要不要存冗余信息？

只存 order_id、old_status、new_status 看似够用，但线上排查时往往需要上下文。建议至少加：
• updated_at（用 NOW() 或 CURRENT_TIMESTAMP，别依赖应用传的时间）
• ip_address 或 user_id（若应用层能通过 SET @app_user_id = ? 注入变量，MySQL 可读 @app_user_id；PostgreSQL 可用 current_setting('app.user_id', TRUE)）
• trigger_source 枚举字段（如 'manual'、'payment_webhook'、'timeout_job'），方便区分是人工操作还是系统自动流转

不建议在触发器里查关联表（比如 JOIN users 表取用户名）——会拖慢主事务，且违反“审计只记录事实”的原则。真要关联，放到异步消费日志表时再做。

触发器执行失败会导致订单更新失败吗？

会。触发器属于事务一部分，一旦插入审计表失败（比如 audit_log 表结构变更、磁盘满、唯一键冲突），整个 UPDATE orders 就会回滚。这是双刃剑：
• 好处：保证状态变更和审计记录强一致
• 风险：审计表出问题 = 订单系统不可用

缓解方法：
• 把 audit_log 表引擎设为 ARCHIVE（MySQL）或分区表（PostgreSQL），降低锁争用
• 加监控：定期查 information_schema.triggers 是否失效，查最近 5 分钟是否有触发器报错日志
• 关键业务可考虑降级：用 INSERT IGNORE 或 ON CONFLICT DO NOTHING（PostgreSQL）让审计失败不阻塞主流程——但得接受“可能丢审计记录”这个事实

真正难缠的是跨库场景：订单在 db_main，审计表在 db_log。MySQL 不支持跨库触发器（除非用 FEDERATED 引擎，但稳定性差），这时只能放弃触发器，改用应用层双写或 CDC 工具。