Journal des modifications à la table de base de données avec déclencheur

Si vous voulez juste pour garder un journal de toutes les transactions (insert, update et delete) dans certains tables de base de données, vous pouvez exécuter le script suivant:

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'Audit')
CREATE TABLE LogTable
(
LogID [int]IDENTITY(1,1) NOT NULL,
Type char(1), 
TableName varchar(128), 
PrimaryKeyField varchar(1000), 
PrimaryKeyValue varchar(1000), 
FieldName varchar(128), 
OldValue varchar(1000), 
NewValue varchar(1000), 
UpdateDate datetime DEFAULT (GetDate()), 
UserName varchar(128)
)
GO
DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON
SELECT @TABLE_NAME= MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.Tables 
WHERE 
--query for table that you want to audit
TABLE_TYPE= 'BASE TABLE' 
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'LogTable'
AND TABLE_NAME!= 'one table to not record de log';
WHILE @TABLE_NAME IS NOT NULL
BEGIN
SELECT 'PROCESANDO ' + @TABLE_NAME;
EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')
SELECT @sql = 'create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete
as
declare 
@bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000), 
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)
select @TableName = ''' + @TABLE_NAME+ '''
-- date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)
-- Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = ''U''
else
select @Type = ''I''
else
select @Type = ''D''
-- get list of columns
select * into #ins from inserted
select * into #del from deleted
-- Get primary key columns for full outer join
select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key fields select for insert(comma deparated)           
select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + '','''''' 
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key values for insert(comma deparated as varchar)           
select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))'' + ''+'''','''''' 
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,    
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c   
where  pk.TABLE_NAME = @TableName   
and CONSTRAINT_TYPE = ''PRIMARY KEY''   
and c.TABLE_NAME = pk.TABLE_NAME   
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME 
if @PKCols is null
begin
raiserror(''no PK on table %s'', 16, -1, @TableName)
return
end
select @sql = ''insert LogTable(Type, TableName, PrimaryKeyField, PrimaryKeyValue, UserName)''
select @sql = @sql + '' select '''''' + @Type + ''''''''
select @sql = @sql + '','''''' + @TableName + ''''''''
select @sql = @sql + '','' + @PKFieldSelect
select @sql = @sql + '','' + @PKValueSelect
select @sql = @sql + '','''''' + @UserName + ''''''''
select @sql = @sql + '' from #ins i full outer join #del d''
select @sql = @sql + @PKCols        
exec (@sql)
';
SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables 
WHERE TABLE_NAME> @TABLE_NAME
--query for table that you want to audit
AND TABLE_TYPE= 'BASE TABLE' 
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'LogTable'
AND TABLE_NAME!= 'one table to not record de log';
END

OriginalL'auteur Juan Carlos Velez

N'oubliez pas que votre enregistrement va être de la partie à la transaction, donc si il y a une erreur et vous rollback de la transaction, votre journal sera également supprimé.

OriginalL'auteur idstam

Il existe un modèle pour la création de ces de ces éléments déclencheurs appelé Journal de Déclenchement. C'est vendeur indépendant et très simple. Il est décrit dans ici.

Les modifications sont enregistrées sur un autre histoire table. Il n'y a aucun moyen de saisir l'énoncé exact, mais il est possible de détecter si c'est l'insertion, et de mettre à jour ou une suppression, car il crée un "enchaîné" du jeu d'enregistrements. L'insertion est un record avec pas de prédécesseur, une suppression d'un enregistrement avec pas de successeur, intermédiaire enregistrements sont mises à jour. Les changements peuvent être détectés en comparant un enregistrement à l'encontre de son prédécesseur.

Il est très facile d'obtenir une instantané d'une seule entité (ou de l'ensemble de la table) à un point donné du temps.

En bonus, la syntaxe de ce modèle pour SQL Server se trouve être la plus simple, par rapport à Oracle, DB2 et MySQL.

OriginalL'auteur user1309314

Déclencheurs sont un bon moyen de s'assurer que toutes les modifications sont enregistrées, depuis ils seront presque toujours le feu indépendamment de la façon dont les mises à jour sont effectuées - par exemple ad-hoc, les connexions ainsi que des connexions d'application.

Comme suggéré par @mwigdahl, le système de gestion de points de vue apparaissent comme un bon moyen de capter le courant par lot. Si c'est particulièrement utile pour ouvrir une session à la détente, c'est autre chose.

Un inconvénient à l'utilisation de déclencheurs, c'est que vous ne pouvez identifier la source de la mise à jour de la base de données de connexion. De nombreuses applications n'ont pas toutes les informations de l'utilisateur associé à la connexion, pour faciliter le regroupement de connexion, de sorte que vous ne connaissez pas l'utilisateur qui effectue l'action. c'est à dire la Connexion utilisée par la connexion est une application générique de connexion plutôt que de la personne qui utilise l'application. La voie normale pour contourner ce problème consiste à utiliser des procédures stockées en tant qu'interface pour tous interaction de base de données, puis de s'assurer qu'un utilisateur est transmis à tous les appels de procédure. Vous pouvez ensuite effectuer votre enregistrement via la procédure stockée à la place d'un déclencheur. Clairement, ce n'est utile que si vous connaissez des gens de ne pas mettre à jour les tables directement, sans l'aide de la procédure, ou n'avez pas besoin de vous connecter à cette situation.

La capacité à obtenir en cours d'exécution du lot pourrait fournir un meilleur mécanisme: si vous assurer que tous vos sql lots contiennent un nom d'utilisateur, vous pouvez l'extraire à partir du sql à l'intérieur de votre détente. Qui vous permettra de faire tous les journaux avec des déclencheurs, ce qui signifie que vous capturez tout, mais aussi vous permettent d'associer des modifications à un utilisateur particulier.

Si vous allez vers le bas sur la gâchette itinéraire, il est conseillé de vérifier les situations les déclencheurs ne sont pas tirés (peut-être en vrac chargement de données? ou si les gens ont l'autorisation de désactiver les déclencheurs).

Également considérer comme @idstam souligné que le code du déclencheur sera à l'intérieur de votre transaction sera normalement connecté et restaurée avec elle.

Une autre chose à considérer lors de la rédaction des déclencheurs est le comportement de @@IDENTITY: si vous avez des procédures à l'aide de @@IDENTITY vous pourriez accidentellement modifier leur comportement.

OriginalL'auteur Rory

Essayez d'installer certains déclencher basé outil tiers comme ApexSQL Audit et puis d'ingénierie inverse de la façon dont ils le font. Il suffit de l'installer en mode d'essai et de voir comment il génère des déclencheurs pour la capture de toutes les différentes sortes d'informations.

Plusieurs autres choses à prendre en compte sont:

La planification du stockage – si vous avez beaucoup de mises à jour, cela signifie que vous aurez une tonne de données d'audit. J'avais envisager de stocker les données dans des bases de données distinctes. Surtout si vous prévoyez sur l'audit de plus d'une base de données.

La gestion de la quantité de données au fil du temps, vous ne sera probablement pas dans un besoin de garder certains très anciens enregistrements. Plan sur la suppression simple des anciennes données

Les modifications de schéma – ce que si le schéma est mis à jour. Dans le pire des cas, ce qui déclenche votre volonté d'arrêter de travailler, et de lever une erreur si pas correctement créé. Dans le meilleur des cas, il vous manque certaines données. C'est aussi quelque chose à considérer.

En prenant tout cela en compte, il est probablement le plus efficace du temps pour aller avec certains déjà développé une solution vs la création de ce à partir de zéro vous-même.

OriginalL'auteur Joel Cuff