SQL触发器实现删除记录自动转入历史归档表

先说几个要点。DELETE归档这件事，触发器选型是第一个容易翻车的坑。不少人想当然地用了INSTEAD OF DELETE，觉得安全，结果原表记录压根没被删——触发器替代了删除操作，归档表自然也拿不到数据。正确做法是必须用AFTER DELETE，确保原表的物理删除已经发生，再通过DELETED临时表（SQL Server）或OLD别名（MySQL/PostgreSQL）稳定捕获被删行。

归档表设计：少一个字段就是事故

归档表不是简单复制原表结构，少一个字段或类型不匹配，触发器一执行就报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是结果集，不是单行变量

很多人习惯写SELECT @id = id FROM DELETED，单行删除时侥幸成功，多行删除就完蛋：SQL Server报Subquery returned more than 1 value，MySQL直接语法报错。正确做法是把DELETED当普通表处理，用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的行为差异、多行处理的惯性思维、事务回滚的连锁影响，这几个点最容易在上线后半夜炸出来。