SQL Server table d'historique renseigner à travers le SP ou le Déclencheur?

Déclencheurs.

Nous avons écrit un GUI (interne appelé Rouge de Matrix Reloaded) pour faciliter la création/gestion de la journalisation d'audit des déclencheurs.

Voici quelques DDL de la substance utilisée:

---

La AuditLog table

CREATE TABLE [AuditLog] (
    [AuditLogID] [int] IDENTITY (1, 1) NOT NULL ,
    [ChangeDate] [datetime] NOT NULL CONSTRAINT [DF_AuditLog_ChangeDate] DEFAULT (getdate()),
    [RowGUID] [uniqueidentifier] NOT NULL ,
    [ChangeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [TableName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [FieldName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OldValue] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NewValue] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Username] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Hostname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [AppName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [UserGUID] [uniqueidentifier] NULL ,
    [TagGUID] [uniqueidentifier] NULL ,
    [Tag] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
)

---

Déclencheur journal insère

CREATE TRIGGER LogInsert_Nodes ON dbo.Nodes
FOR INSERT
AS

/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier

SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'

IF @SavedUserGUID = @NullGUID
BEGIN
    SET @SavedUserGUID = NULL
END

    /*We dont' log individual field changes Old/New because the row is new.
    So we only have one record - INSERTED*/

    INSERT INTO AuditLog(
            ChangeDate, RowGUID, ChangeType, 
            Username, HostName, AppName,
            UserGUID, 
            TableName, FieldName, 
            TagGUID, Tag, 
            OldValue, NewValue)

    SELECT
        getdate(), --ChangeDate
        i.NodeGUID, --RowGUID
        'INSERTED', --ChangeType
        USER_NAME(), HOST_NAME(), APP_NAME(), 
        @SavedUserGUID, --UserGUID
        'Nodes', --TableName
        '', --FieldName
        i.ParentNodeGUID, --TagGUID
        i.Caption, --Tag
        null, --OldValue
        null --NewValue
    FROM Inserted i

---

Déclencheur journal des Mises à jour

CREATE TRIGGER LogUpdate_Nodes ON dbo.Nodes
FOR UPDATE AS
/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier
SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID
DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'
IF @SavedUserGUID = @NullGUID
BEGIN
SET @SavedUserGUID = NULL
END
/* ParentNodeGUID uniqueidentifier */
IF UPDATE (ParentNodeGUID)
BEGIN
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType, 
Username, HostName, AppName,
UserGUID, 
TableName, FieldName, 
TagGUID, Tag, 
OldValue, NewValue)
SELECT 
getdate(), --ChangeDate
i.NodeGUID, --RowGUID
'UPDATED', --ChangeType
USER_NAME(), HOST_NAME(), APP_NAME(), 
@SavedUserGUID, --UserGUID
'Nodes', --TableName
'ParentNodeGUID', --FieldName
i.ParentNodeGUID, --TagGUID
i.Caption, --Tag
d.ParentNodeGUID, --OldValue
i.ParentNodeGUID --NewValue
FROM Inserted i
INNER JOIN Deleted d
ON i.NodeGUID = d.NodeGUID
WHERE (d.ParentNodeGUID IS NULL AND i.ParentNodeGUID IS NOT NULL)
OR (d.ParentNodeGUID IS NOT NULL AND i.ParentNodeGUID IS NULL)
OR (d.ParentNodeGUID <> i.ParentNodeGUID)
END
/* Caption varchar(255) */
IF UPDATE (Caption)
BEGIN
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType, 
Username, HostName, AppName,
UserGUID, 
TableName, FieldName, 
TagGUID, Tag, 
OldValue, NewValue)
SELECT 
getdate(), --ChangeDate
i.NodeGUID, --RowGUID
'UPDATED', --ChangeType
USER_NAME(), HOST_NAME(), APP_NAME(), 
@SavedUserGUID, --UserGUID
'Nodes', --TableName
'Caption', --FieldName
i.ParentNodeGUID, --TagGUID
i.Caption, --Tag
d.Caption, --OldValue
i.Caption --NewValue
FROM Inserted i
INNER JOIN Deleted d
ON i.NodeGUID = d.NodeGUID
WHERE (d.Caption IS NULL AND i.Caption IS NOT NULL)
OR (d.Caption IS NOT NULL AND i.Caption IS NULL)
OR (d.Caption <> i.Caption)
END
...
/* ImageGUID uniqueidentifier */
IF UPDATE (ImageGUID)
BEGIN
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType, 
Username, HostName, AppName,
UserGUID, 
TableName, FieldName, 
TagGUID, Tag, 
OldValue, NewValue)
SELECT 
getdate(), --ChangeDate
i.NodeGUID, --RowGUID
'UPDATED', --ChangeType
USER_NAME(), HOST_NAME(), APP_NAME(), 
@SavedUserGUID, --UserGUID
'Nodes', --TableName
'ImageGUID', --FieldName
i.ParentNodeGUID, --TagGUID
i.Caption, --Tag
(SELECT Caption FROM Nodes WHERE NodeGUID = d.ImageGUID), --OldValue
(SELECT Caption FROM Nodes WHERE NodeGUID = i.ImageGUID) --New Value
FROM Inserted i
INNER JOIN Deleted d
ON i.NodeGUID = d.NodeGUID
WHERE (d.ImageGUID IS NULL AND i.ImageGUID IS NOT NULL)
OR (d.ImageGUID IS NOT NULL AND i.ImageGUID IS NULL)
OR (d.ImageGUID <> i.ImageGUID)
END

