obteniendo solo campos modificados para un disparo de actualización de servidor SQL

introducción

en este escenario, nuestro objetivo es recuperar un XML que contiene solo los valores de columna modificados al ejecutar un desencadenante de actualización en una base de ser del servidor SQL. Esta información es crucial para los propósitos de replicación de datos.

enfoque

Opción 1: Utilizar SQL dinámico (enfoque declarativo)

Un método implica usar SQL dinámico para construir el código que extrae los valores de columna modificados. Sin embargo, este enfoque puede ser tedioso si la tabla tiene una gran cantidad de columnas.

Opción 2: Invivotar y unir (Enfoque no declarativo)

Una solución más eficiente es para no ir a las tablas insertadas y eliminadas, creando una tabla con campos para la clave única (ContactId), nombre de campo (nombre de campo), y de campo (Valor de campo). Al unir estas dos tablas, es posible identificar cualquier fila donde FieldValue haya cambiado, dándonos los valores modificados.

Ejemplo

Considere el siguiente código:

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

Este enfoque no declarativo maneja los cambios, las deleciones y las inserciones de manera efectiva sin usar SQL dinámico complejo o los problemas de desbordamiento aritmético de Bitfield. Tampoco se ve afectado por los cambios en la clave primaria natural siempre que se use una columna GUID adicional para la identificación.