Comment récupérer efficacement uniquement les champs modifiés à partir d'un déclencheur de mise à jour SQL Server ?

Obtenir uniquement les 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 la colonne modifiée valeurs 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 : Utiliser Dynamic SQL (approche déclarative)

Une méthode consiste à utiliser SQL dynamique pour créer le code qui extrait les valeurs de colonne modifiées. Cependant, cette approche peut s'avérer fastidieuse si la table comporte un grand nombre de colonnes.

Option 2 : Annulation du pivotement et jointure (approche non déclarative)

Une solution plus efficace consiste à annuler le pivotement des colonnes. tables insérées et supprimées, créant une table avec des champs pour la clé unique (ContactID), le nom du champ (FieldName) et la valeur du champ (FieldValue). En joignant ces deux tables, il est possible d'identifier toutes les lignes où FieldValue a changé, nous donnant les valeurs modifiées.

Exemple

Considérons 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 modifications, les suppressions et les insertions sans utiliser de SQL dynamique complexe ni rencontrer de problèmes de débordement arithmétique de champ de bits. Il n'est pas non plus affecté par les modifications de la clé primaire naturelle tant qu'une colonne GUID supplémentaire est utilisée pour l'identification.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!