如何通过SQL触发器实现记录删除后自动存入历史归档表？

DELETE归档必须用AFTER而非INSTEAD OF触发器，因后者会替代删除操作导致数据未真正删除且无法归档；AFTER DELETE确保原表记录已物理移除后，再通过DELETED表稳定捕获并插入Archive表。

DELETE 触发器必须用 AFTER 而不是 INSTEAD OF

触发器在 DELETE 操作后存档，核心是捕获被删行数据。用 INSTEAD OF DELETE 会替代原操作，导致记录根本没删——这和需求矛盾。必须选 AFTER DELETE，确保原表记录已删、归档动作再执行。

常见错误是误以为 INSTEAD OF 更“安全”，结果发现原表空了但归档表也没数据，查日志才发现触发器压根没进执行体。

• AFTER DELETE 中可用 DELETED 临时表访问被删行（SQL Server）或 OLD 别名（MySQL/PostgreSQL）
• PostgreSQL 用 FOR EACH ROW，否则无法逐行存档；SQL Server 默认就是行级，但显式写上更稳妥
• 避免在触发器里做耗时操作（如远程 HTTP 调用），否则 DELETE 会卡住甚至超时

归档表字段要严格对齐原表，且额外加时间戳和操作标记

归档表不是简单复制结构。少一个字段或类型不匹配，触发器一执行就报 Column name or number of supplied values does not match table definition。

实操建议直接用原表 DDL 改写：CREATE TABLE orders_archive AS SELECT *, 'delete' AS op_type, GETDATE() AS archived_at FROM orders WHERE 1=0（SQL Server），再手动把主键、索引、NOT NULL 约束去掉——归档表不该有主键冲突，但 op_type 和 archived_at 必须有，否则分不清是谁删的、啥时候删的。

• 日期字段统一用 GETDATE()（SQL Server）、NOW()（MySQL）、CURRENT_TIMESTAMP（PostgreSQL），别依赖应用层传入
• 如果原表有 IDENTITY 或 SERIAL 字段，归档表对应列必须设为普通 INT，否则插入失败
• 大文本字段（如 TEXT、JSON）确认归档表对应类型支持同等长度，MySQL 的 TINYTEXT 存不下原 TEXT

多行删除时，DELETED/OLD 是结果集，不是单行变量

很多人写触发器时习惯用 SELECT @id = id FROM DELETED，这在删一行时侥幸成功，删多行就出错：SQL Server 报 Subquery returned more than 1 value，MySQL 直接语法报错。

正确做法是把 DELETED 当普通表 JOIN 或 INSERT ... SELECT。比如 SQL Server：

INSERT INTO orders_archive (id, customer_id, total, op_type, archived_at)SELECT id, customer_id, total, 'delete', GETDATE() FROM DELETED;

PostgreSQL 则用 OLD.*：

INSERT INTO orders_archive SELECT OLD.*, 'delete', CURRENT_TIMESTAMP;

• 别在触发器里写循环遍历 DELETED——性能差且没必要
• 如果归档逻辑复杂（比如要关联查用户姓名），确保 JOIN 条件能处理多行，别漏数据
• 测试务必用 DELETE TOP(100) FROM orders WHERE ... 模拟批量删，不能只测单行

事务一致性：触发器失败会导致原 DELETE 回滚

这是关键隐含规则：触发器和原 DELETE 在同一事务里。归档表磁盘满、字段超长、唯一约束冲突，都会让整个 DELETE 失败并回滚——原记录删不掉，用户看到报错。

所以归档表设计要足够宽松：日期字段用 DATETIME2 而非 SMALLDATETIME，字符串字段加 20% 长度余量，禁用不必要的唯一索引。

• 上线前用 DELETE 触发一次，立刻查 SELECT * FROM orders_archive ORDER BY archived_at DESC LIMIT 5 验证数据落地
• 监控 sys.dm_exec_trigger_stats（SQL Server）或 pg_stat_all_tables（PostgreSQL）看触发器执行失败次数
• 别指望靠触发器实现“尽力归档”——它要么全成功，要么全失败，没有中间态

归档逻辑看着简单，但跨数据库时 DELETED 和 OLD 的行为差异、多行处理的惯性思维、事务回滚的连锁影响，这几个点最容易在上线后半夜炸出来。