Comment générer efficacement du XML de colonnes de table mises à jour dans SQL Server ?

Génération XML des colonnes de table mises à jour dans SQL Server

La fonction COLUMNS_UPDATED de SQL Server fournit un aperçu des colonnes modifiées lors des mises à jour de table. Cependant, pour les scénarios nécessitant uniquement les valeurs de colonne mises à jour au format XML, nous présentons une approche distincte.

Routine automatisée pour identifier les colonnes modifiées

Plutôt que de spécifier manuellement chaque colonne colonne dans l'instruction SELECT, une routine automatisée peut générer dynamiquement la liste de colonnes en fonction de la structure de la table. Cela simplifie le développement du déclencheur et garantit son adaptation aux futures modifications de la table. Par exemple, dans un déclencheur pour la table DBCustomers :

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

    -- Get the updated columns.
    SELECT TOP (1) COLUMN_NAME
    INTO #ModifiedColumns
    FROM INFORMATION_SCHEMA.COLUMNS_UPDATED()
    ORDER BY COLUMN_NAME;

    WITH RECURSIVE ModifiedColumnsCTE AS (
        SELECT *, ROW_NUMBER() OVER (ORDER BY COLUMN_NAME) AS RowNum, COUNT(*) OVER () AS TotalRows
        FROM #ModifiedColumns
    )
    SELECT COLUMN_NAME INTO #temp
    FROM ModifiedColumnsCTE
    WHERE RowNum < TotalRows
    UNION ALL
    SELECT ',' + COLUMN_NAME
    FROM ModifiedColumnsCTE
    WHERE RowNum = TotalRows;

    -- Append the updated columns to the SELECT statement.
    SET @sql = @sql + STUFF((
        SELECT TOP (1) ColumnName
        FROM #temp
        FOR XML PATH('')
    ), 1, 2, '');

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

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

    -- Use the @x XML variable for replication purposes.
END

Solution alternative utilisant un pivotement

Une approche alternative exploite des techniques sans pivotement, évitant complètement COLUMNS_UPDATED :

• Annulez le pivotement des tables insérées et supprimées, ce qui donne trois colonnes : clé unique, colonne modifiée valeur et nom de la colonne.
• Rejoignez les tableaux non pivotés et filtrez les modifications, suppressions et insertions de valeurs de colonne.
• Stockez les résultats dans un tableau séparé à des fins d'audit.

Cette méthode élimine le besoin de SQL dynamique et garantit un formatage de sortie cohérent, quelle que soit la structure de la table. Il est particulièrement bénéfique pour les tables comportant un grand nombre de colonnes ou des ajouts de colonnes réguliers.

Considérations

Lorsque vous traitez des tables ayant des clés primaires naturelles qui peuvent changer, en utilisant un une colonne supplémentaire remplie avec un GUID via la fonction NEWID() est recommandée. Cela garantit que le processus non pivotant reste valide à des fins de piste d'audit.

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!