怎样在SQL Server中通过触发器实现软删除自动转归档？-SQL-PHP中文网

instead of delete 是软删除+归档的正确起点，它能真正接管删除操作，将“删”转为“标为已删+写入归档”，依赖 deleted 表一次性完成，避免幻读、死锁及跨表一致性风险。

怎样在sql server中通过触发器实现软删除自动转归档？

SQL Server 的 INSTEAD OF DELETE 才是软删除+归档的正确起点

不能靠 AFTER DELETE 或 BEFORE DELETE 实现可靠软删归档——前者物理删除已发生，后者无法阻止删除。只有 INSTEAD OF DELETE 能真正接管语句，把“删”变成“标为已删 + 写入归档”。它不依赖权限禁用或应用层补救，是数据库原生支持的拦截机制。

关键前提是：触发器必须显式处理 deleted 表（只读临时表，结构同原表，含所有被删行），且所有归档动作必须基于它，不能回查原表。

deleted 表只在当前触发器作用域内有效，生命周期随触发器结束而销毁
归档 INSERT 必须与主表在**同一数据库**；跨库写入需用 dblink（PostgreSQL）或链接服务器（SQL Server），但会引入连接稳定性与事务一致性风险
必须加 SET NOCOUNT ON，否则 Entity Framework 等 ORM 可能因多结果集报错中断

归档逻辑必须放在 UPDATE 之后、且仅依赖 deleted 表

常见错误是先 UPDATE 标记字段，再 SELECT 原表数据归档——这会导致读到更新后的状态（如 is_deleted = 1 已生效），甚至引发死锁或幻读。正确做法是：所有归档字段直接从 deleted 表取值，一次完成。

示例（以 Products 表为例）：

CREATE TRIGGER trg_InsteadOfDelete_Products
ON Products
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
<pre class="brush:php;toolbar:false;">-- 步骤1：标记为软删除
UPDATE p 
SET IsDeleted = 1, DeletedAt = GETDATE()
FROM Products p
INNER JOIN deleted d ON p.Id = d.Id;

-- 步骤2：归档（全部字段来自 deleted，不查原表）
INSERT INTO Products_Archive (Id, Name, Price, CreatedAt, DeletedAt, ArchivedAt, Operator)
SELECT Id, Name, Price, CreatedAt, GETDATE(), GETDATE(), SUSER_SNAME()
FROM deleted;

END;