SQL server Historie Tabelle – füllen Sie durch SP oder Trigger?

In meinem SQL server-Backend für meine App möchte ich History-Tabellen für ein paar meiner Schlüsseltabellen erstellen, die einen Verlauf der Änderungen an den Zeilen verfolgen werden.

Meine gesamte Anwendung verwendet gespeicherte Prozeduren, es gibt kein eingebettetes SQL. Die einzige Verbindung zur database, um diese Tabellen zu ändern, erfolgt über die Anwendung und die SP-interface. Traditionsgemäß haben Läden, mit denen ich gearbeitet habe, diese Aufgabe mit Triggern durchgeführt.

Wenn ich eine Wahl zwischen gespeicherten Prozeduren und Trigger habe, was besser ist? Welche ist schneller?

Löst aus.

Wir haben eine GUI (intern genannt Red Matrix Reloaded ) geschrieben, um eine einfache Erstellung / Verwaltung von Audit-Logging-Triggern zu ermöglichen.

Hier ist ein DDL des verwendeten Materials:


Die AuditLog-Tabelle

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 ) 

Auslöser zum Einfügen von Einsätzen

 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 

Trigger, um Updates zu protokollieren

 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 ... 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 

Trigger zum Löschen von Löschen

 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 

Und um zu wissen, welcher Benutzer in der Software das Update gemacht hat, loggt sich jede Verbindung "sich auf SQL server" an, indem sie eine gespeicherte Prozedur aufruft:

 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 */ 

Notizen

  • Stackoverflow-Code-Format entfernt die meisten leeren Zeilen – so Formatierung saugt
  • Wir verwenden eine Tabelle von Benutzern, nicht integrierte security
  • Dieser Code wird als Überzeugung zur Verfügung gestellt – keine Kritik an unserer Designauswahl erlaubt. Puristen könnten darauf bestehen, dass alle Logging-Code in der Business-Schicht durchgeführt werden – sie können hierher kommen und schreiben / pflegen für uns.
  • Blobs kann nicht mit Triggern in SQL server protokolliert werden (es gibt keine "vor" Version eines Blob – da ist nur was ist). Text und nText sind Blobs – das macht Notizen entweder unloggable, oder macht sie varchar (2000) 's.
  • Die Tag-Spalte wird als beliebiger Text verwendet, um die Zeile zu identifizieren (zB wenn ein Kunde gelöscht wurde, zeigt das Tag "General Motors North America" ​​in der Audit-Log-Tabelle an.
  • TagGUID wird verwendet, um auf das "Elternteil" der Zeile zu zeigen. Zum Beispiel protokolliert InvoiceLineItems auf den InvoiceHeader . Auf diese Weise findet jeder, der nach Audit-Log-Einträgen sucht, die für eine bestimmte Rechnung vergeben werden, die gelöschten "Werbebuchungen" durch die TagGUID der Werbebuchung im Prüfpfad.
  • Manchmal werden die Werte "OldValue" und "NewValue" als Sub-Select geschrieben – um einen aussagekräftigen String zu erhalten. dh "

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

ist weniger nützlich im Audit Trail als:

 OldValue: Daimler Chrysler NewValue: Cerberus Capital Management 

Endgültige Anmerkung : Fühlen Sie sich frei, nicht zu tun, was wir tun. Das ist toll für uns, aber alle anderen sind frei, es nicht zu benutzen.

In SQL server 2008 kann eine neue function namens CDC (Change Data Capture) CDC auf MSDN helfen. CDC ist eine Fähigkeit, Änderungen an Tabellendaten in eine andere Tabelle aufzunehmen, ohne Trigger oder einen anderen Mechanismus zu schreiben, dataerfassung zu ändern, zeichnet die Änderungen wie Einfügen, Aktualisieren und Löschen auf eine Tabelle im SQL-server auf, so dass die Details der Änderungen in relationalen Informationen verfügbar sind Format.

Channel9 Video

Wir haben ein Drittanbieter-Tool ApexSQL Audit , mit dem wir Trigger generiert haben.

Hier ist, wie Auslöser im background aussehen und wie data gespeichert sind. Hoffentlich werden die Jungs das nützlich genug finden, um den process umzukehren. Es ist ein bisschen anders als das, was Ian Boyd in seinen Beispielen gezeigt hat, weil es erlaubt, jede Spalte separat zu auditieren.

Tabelle 1 – enthält Transaktionsdetails (wer, wann, Anwendung, Hostname, 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 ) ) ( 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 ) ) ) 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 ) ) 

Tabelle 2 – gilt vor / nach Werten.

 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 ) ) ( 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 ) ) ) 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 ) ) 

Trigger insert

Ich zeige keine Trigger für das Update, weil sie ziemlich lang sind und die gleiche Logik haben wie diese.

 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 ( 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 ) 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 ) 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 ( 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 ) 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 ( 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 ) 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 

