SQL Server 2012动态T-SQL触发器无法访问INSERT/DELETE表数据问题

一、动态SQL访问INSERTED/DELETED表的解决办法 #

触发器内的INSERTED和DELETED是专属上下文临时表，动态SQL的独立执行环境无法直接访问它们。核心解决思路是先将这两个表的数据转存到临时表或表变量中，让动态SQL能通过这个中间容器获取变更数据。

示例实现步骤 #

1. 创建表变量（或临时表），存储需要对比的主键及列数据，同时标记操作类型（插入/删除）
2. 将INSERTED和DELETED的数据同步到中间容器
3. 在动态SQL中引用该容器，对比新旧值提取变更字段

CREATE TRIGGER trg_Employee_ArchiveChanges
ON Employee
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 定义存储变更数据的表变量（包含主键和待检查列）
    DECLARE @ChangeData TABLE (
        EmpID INT PRIMARY KEY,
        IsInsert BIT,
        IsDelete BIT,
        FirstName NVARCHAR(50),
        Surname NVARCHAR(50)
    );

    -- 2. 同步INSERTED数据（标记为插入操作）
    INSERT INTO @ChangeData (EmpID, IsInsert, IsDelete, FirstName, Surname)
    SELECT EmpID, 1, 0, FirstName, Surname FROM INSERTED;

    -- 3. 同步DELETED数据（处理更新/删除场景）
    UPDATE cd
    SET cd.IsDelete = 1,
        cd.FirstName = d.FirstName,
        cd.Surname = d.Surname
    FROM @ChangeData cd
    JOIN DELETED d ON cd.EmpID = d.EmpID;

    -- 处理纯删除场景（无INSERTED数据的情况）
    INSERT INTO @ChangeData (EmpID, IsInsert, IsDelete, FirstName, Surname)
    SELECT EmpID, 0, 1, FirstName, Surname
    FROM DELETED d
    WHERE NOT EXISTS (SELECT 1 FROM @ChangeData cd WHERE cd.EmpID = d.EmpID);

    -- 4. 动态SQL中引用表变量@ChangeData提取变更
    DECLARE @ColName NVARCHAR(128), @SQL NVARCHAR(MAX);
    DECLARE col_cursor CURSOR FOR
        SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('Employee') AND name <> 'EmpID';

    OPEN col_cursor;
    FETCH NEXT FROM col_cursor INTO @ColName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = N'
            SELECT 
                EmpID,
                ''' + @ColName + ''' AS ChangedColumn,
                CASE WHEN IsInsert = 1 THEN '''' ELSE CAST(OldVal AS NVARCHAR(MAX)) END AS OldValue,
                CASE WHEN IsDelete = 1 THEN '''' ELSE CAST(NewVal AS NVARCHAR(MAX)) END AS NewValue
            FROM (
                SELECT 
                    cd.EmpID,
                    cd.IsInsert,
                    cd.IsDelete,
                    (SELECT ' + @ColName + ' FROM @ChangeData WHERE EmpID = cd.EmpID AND IsDelete = 1) AS OldVal,
                    (SELECT ' + @ColName + ' FROM @ChangeData WHERE EmpID = cd.EmpID AND IsInsert = 1) AS NewVal
                FROM @ChangeData cd
                WHERE IsInsert <> IsDelete OR (IsInsert = 1 AND IsDelete = 1 AND OldVal <> NewVal)
            ) t
            WHERE OldVal <> NewVal OR IsInsert = 1 OR IsDelete = 1;
        ';

        -- 执行动态SQL，可将结果存入临时表用于后续JSON拼接
        EXEC sp_executesql @SQL, N'@ChangeData TABLE (EmpID INT PRIMARY KEY, IsInsert BIT, IsDelete BIT, FirstName NVARCHAR(50), Surname NVARCHAR(50))', @ChangeData = @ChangeData;

        FETCH NEXT FROM col_cursor INTO @ColName;
    END

    CLOSE col_cursor;
    DEALLOCATE col_cursor;

    -- 后续逻辑：将变更字段拼接成{firstname:john,surname:doe}格式，存入归档表
END

提示：如果表结构不固定，可动态生成表变量/临时表的列定义，或使用全局临时表（##前缀），避免传递表结构参数的麻烦。

二、触发器代码复用的方案 #

要避免每个表重复编写触发器逻辑，核心是把通用逻辑封装成存储过程，每个表的触发器仅做极简的准备工作：

1. 创建临时表存储当前表的变更数据
2. 调用通用存储过程，传入临时表、表名、Schema等参数
3. 存储过程内部处理扩展属性检查、变更提取、归档存储等逻辑

具体实现 #

1. 创建通用归档存储过程

