如何從 SQL Server 更新觸發器中高效率地僅檢索已修改的欄位？

僅取得SQL Server 更新觸發器的已修改欄位

簡介

在此場景中，我們的目標是擷取僅包含已修改列的XML在SQL Server 資料庫上執行更新觸發器時的值。此資訊對於資料複製目的至關重要。

方法

選項 1：利用動態 SQL（聲明式方法）

一種方法涉及使用動態 SQL 建立提取的程式碼修改後的列值。然而，如果表有大量列，這種方法可能會很乏味。

選項2：逆透視與連接（非聲明式方法）

更有效的解決方案是逆透視插入和刪除的表，建立一個包含唯一鍵(ContactID)、欄位名稱(FieldName) 和欄位值(FieldValue) 欄位的表。透過連接這兩個表，可以識別 FieldValue 發生更改的任何行，從而為我們提供修改後的值。

範例

考慮以下程式碼：

CREATE TRIGGER TriggerName ON dbo.Sample_Table FOR DELETE, INSERT, UPDATE AS
BEGIN
    SET NOCOUNT ON;

    -- Unpivot deleted
    WITH deleted_unpvt AS (
        SELECT ContactID, FieldName, FieldValue
        FROM (SELECT ContactID, Forename, Surname, Extn, Email, Age FROM deleted) p
        UNPIVOT (FieldValue FOR FieldName IN (Forename, Surname, Extn, Email, Age)) AS deleted_unpvt
    ),
    -- Unpivot inserted
    inserted_unpvt AS (
        SELECT ContactID, FieldName, FieldValue
        FROM (SELECT ContactID, Forename, Surname, Extn, Email, Age FROM inserted) p
        UNPIVOT (FieldValue FOR FieldName IN (Forename, Surname, Extn, Email, Age)) AS inserted_unpvt
    )

    -- Join them and identify changes
    INSERT INTO Sample_Table_Changes (ContactID, FieldName, FieldValueWas, FieldValueIs)
    SELECT Coalesce(D.ContactID, I.ContactID) ContactID,
           Coalesce(D.FieldName, I.FieldName) FieldName,
           D.FieldValue AS FieldValueWas,
           I.FieldValue AS FieldValueIs
    FROM deleted_unpvt D
    FULL OUTER JOIN inserted_unpvt I
        ON D.ContactID = I.ContactID AND D.FieldName = I.FieldName
    WHERE D.FieldValue <> I.FieldValue
    OR (D.FieldValue IS NOT NULL AND I.FieldValue IS NULL)
    OR (D.FieldValue IS NULL AND I.FieldValue IS NOT NULL);
    
    -- Use the contents of Sample_Table_Changes for replication
END
GO

這種非聲明性方法可以有效地處理變更、刪除和插入，而無需使用複雜的動態SQL 或面臨位域算術溢位問題。只要使用額外的 GUID 列進行標識，也不會受到自然主鍵變化的影響。

以上是如何從 SQL Server 更新觸發器中高效率地僅檢索已修改的欄位？的詳細內容。更多資訊請關注PHP中文網其他相關文章！