How to Efficiently Retrieve Only Updated Fields in SQL Server Replication?

Getting Updated Fields for Replication in SQL Server

Introduction

When replicating data between SQL Server databases, it's necessary to identify the fields that have been modified. The default mechanism, COLUMNS_UPDATED, provides a bitwise representation of updated fields. However, this article presents an alternative approach to retrieve an XML snippet that contains only the updated column values.

Custom Update Trigger Function

To achieve this, create a custom update trigger function as follows:

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

    WITH UpdatedColumns AS (
        SELECT c.name AS ColumnName
        FROM sys.columns AS c
        JOIN inserted AS i ON c.object_id = i.object_id
        WHERE c.is_identity = 0
            AND i.Original_value <> i.value
    )
    SELECT @sql = @sql + ',' + ColumnName
    FROM UpdatedColumns

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

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

    .. use @x

END

Alternative Solution without COLUMNS_UPDATED

Instead of COLUMNS_UPDATED, a different approach involves unpivoting the inserted and deleted tables, joining them, and filtering for any changes:

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

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

    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 --Changes
        OR (D.FieldValue IS NOT NULL AND I.FieldValue IS NULL) -- Deletions
        OR (D.FieldValue IS NULL AND I.FieldValue IS NOT NULL) -- Insertions
END

The above is the detailed content of How to Efficiently Retrieve Only Updated Fields in SQL Server Replication?. For more information, please follow other related articles on the PHP Chinese website!