---

Déclencheur pour ouvrir une session Supprimer

CREATE TRIGGER LogDelete_Nodes ON dbo.Nodes
FOR DELETE
AS
/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier
SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID
DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'
IF @SavedUserGUID = @NullGUID
BEGIN
SET @SavedUserGUID = NULL
END
/*We dont' log individual field changes Old/New because the row is new.
So we only have one record - DELETED*/
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType, 
Username, HostName, AppName,
UserGUID, 
TableName, FieldName, 
TagGUID, Tag, 
OldValue,NewValue)
SELECT
getdate(), --ChangeDate
d.NodeGUID, --RowGUID
'DELETED', --ChangeType
USER_NAME(), HOST_NAME(), APP_NAME(), 
@SavedUserGUID, --UserGUID
'Nodes', --TableName
'', --FieldName
d.ParentNodeGUID, --TagGUID
d.Caption, --Tag
null, --OldValue
null --NewValue
FROM Deleted d

---

Et pour savoir qui de l'utilisateur dans le logiciel fait la mise à jour, à chaque connexion "ouvre une session sur SQL Server" par l'appel d'une procédure stockée:

CREATE PROCEDURE dbo.SaveContextUserGUID @UserGUID uniqueidentifier AS
/* Saves the given UserGUID as the session's "Context Information" */
IF @UserGUID IS NULL
BEGIN
PRINT 'Emptying CONTEXT_INFO because of null @UserGUID'
DECLARE @BinVar varbinary(128)
SET @BinVar = CAST( REPLICATE( 0x00, 128 ) AS varbinary(128) )
SET CONTEXT_INFO @BinVar
RETURN 0
END
DECLARE @UserGUIDBinary binary(16) --a guid is 16 bytes
SELECT @UserGUIDBinary = CAST(@UserGUID as binary(16))
SET CONTEXT_INFO @UserGUIDBinary
/* To load the guid back 
DECLARE @SavedUserGUID uniqueidentifier
SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID
select @SavedUserGUID AS UserGUID
*/

---

Notes

