How to Efficiently Capture Modified Fields in SQL Server UPDATE Triggers Using XML or Alternative Methods?

Efficient Update Trigger for Capturing Modified Fields in SQL Server

Problem:

The COLUMNS_UPDATED method is known, but a quicker technique is needed to capture modified field values in an XML format for replication purposes.

Automated Routine Query:

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


    -- HELP NEEDED FOR FOLLOWING LINE...
    -- I can manually write every column, but need an automated way that works with any column specifications
    for each column, if its modified append $sql = ',' + columnname...

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

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


    .. use @x

END

Alternative Solution:

Instead of using COLUMNS_UPDATED, this response presents an alternative method that addresses the problem. It involves unpivoting both the inserted and deleted tables to create key, field value, and field name columns. By joining and filtering these two unpivoted tables, only the modified, deleted, and inserted rows are identified.

Example:

-- Setup tables and data
CREATE TABLE dbo.Sample_Table (ContactID int, Forename varchar(100), Surname varchar(100), Extn varchar(16), Email varchar(100), Age int );
INSERT INTO Sample_Table VALUES (1,'Bob','Smith','2295','[email protected]',24);
...

-- Create trigger
CREATE TRIGGER TriggerName ON dbo.Sample_Table FOR DELETE, INSERT, UPDATE AS
BEGIN
    -- Unpivot deleted and inserted tables
    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
    ),
    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 tables and capture 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 -- 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

-- Execute changes
UPDATE Sample_Table SET age = age+1;
...

-- Output results
SELECT *, SQL_VARIANT_PROPERTY(FieldValueWas, 'BaseType') FieldBaseType, SQL_VARIANT_PROPERTY(FieldValueWas, 'MaxLength') FieldMaxLength from Sample_Table_Changes;

Benefits of the Alternative Solution:

• Avoids issues with bigint bitfields and arithmetic overflow
• Customizable output format and data type casting
• Handles scenarios with changing natural primary keys by using a GUID-populated column as a unique identifier

The above is the detailed content of How to Efficiently Capture Modified Fields in SQL Server UPDATE Triggers Using XML or Alternative Methods?. For more information, please follow other related articles on the PHP Chinese website!