Comment capturer efficacement les champs modifiés dans les déclencheurs de mise à jour de SQL Server à l'aide de XML ou de méthodes alternatives ?

Déclencheur de mise à jour efficace pour capturer les champs modifiés dans SQL Server

Problème :

La méthode COLUMNS_UPDATED est connue, mais un une technique plus rapide est nécessaire pour capturer les valeurs de champ modifiées dans un format XML pour la réplication à des fins.

Requête de routine automatisée :

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

Solution alternative :

Au lieu d'utiliser COLUMNS_UPDATED, cette réponse présente une méthode alternative qui résout le problème. Cela implique d'annuler le pivotement des tables insérées et supprimées pour créer des colonnes de clé, de valeur de champ et de nom de champ. En joignant et en filtrant ces deux tableaux non croisés, seules les lignes modifiées, supprimées et insérées sont identifiées.

Exemple :

-- 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;

Avantages du Solution alternative :

• Évite les problèmes avec les champs de bits bigint et débordement arithmétique
• Format de sortie personnalisable et conversion du type de données
• Gère les scénarios de modification des clés primaires naturelles en utilisant une colonne remplie par un GUID comme identifiant unique

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!