如何在 SQL Server 中高效生成更新表列的 XML？

在 SQL Server 中生成更新表列的 XML

SQL Server 的 COLUMNS_UPDATED 函数提供对表更新期间修改的列的洞察。但是，对于仅需要 XML 格式的更新列值的场景，我们提出了一种独特的方法。

用于识别修改列的自动例程

而不是手动指定每个列SELECT 语句中的列，自动化例程可以根据表结构动态生成列列表。这简化了触发器开发并确保它适应未来的表修改。例如，在 DBCustomers 表的触发器中：

CREATE TRIGGER DBCustomers_Insert
ON DBCustomers
AFTER UPDATE
AS
BEGIN
    DECLARE @sql AS NVARCHAR(1024);
    SET @sql = 'SELECT ';

    -- Get the updated columns.
    SELECT TOP (1) COLUMN_NAME
    INTO #ModifiedColumns
    FROM INFORMATION_SCHEMA.COLUMNS_UPDATED()
    ORDER BY COLUMN_NAME;

    WITH RECURSIVE ModifiedColumnsCTE AS (
        SELECT *, ROW_NUMBER() OVER (ORDER BY COLUMN_NAME) AS RowNum, COUNT(*) OVER () AS TotalRows
        FROM #ModifiedColumns
    )
    SELECT COLUMN_NAME INTO #temp
    FROM ModifiedColumnsCTE
    WHERE RowNum < TotalRows
    UNION ALL
    SELECT ',' + COLUMN_NAME
    FROM ModifiedColumnsCTE
    WHERE RowNum = TotalRows;

    -- Append the updated columns to the SELECT statement.
    SET @sql = @sql + STUFF((
        SELECT TOP (1) ColumnName
        FROM #temp
        FOR XML PATH('')
    ), 1, 2, '');

    SET @sql = @sql + ' FROM inserted FOR XML RAW';

    DECLARE @x AS XML;
    SET @x = CAST(EXEC(@sql) AS XML);

    -- Use the @x XML variable for replication purposes.
END

使用逆透视的替代解决方案

另一种方法利用逆透视技术，完全避免 COLUMNS_UPDATED：

• 逆透视插入和删除的表，产生三个columns：唯一键、修改的列值和列名称。
• 加入未透视表并过滤列值更改、删除和插入。
• 将结果存储在单独的表中以供审核

此方法消除了对动态 SQL 的需要，并确保一致的输出格式，无论表结构如何。它对于具有大量列或定期添加列的表特别有益。

注意事项

在处理具有可以更改的自然主键的表时，使用建议通过 NEWID() 函数填充 GUID 的附加列。这确保了逆透视过程对于审计跟踪目的仍然有效。

以上是如何在 SQL Server 中高效生成更新表列的 XML？的详细内容。更多信息请关注PHP中文网其他相关文章！