• Stackoverflow format de code supprime la plupart des lignes vides - la mise en forme suce
• Nous utilisons une table des utilisateurs, ne sont pas intégrés à la sécurité
• Ce code est fourni en tant que convience - pas de critisism de notre sélection de design de permis. Les puristes pourraient insister pour que tous les code d'enregistrement doit être fait dans la couche de gestion - ils peuvent venir ici et d'écrire/de le maintenir pour nous.
• gouttes ne peuvent pas être enregistrés à l'aide de déclencheurs dans SQL Server (il n'y a pas de "avant" version d'un blob - il seulement ce que c'est). Texte et nText sont blobs - ce qui rend les notes d'unloggable, ou les rend varchar(2000)'s.
• la colonne Tag est utilisé comme un texte arbitraire pour identifier la ligne (par exemple, si un client a été supprimé, la balise se montrer "General Motors en Amérique du Nord" dans le journal d'audit de la table.
• TagGUID est utilisé pour point à la ligne "parent". Par exemple journalisation InvoiceLineItems points de retour à la InvoiceHeader. De cette façon, toute personne à la recherche pour les entrées de journal d'audit liées pour une facture spécifique trouverez supprimés sur la ligne "articles" par la ligne de l'élément de TagGUID dans la piste d'audit.

• parfois le "OldValue" et "NewValue" les valeurs sont écrites en tant que sous-choisir - pour obtenir une chaîne explicite. c'est à dire"

  OldValue: {233d-ad34234..}
  NewValue: {883-sdf34...}

est moins utile dans la piste d'audit de:

OldValue: Daimler Chrysler
NewValue: Cerberus Capital Management

Note finale: n'hésitez pas à ne pas faire ce que nous faisons. C'est génial pour nous, mais tout le monde est libre de ne pas l'utiliser.

Nous disposons d'un outil tiers ApexSQL Audit que nous avons utilisé pour générer des déclencheurs.

Ici est de savoir comment les déclencheurs de ressembler à l'arrière-plan et la façon dont les données sont stockées. Espérons que les gars vont trouver cela utile suffisamment afin d'inverser le processus.
Son un peu différent de ce que Ian Boyd a montré dans ses exemples, car elle permet à chacune des colonnes doivent être vérifiés séparément.

Tableau 1 – contient les détails de la transaction (qui, quand, l'application, le nom d'hôte, etc)

CREATE TABLE [dbo].[AUDIT_LOG_TRANSACTIONS](
[AUDIT_LOG_TRANSACTION_ID] [int] IDENTITY(1,1) NOT NULL,
[DATABASE] [nvarchar](128) NOT NULL,
[TABLE_NAME] [nvarchar](261) NOT NULL,
[TABLE_SCHEMA] [nvarchar](261) NOT NULL,
[AUDIT_ACTION_ID] [tinyint] NOT NULL,
[HOST_NAME] [varchar](128) NOT NULL,
[APP_NAME] [varchar](128) NOT NULL,
[MODIFIED_BY] [varchar](128) NOT NULL,
[MODIFIED_DATE] [datetime] NOT NULL,
[AFFECTED_ROWS] [int] NOT NULL,
[SYSOBJ_ID]  AS (object_id([TABLE_NAME])),
PRIMARY KEY CLUSTERED 
(
[AUDIT_LOG_TRANSACTION_ID] ASC
)
)

Tableau 2 – titulaire d'avant/après les valeurs.

CREATE TABLE [dbo].[AUDIT_LOG_DATA](
[AUDIT_LOG_DATA_ID] [int] IDENTITY(1,1) NOT NULL,
[AUDIT_LOG_TRANSACTION_ID] [int] NOT NULL,
[PRIMARY_KEY_DATA] [nvarchar](1500) NOT NULL,
[COL_NAME] [nvarchar](128) NOT NULL,
[OLD_VALUE_LONG] [ntext] NULL,
[NEW_VALUE_LONG] [ntext] NULL,
[NEW_VALUE_BLOB] [image] NULL,
[NEW_VALUE]  AS (isnull(CONVERT([varchar](8000),      [NEW_VALUE_LONG],0),CONVERT([varchar](8000),CONVERT([varbinary](8000),substring([NEW_VALUE_BLOB],(1),(8000)),0),0))),
[OLD_VALUE]  AS (CONVERT([varchar](8000),[OLD_VALUE_LONG],0)),
[PRIMARY_KEY]  AS ([PRIMARY_KEY_DATA]),
[DATA_TYPE] [char](1) NOT NULL,
[KEY1] [nvarchar](500) NULL,
[KEY2] [nvarchar](500) NULL,
[KEY3] [nvarchar](500) NULL,
[KEY4] [nvarchar](500) NULL,
PRIMARY KEY CLUSTERED 
(
[AUDIT_LOG_DATA_ID] ASC
)
)

Déclencheur d'insertion

Je ne suis pas montrant des déclencheurs pour la mise à jour parce qu'ils sont assez long et ont la même logique que celle-ci.

CREATE TRIGGER [dbo].[tr_i_AUDIT_Audited_Table]
ON [dbo].[Audited_Table]
FOR INSERT
NOT FOR REPLICATION
As
BEGIN
DECLARE 
@IDENTITY_SAVE              varchar(50),
@AUDIT_LOG_TRANSACTION_ID       Int,
@PRIM_KEY               nvarchar(4000),
@ROWS_COUNT             int
SET NOCOUNT ON
Select @ROWS_COUNT=count(*) from inserted
Set @IDENTITY_SAVE = CAST(IsNull(@@IDENTITY,1) AS varchar(50))
INSERT
INTO dbo.AUDIT_LOG_TRANSACTIONS
(
TABLE_NAME,
TABLE_SCHEMA,
AUDIT_ACTION_ID,
HOST_NAME,
APP_NAME,
MODIFIED_BY,
MODIFIED_DATE,
AFFECTED_ROWS,
[DATABASE]
)
values(
'Audited_Table',
'dbo',
2,  --  ACTION ID For INSERT
CASE 
WHEN LEN(HOST_NAME()) < 1 THEN ' '
ELSE HOST_NAME()
END,
CASE 
WHEN LEN(APP_NAME()) < 1 THEN ' '
ELSE APP_NAME()
END,
SUSER_SNAME(),
GETDATE(),
@ROWS_COUNT,
'Database_Name'
)
Set @AUDIT_LOG_TRANSACTION_ID = SCOPE_IDENTITY()    
--This INSERT INTO code is repeated for each columns that is audited. 
--Below are examples for only two columns
INSERT INTO dbo.AUDIT_LOG_DATA
(
AUDIT_LOG_TRANSACTION_ID,
PRIMARY_KEY_DATA,
COL_NAME,
NEW_VALUE_LONG,
DATA_TYPE
, KEY1
)
SELECT
@AUDIT_LOG_TRANSACTION_ID,
convert(nvarchar(1500), IsNull('[PK_Column]='+CONVERT(nvarchar(4000), NEW.[PK_Column], 0), '[PK_Column] Is Null')),
'Column1',
CONVERT(nvarchar(4000), NEW.[Column1], 0),
'A'
, CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[PK_Column], 0))
FROM inserted NEW
WHERE NEW.[Column1] Is Not Null
--value is inserted for each column that is selected for auditin
INSERT INTO dbo.AUDIT_LOG_DATA
(
AUDIT_LOG_TRANSACTION_ID,
PRIMARY_KEY_DATA,
COL_NAME,
NEW_VALUE_LONG,
DATA_TYPE
, KEY1
)
SELECT
@AUDIT_LOG_TRANSACTION_ID,
convert(nvarchar(1500), IsNull('[PK_Column]='+CONVERT(nvarchar(4000), NEW.[PK_Column], 0), '[PK_Column] Is Null')),
'Column2',
CONVERT(nvarchar(4000), NEW.[Column2], 0),
'A'
, CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[PK_Column], 0))
FROM inserted NEW
WHERE NEW.[Column2] Is Not Null
End

