如何在 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_UPDATEDTED ：

• 逆透視插入和刪除的表，產生三個columns：唯一鍵、修改的列值和列名稱。
• 加入未透視表並過濾列值變更、刪除和插入。
• 將結果儲存在單獨的表中以供審核

此方法消除了對動態 SQL 的需要，並確保一致的輸出格式，無論表結構如何。它對於具有大量列或定期添加列的表特別有益。

注意事項

在處理具有可以更改的自然主鍵的表時，使用建議透過 NEWID() 函數填充 GUID 的附加列。這確保了逆透視過程對於審計追蹤目的仍然有效。

以上是如何在 SQL Server 中高效率產生更新表列的 XML？的詳細內容。更多資訊請關注PHP中文網其他相關文章！