Déclencheur d'audit de table SQL Server

Question

J'ai deux tables Customers et AuditTable. Lorsque je modifie la table Customers, j'ai besoin d'insérer un nouveau enregistrement dans la table AuditTable:

CREATE TABLE [dbo].[AuditTable]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [StateBefore] [nvarchar](max) NULL,
    [StateAfter] [nvarchar](max) NULL
)

Il me faut placer une représentation XML de l'état du Customer dans StateBefore et StateAfter, avant et après la mise à jour.

La table Customer est:

CREATE TABLE [dbo].[Customer]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](256) NOT NULL,
    [Email] [nvarchar](max) NOT NULL,
    [IsDeleted] [bit] NULL,
    [CreatedUtc] [datetime] NOT NULL,
    [UpdatedUtc] [datetime] NULL,
    [Version] [timestamp] NOT NULL,

    CONSTRAINT [PK_dbo.Customer] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
           WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,  
                 ALLOW_PAGE_LOCKS = ON)
)
GO

ALTER TABLE [dbo].[Customer] 
   ADD DEFAULT (getutcdate()) FOR [CreatedUtc]
GO

J'ai trouvé comment obtenir une représentation xml des lignes:

SELECT
    [State] = (SELECT *
               FROM dbo.Customer [Customer]
               WHERE [Customer].Id = cust.Id
               FOR XML AUTO
              )
FROM 
    dbo.Customer cust

C'est juste un exemple. Donc dans mon déclencheur, je dois créer la représentation xml des lignes des tables deleted et inserted.

Voici le déclencheur:

ALTER TRIGGER [dbo].[UpdateCustomerTrigger]
ON [dbo].[Customer]
FOR UPDATE
AS 
BEGIN
    UPDATE Customer 
    SET UpdatedUtc = GETDATE()
    FROM INSERTED
    WHERE inserted.id = Customer.Id

    -- ici je dois insérer de nouveaux enregistrements dans AuditTable
END

Donc, comment puis-je joindre deux représentations des tables deleted et inserted pour les insérer correctement dans la table AuditTable? Merci.

Answer 1

Génial que vous ayez déjà trouvé une solution...

Je pensais justement à quelque chose de similaire...

Votre approche nécessiterait deux copies de l'enregistrement complet à chaque petite modification. Comme je dois traiter des tables avec beaucoup de colonnes, dont certaines sont des BLOB, cela ne conviendrait pas pour moi.

Eh bien, je n'ai pas trouvé d'approche absolument "propre", mais avec ce qui suit, vous obtiendrez un AuditLog avec seulement les valeurs qui ont réellement changé dans un style plus facile à lire.

Peut-être que cela vous plaira :

Scénario de test

EDIT : Ajout de la prise en charge des INSERT et DELETE et des valeurs NULL.

Essayez-le :

CREATE TABLE AuditTest(TableSchema VARCHAR(250), TableName VARCHAR(250), AuditType VARCHAR(250),Content XML, LogDate DATETIME DEFAULT GETDATE());
GO

CREATE TABLE dbo.Test(ID INT,Test1 VARCHAR(100),Test2 DATETIME,ModifyCounter INT DEFAULT 0,LastModified DATETIME DEFAULT GETDATE());
INSERT INTO dbo.Test(ID,Test1,Test2) VALUES
 (1,'Test1',{d'2001-01-01'})
,(2,'Test2',{d'2002-02-02'});

--contenu actuel

SELECT * FROM dbo.Test;
GO

--Le déclencheur pour l'audit