Avertissement: je ne suis pas affilié avec l'Apex, mais en aucune façon je ne l'utilisation de leurs outils dans mon travail actuel.

Approche recommandée dépend de vos besoins. Si la table d'historique est là pour de la piste d'audit, vous avez besoin pour capturer chaque opération. Si l'histoire de la table est seulement pour performance raisons, puis un planifiée de l'Agent SQL travail de transfert de données devrait être suffisant.

Pour la capture de chaque opération à l'utiliser APRÈS Déclencheurs ou de Capture de Données modifiées.

Après les déclencheurs de vous fournir avec deux tables temporaires pour fonctionner à l'intérieur de la gâchette:

• INSÉRÉ après l'INSERTION ou la mise à JOUR
• SUPPRIMÉ après SUPPRIMER

Vous pouvez effectuer des insertions à la table d'historique à partir de ces tables temporaires et votre table d'historique sera toujours à jour. Vous pouvez ajouter un numéro de version, l'heure ou les deux à la table d'historique pour séparer la modification d'une seule source ligne.

De Capture de Données modifiées (CDC) est conçu pour la création d'un delta de table que vous pouvez utiliser comme une source de chargement des données dans un entrepôt de données (ou un tableau de l'historique). À la différence des déclencheurs, des CDC est asynchrone et vous pouvez utiliser n'importe quelle méthode et de programmation pour le remplissage de votre destination (sprocs, SSIS).

