La création de déclencheurs d'audit dans SQL Server

Je veux juste pour appeler quelques points:

Utiliser des générateurs de code Vous ne pouvez pas avoir une seule procédure à suivre toutes les tables, vous aurez besoin de générer similaire mais distinct des déclencheurs sur chaque table de suivi. Ce genre de travail est le mieux adapté pour automatiser la génération de code. À votre place, je voudrais utiliser une transformation XSLT pour générer le code à partir de XML et XML peuvent être générées automatiquement à partir des métadonnées. Cela vous permet de gérer facilement les déclencheurs par les régénérer à chaque fois que vous apportez une modification à la vérification de la logique ou de la structure ou une table cible est ajouté/modifié.

Envisager la planification de la capacité pour la vérification. Une table d'audit permettant de suivre tous les changements de la valeur sera, de loin, la plus grosse table dans la base de données: il contiendra toutes les données actuelles et les
toute l'histoire de l'actuel des données. Ce tableau permettra d'augmenter la taille de base de données de 2 à 3 ordres de grandeur (x10, x100). Et la table d'audit va rapidement devenir le goulot d'étranglement de tout:

• chaque opération DML requièrent des verrous dans la table d'audit
• administrative et des opérations de maintenance devra s'adapter à la taille de la base de données en raison de la vérification

Prendre en compte la les modifications de schéma. Une table nommée 'Foo' peut être supprimée et, plus tard, une autre table nommée 'Foo' peut être créé. La piste d'audit doit être capable de distinguer les deux objets différents. Vaut mieux utiliser un lent de changer de dimension approche.

Envisager la nécessité de efficacement supprimer des enregistrements d'audit. Lorsque la période de rétention dictée par votre application sous réserve des politiques est dû, vous devez être en mesure de supprimer la cause des enregistrements d'audit. Il peut ne pas sembler une grosse affaire maintenant, mais 5 ans plus tard, quand les premiers enregistrements sont dus la table d'audit a augmenté à 9,5 TUBERCULOSE, il peut être un problème.

Envisager la nécessité de requête de la vérification. La table d'audit de la structure doit être prêt à répondre efficacement aux requêtes de l'audit. Si votre audit ne peut pas être interrogé alors qu'il n'a pas de valeur. Les requêtes seront entièrement due à vos exigences et vous seul savez le faire, mais la plupart des enregistrements d'audit sont interrogés pour des intervalles de temps ("quels changements ont eu lieu entre 7h et 8h hier?'), par objet ("quels changements ont eu lieu à cet enregistrement dans cette table?') ou par l'auteur ("quels changements avez-Bob dans la base de données?').

Il n'existe pas de méthode générique permettant de le faire de la manière que vous voulez. Finalement, vous finissez par écrire des bouts de code pour chaque table. Sans oublier qu'il peut être fée lent si vous avez besoin de comparer chaque colonne pour le changement.

Aussi le fait que vous pourriez être à jour plusieurs lignes en même temps implique que vous avez besoin pour ouvrir un curseur pour faire une boucle sur tous les enregistrements.

Je ferais ça sera l'aide de la table dont la structure est identique pour les tables de suivi et unpivot plus tard pour afficher les colonnes ont réellement changé. Je voudrais aussi garder une trace de la session qui a vraiment fait le changement. Cela suppose que vous avez la clé primaire dans la table en cours de suivi.

Donc, étant donné un tableau comme celui

CREATE TABLE TestTable  
(ID INT NOT NULL CONSTRAINT PK_TEST_TABLE PRIMARY KEY,
Name1 NVARCHAR(40) NOT NULL,  
Name2 NVARCHAR(40))

Je voudrais créer une table d'audit de ce genre dans l'audit schmea.

CREATE TABLE Audit.TestTable  
(SessionID UNIQUEIDENTIFER NOT NULL,  
ID INT NOT NULL,
Name1  NVARCHAR(40) NOT NULL,  
Name2  NVARCHAR(40),  
Action NVARCHAR(10) NOT NULL CONSTRAINT CK_ACTION CHECK(Action In 'Deleted','Updated'),  
RowType NVARCHAR(10) NOT NULL CONSTRAINT CK_ROWTYPE CHECK (RowType in 'New','Old','Deleted'),  
ChangedDate DATETIME NOT NULL Default GETDATE(),  
ChangedBy SYSNHAME NOT NULL DEFAULT USER_NAME())

Et un déclencheur pour la mise à Jour comme ceci

CREATE Trigger UpdateTestTable ON DBO.TestTable FOR UPDATE AS  
BEGIN  
    SET NOCOUNT ON
    DECLARE @SessionID UNIQUEIDENTIFER
    SET @SessionID = NEWID()
    INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID)
    SELECT ID,name1,Name2,'Updated','Old',@SessionID FROM Deleted

    INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID)
    SELECT ID,name1,Name2,'Updated','New',@SessionID FROM Inserted

