Obtenir uniquement des champs modifiés pour un déclencheur de mise à jour SQL Server

introduction

Dans ce scénario, nous visons à récupérer un XML contenant uniquement les valeurs de colonne modifiées lors de l'exécution d'un déclencheur de mise à jour sur une base de données SQL Server. Ces informations sont cruciales à des fins de réplication des données.

approche

Option 1: Utilisation de Dynamic SQL (approche déclarative)

Une méthode implique l'utilisation de SQL dynamique pour créer le code qui extrait les valeurs de colonne modifiées. Cependant, cette approche peut être fastidieuse si la table a un grand nombre de colonnes.

Option 2: Univoting and Rewing (approche non dé-déclarative)

Une solution plus efficace est de diffuser à la fois les tableaux de champs insurés et supprimés, la création de champs avec la clé unique (contactid), le nom de champ (champ de champ). En rejoignant ces deux tables, il est possible d'identifier toutes les lignes où FieldValue a changé, nous donnant les valeurs modifiées.

Exemple

Considérez le code suivant:

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

Cette approche non déclarative gère efficacement les changements, les suppressions et les insertions sans utiliser des problèmes de débordement arithmétiques dynamiques complexes ou face à Bitfield. Il n'est pas non plus affecté par les modifications de la clé primaire naturelle tant qu'une colonne de GUID supplémentaire est utilisée pour l'identification.