Vous pouvez accéder à la fois à des données originales et des changements avec les CDC. Le Suivi Des Modifications (CT) détecte uniquement les lignes modifiées. Il est possible de construire une piste de vérification complète des CDC, mais pas avec la CT. CDC et CT sont à la fois disponibles uniquement dans la MSSQL 2008 Enterprise edition et Developer edition.

Une question d'être très prudent à ce sujet est d'identifier votre cas d'utilisation de cette table, et assurez-vous qu'il est bien construit à cet effet.

Plus précisément, si c'est pour un opérationnel de la piste d'audit pour les intervenants, c'est assez différent de l'avant-et-après des instantanés de l'enregistrement des modifications dans les tables. (En fait, j'ai un moment difficile d'imaginer une bonne utilisation pour enregistrer les modifications, autres que le débogage.)

Une piste de vérification requiert en principe, au minimum, un id d'utilisateur, un timestamp, et un code d'opération - et probablement quelques détails sur le fonctionnement. Exemple - modifier la quantité commandée sur un point de la ligne sur un ordre d'achat.

Et pour ce type de piste d'audit vous ne pas souhaitez utiliser des déclencheurs. Le plus élevé dans le BR de la couche d'incorporer la génération de ces événements, le mieux.

Otoh, que, pour l'enregistrement des changements de niveau, les déclencheurs sont le droit de match. Mais c'est aussi souvent plus facile d'obtenir ce à partir de votre sgbd journalisation des fichiers.

Déclencheurs. Droit maintenant, vous pourriez être en mesure de dire que la seule manière de données est mis à jour par le biais de votre SPs, mais les choses peuvent changer, ou vous pourriez avoir besoin pour faire une masse d'insertion/mise à jour à l'aide de la SPs sera trop lourd pour. Aller avec des déclencheurs.

Déclencheurs. Voici ma démarche:

1. Créer une table pour chaque table critique qui nécessite d'audit procès
2. Table d'Audit comprendra toutes les colonnes de la table source + colonnes enregistrement d'audit des informations telles que: qui, quand et l'action
3. Déclencheur pour mettre à JOUR et SUPPRIMER seulement, l'opération d'INSERTION auront la vierge enregistrement dans la table source elle-même
4. Avant de mettre à jour ou de supprimer, de copier l'original + la vérification des informations pour la vérification de la table de
5. (En option - pour mettre à JOUR uniquement:) À savoir la colonne qui s'est mis à jour, appliquez une mise à JOUR(ColumnName) ou COLUMNS_UPDATED() construit en fonction SQL pour déterminer les colonnes concernées

L'audit de cette manière continue à l'état actuel de la table source et toute l'histoire de la vérification de la table et facilement identifiable par les colonnes de clé.