SQL Server 2016：事务报错时用OUTPUT子句展示变更记录

针对事务涉及50张表、不想创建大量临时表的场景，可通过统一表变量收集变更记录+TRY/CATCH错误捕获的方案实现需求，无需为每张表单独定义存储结构，同时能关联表信息与错误详情。

核心思路 #

1. 用一个通用表变量接收所有DML操作的变更数据，通过OUTPUT INTO子句写入，同时附加表名、操作类型标识
2. 用TRY/CATCH块替代@@error判断，更可靠捕获事务错误（如主键冲突）
3. 在CATCH块中同时输出错误信息与收集到的变更记录，快速定位问题

具体实现代码 #

1. 定义通用变更日志表变量 #

DECLARE @ChangeLogs TABLE (
    TableName NVARCHAR(128),    -- 操作的表名
    OperationType NVARCHAR(10), -- 操作类型：INSERT/UPDATE/DELETE
    InsertedData XML,           -- 变更后的数据（XML格式适配任意表结构）
    DeletedData XML             -- 变更前的数据（DELETE/UPDATE场景有效）
)

2. 事务与错误处理完整示例 #

USE [MergeAccounts]
GO

DECLARE @ChangeLogs TABLE (
    TableName NVARCHAR(128),
    OperationType NVARCHAR(10),
    InsertedData XML,
    DeletedData XML
)

BEGIN TRY
    BEGIN TRANSACTION

    -- 示例1：更新table1（模拟主键冲突场景）
    UPDATE [dbo].[table1]
    SET [IDNUM] = 11111
    OUTPUT 
        'table1' AS TableName,
        'UPDATE' AS OperationType,
        CONVERT(XML, inserted) AS InsertedData,
        CONVERT(XML, deleted) AS DeletedData
    INTO @ChangeLogs
    WHERE IDNUM = 11113

    -- 示例2：插入table2（可扩展到其余48张表的DML操作）
    INSERT INTO [dbo].[table2] (IDNUM, c1, c2, c3, c4, c5)
    VALUES (11114, 'test', 1, 2, 'testval', 10.00)
    OUTPUT 
        'table2' AS TableName,
        'INSERT' AS OperationType,
        CONVERT(XML, inserted) AS InsertedData,
        NULL AS DeletedData
    INTO @ChangeLogs

    COMMIT TRANSACTION
    PRINT '事务执行成功'
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION

    -- 输出错误详情
    SELECT 
        '错误编号' AS [项], CAST(ERROR_NUMBER() AS VARCHAR) AS [值]
    UNION ALL
    SELECT 
        '错误描述' AS [项], ERROR_MESSAGE() AS [值]
    UNION ALL
    SELECT 
        '严重级别' AS [项], CAST(ERROR_SEVERITY() AS VARCHAR) AS [值]

    -- 输出本次事务的所有变更记录
    PRINT '事务涉及的变更记录：'
    SELECT 
        TableName,
        OperationType,
        InsertedData,
        DeletedData
    FROM @ChangeLogs
END CATCH

关键说明 #

• XML存储适配多表结构：用XML类型存储inserted/deleted数据，无需关心每张表的字段差异，完美适配50张表的场景
• 表变量不受事务回滚影响：OUTPUT INTO写入表变量的操作在DML执行时完成，即使事务后续回滚，表变量中的变更记录依然保留，可用于错误排查
• 解决触发器冲突问题：使用OUTPUT INTO子句可避免无INTO的OUTPUT与触发器冲突的报错，符合SQL Server的语法要求
• 快速定位重复键值：变更记录中包含修改前后的键值数据，结合错误信息可直接对比冲突的主键/唯一键内容

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