Haftungsausschluss: Ich bin in keiner Weise mit Apex verbunden, aber ich benutze ihre Werkzeuge in meinem aktuellen Job.

Wie alle anderen sagten, Trigger. Sie sind einfacher zu Unit-Test und weitaus belastbarer, um Benutzer mit unerwartetem Zugriff direkt auf die Tische zu machen, die randome Abfragen machen.

Wie für schneller? Bestimmen, was schnell in einer database ist ein hartes Problem mit einer großen Anzahl von variables. Kurze von "versuchen Sie es beide pathe und vergleichen Sie" Sie werden nicht eine nützliche Antwort auf die Methode schneller zu bekommen. Die variables beinhalten die Größe der beteiligten Tabellen, das normale Aktualisierungsmuster, die Geschwindigkeit der Festplatten im server, die Größe des memorys, die memorygröße, die dem Caching gewidmet ist. Diese list ist endlos und jede Variable beeinflusst, ob sie austriggers sind schneller als benutzerdefinierte SQL im SP.

Gut. Schnell. Billig. search dir zwei aus. Trigger sind gut in Bezug auf Integrität und wahrscheinlich billig in Bezug auf Wartung. Tatsächlich sind sie auch schnell, dass, sobald sie arbeiten, du mit ihnen fertig bist. SPs sind ein Wartungsproblem und drängen Zeug in Wartung kann schnell sein, aber ist nie gut oder billig.

Viel Glück.

Der empfohlene Ansatz hängt von Ihren Anforderungen ab. Wenn der History-Tisch für Audit Trail vorhanden ist , müssen Sie jede Operation erfassen. Wenn History-Tabelle nur aus Performance- Gründen, dann eine geplante SQL Agent datatransfer Job sollte genug sein

Um jede Operation zu erfassen, verwenden Sie entweder AFTER TRIGGER oder Change Data Capture.

Nach Auslösern versorgen Sie mit zwei Temp-Tischen, um mit dem Auslöser zu arbeiten:

  • INSERTED nach INSERT oder UPDATE
  • DELETED nach DELETE

Sie können Einfügungen aus der Historie-Tabelle aus diesen Temp-Tabellen ausführen und Ihre Verlaufstabelle wird immer aktuell sein. Möglicherweise möchten Sie Versionsnummern, timestempel oder beide in der Verlaufstabelle hinzufügen, um Änderungen an einer einzelnen Quellzeile zu trennen.

Change Data Capture (CDC) ist für die Erstellung einer Delta-Tabelle konzipiert, die Sie als Quelle zum Laden von data in ein Data Warehouse (oder eine History-Tabelle) verwenden können. Im Gegensatz zu Triggern ist CDC asynchronous und Sie können jede Methode und Scheduling für das Befüllen Ihres Ziels (Sprocs, SSIS) verwenden.

Sie können auf beide Originaldaten und Änderungen mit CDC zugreifen. Change Tracking (CT) erkennt nur geänderte Zeilen. Es ist möglich, einen kompletten Audit Trail mit CDC zu konstruieren, aber nicht mit CT. CDC und CT sind beide nur in den MSSQL 2008 Enterprise und Developer Editions verfügbar.

Verwenden Sie dazu Trigger. Dies bedeutet, dass alle Änderungen, unabhängig von der Quelle, in der Geschichte Tabelle reflektiert werden. Es ist gut für die security, widerstandsfähig zu Fehlermodi wie Menschen vergessen zu Code hinzufügen, um die Geschichte Tabelle und so weiter zu aktualisieren.

Es ist nicht wahrscheinlich, dass eine bestimmte Geschwindigkeitsdifferenz in entweder für diese Art von Operation als Ausführungszeit wird von der I / O dominiert werden.

Eine Frage, um sehr vorsichtig zu sein, ist, Ihre beabsichtigten Gebrauchsfälle für diese Tabelle zu identifizieren, und stellen Sie sicher, dass es zu diesem Zweck ordnungsgemäß konstruiert ist.

Genauer gesagt, wenn es für einen operativen Audit Trail für Stakeholder ist, ist das ganz anders als vor-und-nach-Schnappschüsse von Rekord-Änderungen in Tabellen. (In der Tat, ich habe eine schwierige time vorstellen eine gute Verwendung für Rekord-Änderungen, außer Debugging.)

Ein Audit Trail erfordert normalerweise mindestens eine Benutzer-ID, einen timestempel und einen Operationscode – und wahrscheinlich einige Details über die Operation. Beispiel – Ändern Sie die bestellte Menge auf einer Werbebuchung auf einer Bestellung.

Und für diese Art von Audit Trail wollen Sie nicht Trigger verwenden. Je höher in der BR-Schicht du die Generation dieser Events eingebettet hast, desto besser.

OTOH, für Rekord-Level-Änderungen, Trigger sind die richtige Übereinstimmung. Aber es ist auch oft einfacher, dies aus Ihren dbms Journaling-fileen zu bekommen.

