SQL Server 2017：为用户表替代删除触发器添加事务

首先先明确你的现有表结构：

CREATE TABLE dbo.[user] ( 
    user_id INT NOT NULL IDENTITY PRIMARY KEY, 
    user_deleter INT REFERENCES [user] (user_id), 
    user_deleted DATETIME2 
);
CREATE TABLE dbo.[project] ( 
    project_id INT NOT NULL IDENTITY PRIMARY KEY, 
    project_owner INT NOT NULL REFERENCES [user] (user_id) 
);

你提到原有触发器用WHILE循环+DELETED临时表处理单/多条用户删除，但怕批量操作时出现部分处理的情况——这个担心很合理，毕竟如果中间某一步出错，没有事务控制的话，前面的操作已经生效，后面的就停了，就会出现“删了2个，剩下3个没处理”的半吊子状态。

其实SQL Server的触发器本身就运行在触发它的语句的事务上下文里，但为了确保批量操作的原子性（要么全成，要么全败），我们需要用TRY/CATCH块来包裹所有逻辑，捕获错误后回滚整个事务。下面是正确的实现方式：

完整的触发器代码 #

CREATE OR ALTER TRIGGER dbo.trg_user_InsteadOfDelete
ON dbo.[user]
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- 声明循环需要的变量
    DECLARE @TargetUserId INT, @DeleterUserId INT;

    BEGIN TRY
        -- 注意：这里不需要手动BEGIN TRANSACTION！
        -- 触发器默认继承外部DELETE语句的事务上下文，手动开事务反而容易出嵌套问题

        -- 循环处理DELETED表中的每条待删除记录
        WHILE EXISTS(SELECT 1 FROM DELETED)
        BEGIN
            -- 取出一条未处理的用户ID，加锁提示避免死锁和重复处理
            SELECT TOP 1 
                @TargetUserId = user_id, 
                @DeleterUserId = user_deleter 
            FROM DELETED WITH (ROWLOCK, UPDLOCK, READPAST);

            -- 执行你的软删除逻辑：标记用户为已删除
            UPDATE dbo.[user]
            SET 
                user_deleted = GETUTCDATE(),
                -- 这里可以根据实际业务设置删除者，比如当前操作的用户
                user_deleter = COALESCE(@DeleterUserId, SUSER_SID())
            WHERE user_id = @TargetUserId;

            -- 【可选】如果需要处理关联的project表，把操作放在这里
            -- 比如设置项目的owner为NULL（如果外键允许），或者删除关联项目
            -- UPDATE dbo.[project] SET project_owner = NULL WHERE project_owner = @TargetUserId;
            -- DELETE FROM dbo.[project] WHERE project_owner = @TargetUserId;

            -- 从DELETED表移除已处理的记录，避免重复循环
            DELETE FROM DELETED WHERE user_id = @TargetUserId;
        END
    END TRY
    BEGIN CATCH
        -- 捕获到错误时，回滚整个事务（包括外部DELETE语句的上下文）
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- 抛出错误信息，让调用方知道操作失败
        THROW;
    END CATCH
END;

关键要点解释 #

• SET NOCOUNT ON：关闭影响行数的返回消息，避免干扰应用程序的逻辑处理。
• TRY/CATCH块：这是确保原子性的核心——所有业务逻辑都放在TRY块里，只要任何一步出错，就会跳到CATCH块，回滚所有已执行的操作，保证批量删除要么全部完成，要么全部回滚。
• 不要手动开启事务：触发器默认运行在触发它的DELETE语句的事务中（比如你执行DELETE FROM [user] WHERE ...，这个语句本身就在一个隐式事务里），手动开启新事务会导致嵌套事务，反而容易引发异常。
• 循环锁提示：WITH (ROWLOCK, UPDLOCK, READPAST) 是为了在批量处理时避免死锁，确保每次只处理一条未被锁定的记录，提升并发场景下的稳定性。
• THROW语句：在CATCH块中抛出错误，让外部调用者（比如你的应用程序）能获取到错误详情，知道删除操作失败了。

测试建议 #

你可以故意制造一个错误场景来验证原子性：比如一次性删除5个用户，其中一个用户关联的project表有外键约束无法修改/删除，这时候整个批量操作应该会回滚，所有用户都不会被标记为已删除。

内容的提问来源于stack exchange，提问作者DNA