END

Cela va assez vite. Pendant la rédaction du rapport , il vous suffit de rejoindre les lignes en fonction de sessionID, et la clé Primaire et de produire un rapport. Alternativement, vous pouvez avoir un travail par lots qui périodiquement passe par toutes les tables dans la table d'audit et de préparer une paire nom-valeur indiquant les changements.

HTH

Je vais me jeter dans mon approche et des suggestions pour le mélange.

J'ai très semblable à la table de votre projet de la conception que j'ai utilisé pour les sept dernières années sur un SQL 2005 (maintenant 2008) de la base de données.

J'ai ajouté insérer, mettre à jour et supprimer des déclencheurs pour des tables sélectionnées, puis vérifié pour les changements de champs sélectionnés. À l'époque c'était simple et fonctionne bien.

Ici les questions que j'ai trouver avec cette approche:

1. La table d'audit de l'ancienne et de la nouvelle valeur du champs devaient être de type varchar(MAX) types pour être en mesure de faire face à toutes les valeurs qui pourraient être vérifiés: int,bool,décimal,float,varchar, etc. tous ont pour s'adapter à

2. Le code de vérification pour chaque champ est fastidieux à écrire un maintenir. Il est également facile de manquer des choses (comme la modification d'un champ nul pour une valeur de ne pas se faire attraper, parce que NULL != la valeur est NULL.

3. Supprimer un enregistrement: comment voulez-vous enregistrer ce? Tous les champs? Ceux qui sont sélectionnés? Il devient compliqué

Ma vision pour l'avenir est d'utiliser SQL CLR code et écrire une generic trigger est exécuté et les chèques de table de méta-données pour voir ce que d'audit. Deuxièmement, les Nouvelles/Anciennes valeurs seront convertis en champs XML et l'ensemble de l'objet enregistré: il en résulte plus de données, mais supprimer a tout un album. Il y a plusieurs articles sur le web à propos de XML déclencheurs d'audit.

Déclencheur est utilisé pour Si vous les modifier ou de les insérer dans un tableau particulier, ce sera execute, et vous pouvez vérifier le particulier de la colonne de déclenchement. Exemple complet avec explication se trouve dans le site web suivant.
http://www.allinworld99.blogspot.com/2015/04/triggers-in-sql.html

Chaque table on veut surveiller, aura besoin de son propre déclencheur. Il est assez évident, que - comme l'a souligné dans l'acceptation de réponse - la génération de code sera une bonne chose.

Si vous aimez cette approche, il pourrait être une idée pour utiliser ce déclencheur et de remplacer certaines étapes générales avec code généré pour chaque table séparément.

Néanmoins, j'ai créé un entièrement générique Vérification de Déclenchement. L'observé table doit avoir un PK, mais ce PK peut-être même multi-colonne.

Certains types de colonnes (comme des Gouttes) peut ne pas fonctionner, mais vous pouvez facilement les exclure.

Ce ne sera pas le meilleur dans la performance 😀

Pour être honnête: C'est plus le genre d'exercice...

SET NOCOUNT ON;
GO
CREATE TABLE AuditTest(ID UNIQUEIDENTIFIER
,LogDate DATETIME
,TableSchema VARCHAR(250)
,TableName VARCHAR(250)
,AuditType VARCHAR(250),Content XML);
GO

--Une table pour tester cette (utilisé bizarre PK colonnes sur le but...)

CREATE TABLE dbo.Testx(ID1 DATETIME NOT NULL
,ID2 UNIQUEIDENTIFIER NOT NULL
,Test1 VARCHAR(100)
,Test2 DATETIME);
--Add a two column PK
ALTER TABLE dbo.Testx ADD CONSTRAINT PK_Test PRIMARY KEY(ID1,ID2);

--Quelques données de test

INSERT INTO dbo.Testx(ID1,ID2,Test1,Test2) VALUES
({d'2000-01-01'},NEWID(),'Test1',NULL)
,({d'2000-02-01'},NEWID(),'Test2',{d'2002-02-02'});

--C'est le contenu actuel

SELECT * FROM dbo.Testx;
GO

--Le trigger pour la vérification

    CREATE TRIGGER [dbo].[UpdateTestTrigger]
ON [dbo].[Testx]
FOR UPDATE,INSERT,DELETE
AS 
BEGIN
IF NOT EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) RETURN;
SET NOCOUNT ON;
DECLARE @tableSchema VARCHAR(250);
DECLARE @tableName   VARCHAR(250);
DECLARE @AuditID UNIQUEIDENTIFIER=NEWID();
DECLARE @LogDate DATETIME=GETDATE();
SELECT @tableSchema = sch.name
,@tableName   = tb.name
FROM sys.triggers AS tr
INNER JOIN sys.tables AS tb ON tr.parent_id=tb.object_id 
INNER JOIN sys.schemas AS sch ON tb.schema_id=sch.schema_id
WHERE tr.object_id = @@PROCID
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;
SELECT * INTO #tmpInserted FROM inserted;
SELECT * INTO #tmpDeleted FROM deleted;
SELECT kc.ORDINAL_POSITION, kc.COLUMN_NAME
INTO #tmpPKColumns
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc 
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kc ON tc.TABLE_CATALOG=kc.TABLE_CATALOG
AND tc.TABLE_SCHEMA=kc.TABLE_SCHEMA
AND tc.TABLE_NAME=kc.TABLE_NAME
AND tc.CONSTRAINT_NAME=kc.CONSTRAINT_NAME
AND tc.CONSTRAINT_TYPE='PRIMARY KEY'
WHERE tc.TABLE_SCHEMA=@tableSchema
AND tc.TABLE_NAME=@tableName
ORDER BY kc.ORDINAL_POSITION;
DECLARE @pkCols VARCHAR(MAX)=
STUFF
(
(
SELECT 'UNION ALL SELECT ''' + pc.COLUMN_NAME + ''' AS [@name] , CAST(COALESCE(i.' + QUOTENAME(pc.COLUMN_NAME) + ',d.' + QUOTENAME(pc.COLUMN_NAME) + ') AS VARCHAR(MAX)) AS [@value] '
FROM #tmpPKColumns AS pc
ORDER BY pc.ORDINAL_POSITION
FOR XML PATH('')
),1,16,'');
DECLARE @pkColsCompare VARCHAR(MAX)=
STUFF
(
(
SELECT 'AND i.' + QUOTENAME(pc.COLUMN_NAME) + '=d.' + QUOTENAME(pc.COLUMN_NAME) 
FROM #tmpPKColumns AS pc
ORDER BY pc.ORDINAL_POSITION
FOR XML PATH('')
),1,3,'');
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   A.PK' +
',A.PK.query(''data(/PK/Column/@value)'').value(''text()[1]'',''nvarchar(max)'') AS PKVals' +
',Col.*  
FROM #tmpInserted AS i
FULL OUTER JOIN #tmpDeleted AS d ON ' + @pkColsCompare +
' CROSS APPLY
(
SELECT ' + @cols + ' 
FOR XML PATH(''''),TYPE
) AS Col([Column])
CROSS APPLY(SELECT (SELECT tbl.* FROM (SELECT ' + @pkCols + ') AS tbl FOR XML PATH(''Column''), ROOT(''PK''),TYPE)) AS A(PK)
)
INSERT INTO AuditTest(ID,LogDate,TableSchema,TableName,AuditType,Content)
SELECT  ''' + CAST(@AuditID AS VARCHAR(MAX)) + ''',''' + CONVERT(VARCHAR(MAX),@LogDate,126) + ''',''' + @tableSchema + ''',''' + @tableName + ''',''' + @tp + '''
,(
SELECT ''' + @tableSchema + ''' AS [@TableSchema]
,''' + @tableName + ''' AS [@TableName]
,''' + @tp + ''' AS [@ActionType]
,(
SELECT ChangedColumns.PK AS [*]
,(
SELECT x.[Column] AS [*],''''
FROM ChangedColumns AS x 
WHERE x.PKVals=ChangedColumns.PKVals
FOR XML PATH(''Values''),TYPE
)
FROM ChangedColumns
FOR XML PATH(''Row''),TYPE
)
FOR XML PATH(''Changes'')
);';
EXEC (@cmd);
DROP TABLE #tmpInserted;
DROP TABLE #tmpDeleted;
END
GO

--Maintenant, nous allons le tester avec certaines opérations:

UPDATE dbo.Testx SET Test1='New 1' WHERE ID1={d'2000-01-01'};
UPDATE dbo.Testx SET Test1='New 1',Test2={d'2000-01-01'} ;
DELETE FROM dbo.Testx WHERE ID1={d'2000-02-01'};
DELETE FROM dbo.Testx WHERE ID1=GETDATE(); --no affect
INSERT INTO dbo.Testx(ID1,ID2,Test1,Test2) VALUES
({d'2000-03-01'},NEWID(),'Test3',{d'2001-03-03'})
,({d'2000-04-01'},NEWID(),'Test4',{d'2001-04-04'})
,({d'2000-05-01'},NEWID(),'Test5',{d'2001-05-05'});
UPDATE dbo.Testx SET Test2=NULL; --all rows
DELETE FROM dbo.Testx WHERE ID1 IN ({d'2000-02-01'},{d'2000-03-01'});
GO

--Vérifier le statut final

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

--Nettoyer (attention avec des données réelles!)

DROP TABLE dbo.Testx;
GO
DROP TABLE dbo.AuditTest;
GO

Le résultat de l'insertion

<Changes TableSchema="dbo" TableName="Testx" ActionType="ins">
<Row>
<PK>
<Column name="ID1" value="May  1 2000 12:00AM" />
<Column name="ID2" value="C2EB4D11-63F8-434E-8470-FB4A422A4ED1" />
</PK>
<Values>
<Column name="ID1" new="May  1 2000 12:00AM" />
<Column name="ID2" new="C2EB4D11-63F8-434E-8470-FB4A422A4ED1" />
<Column name="Test1" new="Test5" />
<Column name="Test2" new="May  5 2001 12:00AM" />
</Values>
</Row>
<Row>
<PK>
<Column name="ID1" value="Apr  1 2000 12:00AM" />
<Column name="ID2" value="28625CE7-9424-4FA6-AEDA-1E4853451655" />
</PK>
<Values>
<Column name="ID1" new="Apr  1 2000 12:00AM" />
<Column name="ID2" new="28625CE7-9424-4FA6-AEDA-1E4853451655" />
<Column name="Test1" new="Test4" />
<Column name="Test2" new="Apr  4 2001 12:00AM" />
</Values>
</Row>
<Row>
<PK>
<Column name="ID1" value="Mar  1 2000 12:00AM" />
<Column name="ID2" value="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
</PK>
<Values>
<Column name="ID1" new="Mar  1 2000 12:00AM" />
<Column name="ID2" new="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
<Column name="Test1" new="Test3" />
<Column name="Test2" new="Mar  3 2001 12:00AM" />
</Values>
</Row>
</Changes>

La sélective suite à une mise à jour

<Changes TableSchema="dbo" TableName="Testx" ActionType="upd">
<Row>
<PK>
<Column name="ID1" value="Feb  1 2000 12:00AM" />
<Column name="ID2" value="D7AB263A-EEFC-47DB-A6BB-A559FE8F2119" />
</PK>
<Values>
<Column name="Test1" old="Test2" new="New 1" />
<Column name="Test2" old="Feb  2 2002 12:00AM" new="Jan  1 2000 12:00AM" />
</Values>
</Row>
<Row>
<PK>
<Column name="ID1" value="Jan  1 2000 12:00AM" />
<Column name="ID2" value="318C0A66-8833-4F03-BCEF-7AB78C91704F" />
</PK>
<Values>
<Column name="Test2" old="##NULL##" new="Jan  1 2000 12:00AM" />
</Values>
</Row>
</Changes>

Et le résultat de supprimer

<Changes TableSchema="dbo" TableName="Testx" ActionType="del">
<Row>
<PK>
<Column name="ID1" value="Mar  1 2000 12:00AM" />
<Column name="ID2" value="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
</PK>
<Values>
<Column name="ID1" old="Mar  1 2000 12:00AM" />
<Column name="ID2" old="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
<Column name="Test1" old="Test3" />
<Column name="Test2" old="##NULL##" />
</Values>
</Row>
</Changes>