CREATE PROCEDURE sp_ArchiveTableChanges
    @TableName NVARCHAR(128),
    @SchemaName NVARCHAR(128) = 'dbo',
    @TempTable NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @FullTableName NVARCHAR(256) = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);
    DECLARE @ColName NVARCHAR(128), @SQL NVARCHAR(MAX);
    DECLARE @ChangeJSON NVARCHAR(MAX) = '';

    -- 1. 筛选需要归档的列（通过扩展属性判断）
    DECLARE col_cursor CURSOR FOR
        SELECT c.name
        FROM sys.columns c
        JOIN sys.tables t ON c.object_id = t.object_id
        JOIN sys.schemas s ON t.schema_id = s.schema_id
        WHERE s.name = @SchemaName AND t.name = @TableName
        AND EXISTS (
            SELECT 1 FROM sys.extended_properties ep
            WHERE ep.major_id = c.object_id AND ep.minor_id = c.column_id
            AND ep.name = 'IsArchive' AND ep.value = 1
        );

    OPEN col_cursor;
    FETCH NEXT FROM col_cursor INTO @ColName;

    -- 2. 动态拼接变更字段的JSON字符串
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = N'
            SELECT @ChangeJSON += CASE WHEN @ChangeJSON = '''' THEN '''' ELSE '','' END + 
                ''' + QUOTENAME(@ColName, '"') + ':' + ''' + 
                CASE WHEN IsInsert = 1 THEN CAST(NewVal AS NVARCHAR(MAX)) ELSE CAST(OldVal AS NVARCHAR(MAX)) END + ''''
            FROM (
                SELECT 
                    cd.EmpID,
                    cd.IsInsert,
                    cd.IsDelete,
                    (SELECT ' + QUOTENAME(@ColName) + ' FROM ' + @TempTable + ' WHERE EmpID = cd.EmpID AND IsDelete = 1) AS OldVal,
                    (SELECT ' + QUOTENAME(@ColName) + ' FROM ' + @TempTable + ' WHERE EmpID = cd.EmpID AND IsInsert = 1) AS NewVal
                FROM ' + @TempTable + ' cd
                WHERE (IsInsert <> IsDelete OR OldVal <> NewVal)
            ) t;
        ';

        EXEC sp_executesql @SQL, N'@ChangeJSON NVARCHAR(MAX) OUTPUT', @ChangeJSON = @ChangeJSON OUTPUT;

        FETCH NEXT FROM col_cursor INTO @ColName;
    END

    CLOSE col_cursor;
    DEALLOCATE col_cursor;

    -- 3. 将JSON存入归档表
    INSERT INTO ChangeArchive (TableName, ChangeTime, ChangeData)
    VALUES (@FullTableName, GETDATE(), '{' + @ChangeJSON + '}');
END

2. 单表触发器调用存储过程

CREATE TRIGGER trg_Employee_Archive
ON dbo.Employee
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- 创建全局临时表存储变更数据
    CREATE TABLE ##EmpChangeData (
        EmpID INT PRIMARY KEY,
        IsInsert BIT,
        IsDelete BIT,
        FirstName NVARCHAR(50),
        Surname NVARCHAR(50)
    );

    -- 同步INSERTED数据
    INSERT INTO ##EmpChangeData (EmpID, IsInsert, IsDelete, FirstName, Surname)
    SELECT EmpID, 1, 0, FirstName, Surname FROM INSERTED;

    -- 同步DELETED数据
    UPDATE cd
    SET cd.IsDelete = 1,
        cd.FirstName = d.FirstName,
        cd.Surname = d.Surname
    FROM ##EmpChangeData cd
    JOIN DELETED d ON cd.EmpID = d.EmpID;

    -- 处理纯删除场景
    INSERT INTO ##EmpChangeData (EmpID, IsInsert, IsDelete, FirstName, Surname)
    SELECT EmpID, 0, 1, FirstName, Surname
    FROM DELETED d
    WHERE NOT EXISTS (SELECT 1 FROM ##EmpChangeData cd WHERE cd.EmpID = d.EmpID);

    -- 调用通用存储过程
    EXEC sp_ArchiveTableChanges 
        @TableName = 'Employee',
        @SchemaName = 'dbo',
        @TempTable = '##EmpChangeData';

    -- 清理临时表
    DROP TABLE ##EmpChangeData;
END

进阶优化 #

如果需要完全通用的触发器（无需手动写列定义），可以在触发器内动态生成临时表结构：

DECLARE @SQL NVARCHAR(MAX) = N'
    SELECT *, 1 AS IsInsert, 0 AS IsDelete INTO ##TempChangeData FROM INSERTED;
    UPDATE ##TempChangeData SET IsDelete = 1 WHERE EXISTS (SELECT 1 FROM DELETED d WHERE d.EmpID = ##TempChangeData.EmpID);
    INSERT INTO ##TempChangeData SELECT *, 0, 1 FROM DELETED d WHERE NOT EXISTS (SELECT 1 FROM ##TempChangeData cd WHERE cd.EmpID = d.EmpID);
';
EXEC sp_executesql @SQL;

这种方式下，触发器代码几乎可以复制到任意表使用，仅需调整主键字段的关联逻辑。

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