Getting Only Modified Fields for an SQL Server Update Trigger

Introduction

In this scenario, we aim to retrieve an XML containing only the modified column values when executing an update trigger on an SQL Server database. This information is crucial for data replication purposes.

Approach

Option 1: Utilizing Dynamic SQL (Declarative Approach)

One method involves using dynamic SQL to build the code that extracts the modified column values. However, this approach can be tedious if the table has a large number of columns.

Option 2: Unpivoting and Joining (Non-Declarative Approach)

A more efficient solution is to unpivot both the inserted and deleted tables, creating a table with fields for the unique key (ContactID), field name (FieldName), and field value (FieldValue). By joining these two tables, it is possible to identify any rows where FieldValue has changed, giving us the modified values.

Example

Consider the following code:

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

This non-declarative approach handles changes, deletions, and insertions effectively without using complex dynamic SQL or facing bitfield arithmetic overflow issues. It is also not affected by changes in the natural primary key as long as an additional GUID column is used for identification.