Ich ziehe es vor, Trigger für Audit-Tabellen zu verwenden, da Trigger alle Updates erfassen, insert und löschen können, nicht nur die Updates, Einfügungen und Löschungen, die durch bestimmte gespeicherte Prozeduren aufgerufen werden:

 CREATE TRIGGER [dbo].[tr_Employee_rev] ON [dbo].[Employee] AFTER UPDATE, INSERT, DELETE AS BEGIN IF EXISTS(SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED) BEGIN INSERT INTO [EmployeeRev](EmployeeID,Firstname,Initial,Surname,Birthdate,operation, updated, updatedby) SELECT inserted.ID, inserted.Firstname,inserted.Initial,inserted.Surname,inserted.Birthdate,'u', GetDate(), SYSTEM_USER FROM INSERTED END IF EXISTS (SELECT * FROM INSERTED) AND NOT EXISTS(SELECT * FROM DELETED) BEGIN INSERT INTO [EmployeeRev](EmployeeID,Firstname,Initial,Surname,Birthdate,operation, updated, updatedby) SELECT inserted.ID, inserted.Firstname,inserted.Initial,inserted.Surname,inserted.Birthdate,'i', GetDate(), SYSTEM_USER FROM INSERTED END IF EXISTS(SELECT * FROM DELETED) AND NOT EXISTS(SELECT * FROM INSERTED) BEGIN INSERT INTO [EmployeeRev](EmployeeID,Firstname,Initial,Surname,Birthdate,operation, updated, updatedby) SELECT deleted.ID, deleted.Firstname,deleted.Initial,deleted.Surname,deleted.Birthdate,'d', GetDate(), SYSTEM_USER FROM DELETED END END 

Ich verwende SQLserver, um das SQL für die Revisionstabellen zu erzeugen, anstatt die Hand zu codieren. Dieser Code steht unter https://github.com/newdigate/sqlserver-revision-tables zur Verfügung

Löst aus. Im Moment können Sie vielleicht sagen, dass die einzige Art und Weise data aktualisiert wird, ist durch Ihre SPs, aber die Dinge können sich ändern, oder Sie müssen möglicherweise eine Masseneinlage / Update, dass mit den SPs wird zu schwerfällig für. Gehe mit Auslösern.

Es hängt von der Art der Anwendung und der Tabellenstruktur, der Anzahl der Indizes, der datagröße, usw., Fremdschlüssel usw. ab. Wenn es sich um relativ einfache Tabellen handelt (keine oder wenige Indizes wie Indizes für datetime / integer-Spalten) mit begrenzten data Set (<1 Million Zeilen), werden Sie wahrscheinlich in Ordnung, um Trigger verwenden.

Denken Sie daran, dass Auslöser die Quelle der Sperrprobleme sein können. Ich würde davon ausgehen, dass, wenn Sie die Geschichte Tabellen als eine Art von Audit Trail verwenden Sie werden Indizierung sie für zukünftige reference. Wenn der Trigger die Historien-Tabelle aktualisiert, die aufgrund der Indizes langsam einzufügen / aktualisieren / löschen soll, wird der Prozeduraufruf blockiert, bis der Trigger beendet ist. Auch wenn es irgendwelche Fremdschlüssel-Einschränkungen gibt, die im Trigger aktualisiert werden, könnte dies auch die performance beeinträchtigen.

In diesem Fall hängt alles von den Tabellenindizes ab. Wir verwenden Sql server 2000 für eine 24/7-App, die über 100K finanzielle transactions pro Tag verarbeitet. Die größte / Haupttabelle hat über 100Million Zeilen und 15 Indizes (Massenlöcher sind nicht möglich, wenn die Uptime gewünscht wird). Obwohl alle SQL in gespeicherten Prozeduren durchgeführt wird, verwenden wir keine Trigger oder Fremdschlüssel wegen des Performance-Hit.

Löst aus. Hier ist mein Ansatz:

  1. Erstellen Sie für jede kritische Tabelle eine Audit-Tabelle, die eine Audit-Testversion erfordert
  2. Audit-Tabelle enthält alle Spalten aus Quelltabelle + Spalten Audit-Record-Info wie wer, wann und die Aktion
  3. Trigger für UPDATE und DELETE nur, die INSERT-Operation hat die ursprüngliche Aufzeichnung in Quellentabelle selbst
  4. Vor dem Update oder Löschen kopieren Sie den ursprünglichen datasatz + Audit-Info in die Audit-Tabelle
  5. (Optional – nur für UPDATE 🙂 Um zu wissen, welche Spalte aktualisiert wurde, verwenden Sie entweder UPDATE (ColumnName) oder COLUMNS_UPDATED () in SQL-function, um die betroffenen Spalten zu ermitteln

Das Auditing auf diese Weise behält den aktuellen Status in der Quellentabelle und den gesamten Verlauf in der Audit-Tabelle und wird leicht durch die Schlüsselspalten identifiziert.