SQL存储过程多表更新时单步失败如何回滚全部操作

• 每个UPDATE语句单独嵌套TRY/CATCH块，第一个表更新报错触发回滚后，后续的TRY块仍会继续执行，此时已无活动事务，后续执行ROLLBACK或最终的COMMIT语句时，会触发“无对应事务”的执行错误。
• 捕获到错误执行回滚后，没有终止后续流程的逻辑，哪怕前面已经回滚完成，代码仍会继续执行剩余表的更新操作，无法实现“前一步失败就终止后续操作”的要求。
• 没有对事务状态做判断，容易出现重复回滚、无事务可提交/回滚的异常。
• 未开启XACT_ABORT配置，部分严重级别的运行错误会直接跳出TRY块不进入CATCH逻辑，导致事务长期持有锁不释放，引发阻塞。
• 没有校验更新影响行数，如果UPDATE语句语法正确但没有匹配到待更新的记录，不会触发报错，会出现部分表更新、部分表无操作的不符合预期的情况。

CREATE PROCEDURE dbo.CheckRegEnf 
    @id_Enf int, 
    @id int, 
    @Data datetime, 
    @Hora datetime,    
    @noUtente int , 
    @idAtiv int, 
    @ID_Medicamento int
AS
BEGIN
    SET NOCOUNT ON;
    -- 开启严重错误自动终止批处理配置，避免事务悬挂
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRAN;
        -- 第一步更新RegEnfermeiro表
        UPDATE RegEnfermeiro 
        SET Data = @Data, Hora = @Hora 
        WHERE id = @id and noUtente = @noUtente;
        -- 可选校验：要求必须匹配到记录才算更新成功，无该需求可删除
        IF @@ROWCOUNT = 0
            THROW 50001, 'RegEnfermeiro表无匹配更新记录', 1;

        -- 第二步更新RegEnfAtiv表
        UPDATE RegEnfAtiv 
        SET idAtiv = @idAtiv 
        where idReg = @id;
        -- 同上可选行数校验
        IF @@ROWCOUNT = 0
            THROW 50002, 'RegEnfAtiv表无匹配更新记录', 1;

        -- 第三步更新RegEnfMEd表
        UPDATE RegEnfMEd 
        SET ID_Medicamento = @ID_Medicamento 
        where ID_RegEnf = @id;
        -- 同上可选行数校验
        IF @@ROWCOUNT = 0
            THROW 50003, 'RegEnfMEd表无匹配更新记录', 1;

        -- 所有步骤无异常，统一提交
        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        -- 判断存在活动事务才回滚，避免无事务可回滚的报错
        IF XACT_STATE() <> 0
            ROLLBACK TRAN;
        -- 抛出原始错误信息方便定位问题
        THROW;
    END CATCH
END

• 所有更新操作放在同一个事务、同一个TRY块内，任意一步执行出错，都会直接跳转到唯一的CATCH块执行全量回滚，不会继续执行后续的更新语句，满足“前一步失败则所有已执行操作全部回滚、终止后续流程”的要求。
• 所有表更新全部成功才会走到COMMIT语句，一次性提交所有变更，完全匹配链式失败全回滚的规则。
• CATCH块内先通过XACT_STATE()判断事务状态，仅当存在活动事务时才执行回滚，避免事务不匹配的报错，回滚完成后通过THROW抛出原始错误信息，方便排查问题。
• 开头开启SET XACT_ABORT ON，遇到运行时错误会自动终止批处理、回滚事务，避免严重错误导致的事务悬挂问题。
• 移除了原代码中未使用的@count变量，增加SET NOCOUNT ON减少不必要的网络消息返回开销。

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