事务执行出错时如何回滚？现有SQL事务代码能否实现错误自动回滚？

结论 #

你现有的代码可以实现任意语句执行失败时整个事务全量回滚的预期效果。

原理说明 #

• 你已经显式开启了SET XACT_ABORT ON，在SQL Server中该配置的作用为：任意T-SQL语句触发运行时错误时，会直接终止当前事务的执行流程，同时回滚事务内的所有已执行操作。
• 无论是INSERT语句本身执行失败（如主键/唯一约束冲突、字段长度溢出、目标表无写入权限），还是INSERT内嵌的SELECT子查询出错（如源表不存在、查询字段名不匹配、数据类型转换失败、源表无读取权限），都会被判定为运行时错误，触发全事务回滚。

补充说明 #

如果XACT_ABORT处于默认的OFF状态，仅会回滚出错的单条语句，事务会继续执行后续的其他语句，无法满足全事务回滚的要求，你当前显式开启ON的写法是符合需求的。
如果需要更可控的错误捕获逻辑，也可以搭配TRY/CATCH块做显式处理，参考写法如下：

SET XACT_ABORT ON

BEGIN TRY
    BEGIN TRAN
        INSERT INTO [FTR].[dbo].[Usr](fname, lname, phonea, addr)
        SELECT p_code, y_code, y_name, r_coden
        FROM [ADS].[Prep].[InfoEm]
        -- 其他多条INSERT语句
    COMMIT TRAN
END TRY
BEGIN CATCH
    -- 事务未提交时主动回滚
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN
    -- 可在此处打印/返回错误信息
    SELECT 
        ERROR_NUMBER() AS 错误码,
        ERROR_MESSAGE() AS 错误详情
END CATCH

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