Melden Sie änderungen der Datenbank-Tabelle mit trigger

Wenn Sie nur wollen, zu halten Protokoll aller Transaktionen (insert, update und delete) in einigen Tabellen der Datenbank, dann können Sie das folgende Skript ausführen:

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

Vergessen Sie nicht, dass Ihre Anmeldung wird ein Teil der Transaktion, also wenn ein Fehler vorliegt, und Sie ein rollback für die Transaktion, dein log auch gelöscht werden.

Gibt es ein Muster für die Erstellung wie diese Auslöser genannt Log Trigger. Das ist Hersteller unabhängig und sehr einfach. Es ist beschrieben in hier.

Der Veränderungen, die aufgezeichnet werden auf einem anderen Geschichte Tabelle. Es gibt keine Möglichkeit zu greifen, die genaue Aussage, aber es ist möglich zu erkennen, ob es war ein einfügen und aktualisieren oder löschen, denn es sorgt für ein "Ketten" - Satz von Datensätzen. Eine insertion ist eine Aufzeichnung ohne Vorgänger, ist eine Löschung eines Datensatzes ohne Nachfolger, intermediate Datensätze werden aktualisiert. Veränderungen können erkannt werden, vergleicht man einen Datensatz gegen seine Vorgänger.

Ist es sehr einfach, ein snapshot von einer einzigen Person (oder die gesamte Tabelle) an einem gegebenen Punkt in der Zeit.

Als bonus, die syntax dieses Muster für den SQL-Server erfolgt auf die einfachste, im Vergleich zu Oracle, DB2 und MySQL.

Trigger sind eine gute Möglichkeit, um sicherzustellen, dass alle änderungen protokolliert werden, da Sie fast immer ausgelöst, unabhängig davon, wie die updates durchgeführt werden - z.B. ad-hoc-verbindungen als auch die Anwendung von verbindungen.

Vorgeschlagen von @mwigdahl, die system-management-Ansichten, die Aussehen wie ein guter Weg, um erfassen des aktuellen Batches. Ob das ist besonders nützlich, um log-in-der Auslöser ist eine andere Sache.

Einen Nachteil bei der Verwendung von Triggern ist, dass Sie nur identifizieren Sie die Quelle des update aus der Datenbank-Verbindung. Viele Anwendungen haben keine Benutzer Informationen, die im Zusammenhang mit der Verbindung zu erleichtern, connection pooling, so dass Sie nicht wissen, welcher Benutzer die Aktion ausführt. ie der Login verwendet, durch die Verbindung ist eine generische Anwendung, die login-eher als die person, die mit der Anwendung. Der normale Weg, dies zu umgehen, ist die Verwendung von gespeicherten Prozeduren als Schnittstelle, um alle Datenbank-Interaktion, und dann sicherzustellen, dass eine Benutzer-id übergeben wird, mit all procedure calls. Sie können dann Ihre Anmeldung über die gespeicherte Prozedur anstelle eines triggers. Natürlich ist dies nur sinnvoll, wenn Sie wissen, dass die Menschen nicht update-Tabellen direkt und ohne Verwendung von Verfahren oder nicht anmelden müssen, die situation.

Die Möglichkeit zu bekommen, die derzeit ausgeführten batch könnten, bieten einen noch besseren Mechanismus: wenn Sie sicherstellen, dass alle Ihre sql-batches enthalten eine Benutzer-id Sie können dann extrahieren Sie diese aus dem sql im trigger. Das würde Ihnen ermöglichen, zu tun, alle logging-Trigger, das heißt, Sie erfassen alles, aber auch lassen Sie verknüpfen die änderungen eines bestimmten Benutzers.

Wenn Sie gehen hinunter den Auslöser route-es lohnt sich, die Situationen, die Auslöser sind nicht gefeuert (vielleicht bulk-Daten geladen? oder wenn die Leute über die Berechtigung zum deaktivieren von Triggern).

Berücksichtigen Sie auch, wie @idstam wies darauf hin, dass die trigger-code in Ihre Transaktion so werden in der Regel protokolliert und rollten sich wieder zusammen mit ihm.

Andere Sache zu prüfen, wenn das schreiben löst das Verhalten der @@IDENTITY: wenn Sie über Verfahren verfügen, mit der @@IDENTITY, die Sie möglicherweise versehentlich Ihr Verhalten ändern.

Versuchen Sie, einige trigger-basierte third-party-tool wie ApexSQL Audit und dann reverse-engineering, wie Sie es tun. Installieren Sie es einfach im trial-Modus und sehen, wie es generiert den Trigger für die Erfassung aller anderen Arten von Informationen.

Einige andere Dinge zu beachten sind:

Storage-Planung – wenn Sie eine Menge von updates, das bedeutet, Sie haben eine Tonne von audit-Daten. Ich würde erwägen, das speichern, die Daten in separaten Datenbanken. Vor allem, wenn Sie planen, Rechnungsprüfung mehr als eine Datenbank.

Verwalten die Menge der Daten, die im Laufe der Zeit werden Sie wahrscheinlich nicht brauchen, zu halten, einige sehr alte Aufzeichnungen. Planen Sie auf einfache löschen von alten Daten

Schema-änderungen – was wäre wenn-schema aktualisiert. Im schlimmsten Fall löst, wird aufhören zu arbeiten und werfen einen Fehler, wenn nicht richtig erstellt. Im besten Fall werden Sie vermissen einige der Daten. Das ist auch etwas zu prüfen.

Nimmt man all dies berücksichtigt, ist es wahrscheinlich die meiste Zeit effektiv zu gehen mit einige bereits entwickelte Lösung vs erstellen, diese von Grund auf selbst.