CREATE TRIGGER [dbo].[UpdateTestTrigger]
ON [dbo].[Test]
FOR UPDATE,INSERT,DELETE
AS 
BEGIN
   IF NOT EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) RETURN;

   DECLARE @tp VARCHAR(10)=CASE WHEN EXISTS(SELECT 1 FROM deleted) AND EXISTS(SELECT 1 FROM inserted) THEN 'upd'
                           ELSE CASE WHEN EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) THEN 'del' ELSE 'ins' END END;
   WITH UpdateableCTE AS
   (
    SELECT t.LastModified,t.ModifyCounter 
    FROM dbo.Test AS t
    INNER JOIN inserted AS i ON t.ID=i.ID
   )
   UPDATE UpdateableCTE SET LastModified=GETDATE()
                           ,ModifyCounter=ModifyCounter+1;

   SELECT * INTO #tmpInserted FROM inserted;
   SELECT * INTO #tmpDeleted FROM deleted;

   DECLARE @tableSchema VARCHAR(250)='dbo';
   DECLARE @tableName   VARCHAR(250)='Test';

   DECLARE @cols VARCHAR(MAX)=
   STUFF
   (
   (
    SELECT ',' + CASE WHEN @tp='upd' THEN 
           'CASE WHEN (i.[' + COLUMN_NAME + ']!=d.[' + COLUMN_NAME + '] ' +
           'OR (i.[' + COLUMN_NAME + '] IS NULL AND d.[' + COLUMN_NAME + '] IS NOT NULL) ' + 
           'OR (i.['+ COLUMN_NAME + '] IS NOT NULL AND d.[' + COLUMN_NAME + '] IS NULL)) ' +
           'THEN ' ELSE '' END +
           '(SELECT ''' + COLUMN_NAME + ''' AS [@name]' + 
                         CASE WHEN @tp IN ('upd','del') THEN ',ISNULL(CAST(d.[' + COLUMN_NAME + '] AS NVARCHAR(MAX)),N''##NULL##'') AS [@old]' ELSE '' END + 
                         CASE WHEN @tp IN ('ins','upd') THEN ',ISNULL(CAST(i.[' + COLUMN_NAME + '] AS NVARCHAR(MAX)),N''##NULL##'') AS [@new] ' ELSE '' END + 
                  ' FOR XML PATH(''Column''),TYPE) ' + CASE WHEN @tp='upd' THEN 'END' ELSE '' END
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA=@tableSchema AND TABLE_NAME=@tableName
    FOR XML PATH('')
   ),1,1,''
   );

    DECLARE @cmd VARCHAR(MAX)=   
    'SET LANGUAGE ENGLISH;
    WITH ChangedColumns AS
    (
    SELECT COALESCE(i.ID,d.ID) AS ID
            ,Col.*  
    FROM #tmpInserted AS i
    FULL OUTER JOIN #tmpDeleted AS d ON i.ID=d.ID
    CROSS APPLY
    (
        SELECT ' + @cols + ' 
        FOR XML PATH(''''),TYPE
    ) AS Col([Column])
    )
    INSERT INTO AuditTest(TableSchema,TableName,AuditType,Content)
    SELECT ''' + @tableSchema + ''',''' + @tableName + ''',''' + @tp + '''
    ,(
    SELECT ''' + @tableSchema + ''' AS [@TableSchema]
            ,''' + @tableName + ''' AS [@TableName]
            ,''' + @tp + ''' AS [@ActionType]
    ,(
        SELECT ChangedColumns.ID AS [@ID]
        ,(
        SELECT x.[Column] AS [*],''''
        FROM ChangedColumns AS x WHERE x.ID=ChangedColumns.ID
        FOR XML PATH(''''),TYPE
        )
        FROM ChangedColumns
        FOR XML PATH(''Row''),TYPE
        )
    FOR XML PATH(''Changes'')
    );';

    EXEC (@cmd);

   DROP TABLE #tmpInserted;
   DROP TABLE #tmpDeleted;
END
GO

--Maintenant, testons-le avec quelques opérations :

UPDATE dbo.Test SET Test1='Nouveau 1' WHERE ID=1;
UPDATE dbo.Test SET Test1='Nouveau 1',Test2={d'2000-01-01'} ;
DELETE FROM dbo.Test WHERE ID=2;
DELETE FROM dbo.Test WHERE ID=99; --pas d'effet
INSERT INTO dbo.Test(ID,Test1,Test2) VALUES
 (3,'Test3',{d'2001-03-03'})
,(4,'Test4',{d'2001-04-04'})
,(5,'Test5',{d'2001-05-05'});
UPDATE dbo.Test SET Test2=NULL; --toutes les lignes
DELETE FROM dbo.Test WHERE ID IN (1,3);
GO

--Vérifions l'état final

SELECT * FROM dbo.Test;
SELECT * FROM AuditTest;
GO

--Nettoyage

DROP TABLE dbo.Test;
GO
DROP TABLE dbo.AuditTest;
GO

Résultat de la deuxième action : METTRE À JOUR deux lignes

Résultat de l'action INSERT : trois nouvelles lignes

Résultat de la dernière action de DELETE

Answer 2

J'ai trouvé la solution. Voici le code de déclenchement :

USE [ISContext]
GO
/****** Objet: Déclencheur [dbo].[UpdateCustomerTrigger]    Date du script: 22.05.2016 10:40:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[UpdateCustomerTrigger]
ON [dbo].[Customer]
FOR UPDATE
AS 
BEGIN
    UPDATE Customer SET UpdatedUtc = GETDATE()
    FROM INSERTED
    WHERE inserted.id=Customer.Id

    INSERT INTO [dbo].[AuditTable]
    SELECT
    StateBefore = 
    (
        SELECT *
        FROM deleted [Customer]
        WHERE [Customer].Id = cust.Id
        FOR XML AUTO
    ),
    [StateAfter] =(
        SELECT *
        FROM inserted [Customer]
        WHERE [Customer].Id = cust.Id
        FOR XML AUTO
    )
    FROM inserted cust
END