Click here to Skip to main content
15,885,366 members
Search within:


Articles / Database Development / SQL Server
 
Article

Audit Trail Generator for Microsoft SQL

Rate me:
Please Sign up or sign in to vote.
4.94/5 (55 votes)
27 Oct 20078 min read 325.3K   9.2K   134   68
Discussion of audit trail techniques and a script to automate audit trail implementation on a Microsoft SQL database
Screenshot - AuditTrailGenerator_ManagementStudio.jpg

Introduction

There are different reasons for having an audit trail on your data. Some companies have to do it because of legal obligations, such as Sarbanes-Oxley or FDA regulations. But I also find it very useful for debugging purposes. It shows you what was in your database at any point in time.

In this article I will explain the method I prefer for implementing an audit trail. Next, I will introduce a script to automate the implementation of the audit trail in the database.

Audit Trail With Shadow Table and Triggers

So how to implement an audit trail? Different visions exist. The one I prefer is to use a shadow table for each table that exists in the database. Every time a record is inserted, updated or deleted in a table, the record is also inserted in the corresponding shadow table. For inserting the record in the shadow table too, I use triggers on the original table that will fire whenever something happens. Other names you may encounter on the web for shadow tables are archive table or audit table.

Let's make this clear with a small example.

Screenshot - AuditTrailGenerator_diagram.gif

On the left side, you see the structure of a table called Users containing three columns. I refer to this table as the base table. On the right, you see the shadow table for this table. The shadow table contains all columns from the Users table, plus some extra columns:

  • AuditId: This the primary key of the shadow table. It is an identity field.
  • AuditAction: This is a one letter code to indicate the kind of operation. Values are I, U or D, for insert, update and delete respectively.
  • AuditDate: The date and time when the action occurred. The default value is set to getdate(), an SQL function that returns the current date and time.
  • AuditUser: The user who performed the action. The default value is set to suser_sname(), an SQL function that returns the user name of the user currently connected.
  • AuditApp: The application that was used. The default value is set to (('App=('+rtrim(isnull(app_name(),'')))+') '). This allows you to tell which application was used to modify the data, e.g. App=(Microsoft SQL Server Management Studio Express).

To fill up the shadow table, I define triggers on the Users table. We need three triggers: one for inserts, one for updates, and one for deletes. The code for the insert action is shown below. Those for updates and deletes are similar. If you are new to triggers, see Brief about Triggers in SQL Server 2000 by SquaredRomi.

SQL
CREATE TRIGGER tr_users_Insert ON dbo.users
FOR INSERT AS INSERT INTO users_shadow(UserId,FirstName,LastName,AuditAction)
SELECT UserId,FirstName,LastName,'I' FROM Inserted

The columns that are filled up by the trigger are only the data columns from the base table (userid, FirstName and LastName) and the AuditAction column. All other columns in the shadow table (AuditId, AuditDate, AuditUser and AuditApp) are filled up by their default value definition.

So what are the strengths and weaknesses of this approach? Let's start with the strengths:

  • It completely separates the current data from the audit trail. The old values are no longer in the base table but in the shadow table. There are no soft deletes, where deleted records are flagged as being deleted instead of being actually deleted.
  • It can easily be implemented on existing databases. If originally you did not foresee audit trailing, you can add it afterwards. The only thing you need to do is add the triggers on the base tables and create the shadow table. No changes have to be made to stored procedures or applications working with your database.
  • It always triggers. E.g. if you connect to your database through Enterprise Manager and you modify the data by hand, the triggers fire and the shadow table is updated accordingly.

The method also has some drawbacks:

  • The entire record is copied to the shadow table, including the columns that were not changed. In our example, if you change the firstname of a user in the base table, the lastname is also copied to the shadow table although it did not change. Hence, the shadow table will take up more space than strictly needed.
  • A trigger cannot be used on all column data types. Text, Ntext, and Image are not supported. The reason is that they are not stored in the record itself. The record only holds a pointer to the data. In SQL 2005, the timestamp is not supported either.
  • The number of tables doubles, although I personally don't find this an objection.
  • The audit trail is on a table level instead of on an action level. If during a single save operation in your application multiple tables in your database get updated, there is no link between the different transactions that took place on the different tables. The only thing that links them together is that they occurred at (almost) the same moment and by the same user.

The Audit Trail Generator Script

If you have 50 tables in your database, adding an audit trail using the method just described means adding another 50 tables and creating 150 triggers. This is why I have created the audit trail generator. It saves time and avoids typo errors. See the link on top of this article to download the code.

The audit trail generator is written as a stored procedure. Hence, you don't need any other tools. RichardRoe used codesmith as a generator, but this means you have to buy this tool first.

The stored procedure takes four arguments:

  • @TableName: The name of the table to which you want to add an audit trail, e.g. users
  • @Owner: The owner of the table. The default value is dbo
  • @AuditNameExtention: The extension you want for the shadow table name. E.g., if you set it to _shadow, the audit table for users will be called users_shadow. The default value is _shadow
  • @DropAuditTable: A bit to specify if the shadow table can be dropped. If 1, the existing audit table will be dropped and recreated. Of course, you lose all data in there. This is especially useful when you are still in development, but you may want to do this on a production system. The default value is 0.

The stored procedure will discover the columns in the original table by querying the system tables of SQL Server. These system tables are used by SQL Server itself to store the structure of the tables. The query to get all info about the table is shown below. For a complete overview of the system tables in SQL Server, see the Microsoft MSDN site.

SQL
SELECT b.name, c.name as TypeName, b.length, _
    b.isnullable, b.collation, b.xprec, b.xscale
FROM sysobjects a
inner join syscolumns b on a.id = b.id
inner join systypes c on b.xtype = c.xtype and c.name <> 'sysname'
WHERE a.id = object_id(N'[' + @Owner + '].[' + @TableName + ']')
and OBJECTPROPERTY(a.id, N'IsUserTable') = 1
ORDER BY b.colId

The image below shows the results if we launch this query for our Users table.

Screenshot - AuditTrailGenerator_QueryResult.gif

The remainder of the stored procedure loops over the result of this query using a cursor, and dynamically builds up the SQL statements in a string to create the shadow table and to add the triggers to the original table. These statements are then executed with the EXEC command. I will not go into the details of it, since it is straight forward.

Using the Script

The script is a stored procedure, so using it means calling the stored procedure. In its simplest form, you only need to set the @TableName parameter because for all other parameters, default values have been specified. The following statement can be launched from a query window.

SQL
EXECUTE GenerateAudittrail 'Users'

The following example shows what it looks like if all parameter values are specified.

SQL
EXECUTE GenerateAudittrail 'Users', 'dbo','_shadow', 0

The script is very handy to quickly create a shadow table for a given database table. However, it was not designed to modify a shadow table to reflect changes to the corresponding base table. In this case, it can only drop the shadow table, losing all records in it, and recreate it. Set the @DropAuditTable to 1 to force dropping and recreating the shadow table.

Related Articles

There are two articles I would like to mention that relate to this subject.

Nigel Rivett wrote an article describing an audit trail mechanism similar to the one described here, but where he tries to tackle some of the drawbacks I described. He uses one central audit trail table for all tables in the database, instead of a separate shadow table for each original table. Of course, it avoids the many shadow tables but he has to opt for varchars to store all values, regardless of what type they are in the original tables. But the most interesting point he makes is the logic that he builds into the triggers. The old values and the new values are compared and only the changed columns are stored in the audit trail. This can save storage space as the entire record is not duplicated. There is only one caveat. The time needed to modify data in a table increases since the trigger, which fires as a result of this modification, will take longer to complete since it contains much more logic compared to the simple triggers created by the audit trail generator presented here.

Ben Allfree criticizes the audit trail approach I took here in Record Versioning with SQL Server and proposes an alternative. Unfortunately, he does not mention triggers and moves audit trail logic into the code that accesses his database, such as stored procedures. Hence, it is not possible to add an audit trail to an existing database with the method he proposes, without changing the applications accessing that database. Another thing I dislike is the soft delete approach he takes, flagging old versions of records as inactive instead of actually deleting them. By moving older versions of records to a separate shadow table, I kept the original table clean and the number of records low, without impacting the speed of select statements.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Cedric Baelemans
Belgium Belgium
My name is Cedric and I live near Brussels, Belgium. Although I have an educational background in IT, I don't program for a living. My career has been more as an IT project leader and manager. But I want to stay up to date with the evolutions. Therefore, I dive into code after hours once in a while. Just to keep up with the programmers at work. You can read my blog at http://blog.baelemans.com


Comments and Discussions

 
First PrevNext
QuestionMsg 137, Level 15, State 2, Line 167 Must declare the scalar variable "@TableName". Pin
Member 162165896-Mar-24 0:16
Member 162165896-Mar-24 0:16 
AnswerRe: Msg 137, Level 15, State 2, Line 167 Must declare the scalar variable "@TableName". Pin
Member 162165896-Mar-24 0:24
Member 162165896-Mar-24 0:24 
QuestionHow to get updated fields name Pin
Member 138858652-Jul-18 18:35
Member 138858652-Jul-18 18:35 
QuestionOracle version? Pin
Member 1346405613-Oct-17 10:11
Member 1346405613-Oct-17 10:11 
Questionhow to save original row in shadow table Pin
as4511-May-17 3:40
as4511-May-17 3:40 
AnswerRe: how to save original row in shadow table Pin
Member 1158520012-Jun-17 2:39
Member 1158520012-Jun-17 2:39 
GeneralRe: how to save original row in shadow table Pin
as458-Jul-17 20:40
as458-Jul-17 20:40 
GeneralUltimate version Pin
Patrick Lavoie22-Nov-16 4:27
Patrick Lavoie22-Nov-16 4:27 
I took this script and merged all the fixes in these comments and others found. Support audit in another database too if you want. Support updating the audit tables if new columns are added.

SQL
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('Utility.GenerateAuditTrail') IS NULL
BEGIN
	PRINT('Create procedure Utility.GenerateAuditTrail');
	EXEC('CREATE PROCEDURE Utility.GenerateAuditTrail AS SET NOCOUNT ON;')
END
ELSE
	PRINT('Alter procedure Utility.GenerateAuditTrail');
GO
 
ALTER PROCEDURE Utility.GenerateAuditTrail
     @Owner VARCHAR(128)
    ,@TableName VARCHAR(128)
    ,@AuditNameExtension VARCHAR(128) = '_Audit'
    ,@DropAuditTable BIT = 0
    ,@AuditDatabaseName VARCHAR(128) = NULL
AS
BEGIN

    IF @AuditDatabaseName IS NULL
	   SET @AuditDatabaseName = (SELECT DB_NAME())
    ELSE
	   IF NOT EXISTS(SELECT name FROM sys.databases WHERE name = @AuditDatabaseName)
	   BEGIN
		  PRINT 'ERROR: Database''' + @AuditDatabaseName + ''' does not exist';
		  RETURN;  
	   END

    PRINT 'Current database : ' + @AuditDatabaseName;

    -- Check if table exists
    IF NOT EXISTS(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
	   PRINT 'ERROR: Table does not exist';
	   RETURN;
    END
    
    -- Check @AuditNameExtension
    IF @AuditNameExtension IS NULL
    BEGIN
	   PRINT 'ERROR: @AuditNameExtension cannot be null';
	   RETURN;
    END

    DECLARE @ExecuteInAuditDatabase NVARCHAR(MAX);
    SET @ExecuteInAuditDatabase = 'EXEC ' + @AuditDatabaseName + '..sp_executesql N''
    IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE [name] = ''''' + @Owner + ''''')
	   EXECUTE(N''''CREATE SCHEMA ' + @Owner + ';'''');''';
    EXEC sp_executesql @ExecuteInAuditDatabase;

    -- Drop audit table if it exists and drop should be forced
    SET @ExecuteInAuditDatabase = '
    IF (EXISTS(SELECT * FROM ' + @AuditDatabaseName + '.sys.sysobjects WHERE id = OBJECT_ID(N''[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'') AND OBJECTPROPERTY(id, N''IsUserTable'') = 1) AND ' + CAST(@DropAuditTable AS CHAR(1)) + ' = 1)
    BEGIN
	   PRINT ''Dropping audit table [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '];
	   DROP TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'';
    END';
    EXEC sp_executesql @ExecuteInAuditDatabase;
    
    -- Declare cursor to loop over columns
    DECLARE TableColumns CURSOR Read_Only
    FOR SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
	   FROM 
		  sys.sysobjects a 
		  INNER JOIN sys.syscolumns b on a.id = b.id 
		  INNER JOIN sys.systypes c on b.xusertype  = c.xusertype  and c.name <> 'sysname' 
	   WHERE 
		  a.id = OBJECT_ID(N'[' + @Owner + '].[' + @TableName + ']') 
		  AND OBJECTPROPERTY(a.id, N'IsUserTable') = 1 
	   ORDER BY 
		  b.colId;
    
    OPEN TableColumns;
    
    -- Declare temp variable to fetch records into
    DECLARE @ColumnName VARCHAR(128);
    DECLARE @ColumnType VARCHAR(128);
    DECLARE @ColumnLength SMALLINT;
    DECLARE @ColumnNullable INT;
    DECLARE @ColumnCollation SYSNAME;
    DECLARE @ColumnPrecision TINYINT;
    DECLARE @ColumnScale TINYINT;
    
    -- Declare variable to build statements
    DECLARE @CreateStatement VARCHAR(MAX);
    DECLARE @ListOfFields VARCHAR(MAX);
    SET @ListOfFields = '';    
    
    -- Check if audit table exists
    DECLARE @IsAuditTableExistsInAuditDatabase BIT;
    SET @ExecuteInAuditDatabase = 'EXEC ' + @AuditDatabaseName + '..sp_executesql N''' + '
    IF EXISTS(SELECT 1 FROM sys.sysobjects WHERE id = OBJECT_ID(N''''[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'''') AND OBJECTPROPERTY(id, N''''IsUserTable'''') = 1) SET @IsAuditTableExistsInAuditDatabase = 1; ELSE SET @IsAuditTableExistsInAuditDatabase = 0;'', N''@IsAuditTableExistsInAuditDatabase BIT OUTPUT'', @IsAuditTableExistsInAuditDatabase OUTPUT'
    EXEC sp_executesql @ExecuteInAuditDatabase, N'@IsAuditTableExistsInAuditDatabase BIT OUTPUT', @IsAuditTableExistsInAuditDatabase OUTPUT;
    
    IF @IsAuditTableExistsInAuditDatabase = 1
    BEGIN
	   -- AuditTable exists, update needed
	   PRINT 'Table already exists. Will update table schema with new fields.';
	   
	   DECLARE @NewFields VARCHAR(MAX) = '',
			 @ExistingFields VARCHAR(MAX)
    
	   SET @ExecuteInAuditDatabase = 'EXEC ' + @AuditDatabaseName + '..sp_executesql N''' + '
	   DECLARE @ExcludeColumnNames VARCHAR(MAX) = '''',AuditId,AuditAction,AuditDate,AuditUtcDate,AuditUser,AuditApp,AuditTransactionId,''''
	   SELECT 
		  @ExistingFields = COALESCE(@ExistingFields, '''''''') + '''','''' + sc.name + '''',''''
	   FROM
		  sysobjects so 
		  INNER JOIN syscolumns sc on so.id = sc.id 
	   WHERE 
		  so.id = OBJECT_ID(N''''[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'''') 
		  AND OBJECTPROPERTY(so.id, N''''IsUserTable'''') = 1 
		  AND CHARINDEX('''','''' + sc.name + '''','''', @ExcludeColumnNames) = 0
	   ORDER BY 
		  sc.colId'', N''@ExistingFields VARCHAR(MAX) OUTPUT'', @ExistingFields OUTPUT;';
	   EXEC sp_executesql @ExecuteInAuditDatabase, N'@ExistingFields VARCHAR(MAX) OUTPUT', @ExistingFields OUTPUT;
	   PRINT 'Existing fields : ' + ISNULL(@ExistingFields, '');

	   FETCH Next FROM TableColumns
	   INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale;
    	
	   WHILE @@FETCH_STATUS = 0
	   BEGIN
    		  IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
    		  BEGIN
    			 SET @ListOfFields = @ListOfFields + ',[' + @ColumnName + ']';
    		  END

		  IF (CHARINDEX(',' + @ColumnName + ',', @ExistingFields) = 0)
		  BEGIN
			 SET @NewFields = @NewFields + ',[' + @ColumnName + '] [' + @ColumnType + '] ';
    			
    			 IF @ColumnType in ('binary', 'char', 'varbinary', 'varchar')
			 BEGIN
				IF (@ColumnLength = -1)
				    Set @NewFields = @NewFields + '(MAX) ';
				ELSE
				    SET @NewFields = @NewFields + '(' + cast(@ColumnLength as varchar(10)) + ') ';
			 END
 
			 IF @ColumnType in ('nchar', 'nvarchar')
			 BEGIN
				IF (@ColumnLength = -1)
				    Set @NewFields = @NewFields + '(MAX) ';
				ELSE
				    SET @NewFields = @NewFields + '(' + cast((@ColumnLength / 2 ) as varchar(10)) + ') ';
			 END
    	
    			 IF @ColumnType in ('decimal', 'numeric')
    				SET @NewFields = @NewFields + '(' + cast(@ColumnPrecision as varchar(10)) + ',' + cast(@ColumnScale as varchar(10)) + ') ';
    	
    			 IF @ColumnType in ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
    				SET @NewFields = @NewFields + 'COLLATE ' + @ColumnCollation + ' ';
    	
			 -- Why put not nullable? Blocks some changes like new columns
    			 --IF @ColumnNullable = 0
    				--SET @NewFields = @NewFields + 'NOT '	;	
    	
    			 SET @NewFields = @NewFields + 'NULL'; 
		  END
    
    		  FETCH Next FROM TableColumns
    		  INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale;
    
	   END

	   IF LEN(@NewFields) > 0
	   BEGIN
		  SET @CreateStatement = 'ALTER TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '] ADD ' + SUBSTRING(@NewFields, 2, LEN(@NewFields));

		  PRINT 'Adding new Fields to audit table [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'
		  PRINT @CreateStatement
		  EXEC (@CreateStatement)
	   END
	   ELSE
		  PRINT 'No new fields to add to the audit table'
    END
    ELSE
    BEGIN
	   -- AuditTable does not exist, create new
    
	   -- Start of create table
	   SET @CreateStatement = 'CREATE TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '] (';
	   SET @CreateStatement = @CreateStatement + '[AuditId] [BIGINT] IDENTITY (1, 1) NOT NULL';
	   
	   -- Add audit trail columns
	   SET @CreateStatement = @CreateStatement + ',[AuditAction] [CHAR] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL';
	   SET @CreateStatement = @CreateStatement + ',[AuditDate] [DATETIME] NOT NULL';
	   SET @CreateStatement = @CreateStatement + ',[AuditUser] [VARCHAR] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL';
	   SET @CreateStatement = @CreateStatement + ',[AuditApp] [VARCHAR](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL' ;
	   SET @CreateStatement = @CreateStatement + ',[AuditTransactionId] [BIGINT] NOT NULL';
    
	   FETCH Next FROM TableColumns
	   INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale;
    	
	   WHILE @@FETCH_STATUS = 0
	   BEGIN
    		  IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
    		  BEGIN
    			 SET @ListOfFields = @ListOfFields + ',[' + @ColumnName + ']';
    	
    			 SET @CreateStatement = @CreateStatement + ',[' + @ColumnName + '] [' + @ColumnType + '] ';
    			
    			 IF @ColumnType in ('binary', 'char', 'varbinary', 'varchar')
			 BEGIN
				IF (@ColumnLength = -1)
				    Set @CreateStatement = @CreateStatement + '(MAX) ';
				ELSE
				    SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnLength as varchar(10)) + ') ';
			 END
 
			 IF @ColumnType in ('nchar', 'nvarchar')
			 BEGIN
				IF (@ColumnLength = -1)
				    Set @CreateStatement = @CreateStatement + '(MAX) ';
				ELSE
				    SET @CreateStatement = @CreateStatement + '(' + cast((@ColumnLength / 2 ) as varchar(10)) + ') ';
			 END
    	
    			 IF @ColumnType in ('decimal', 'numeric')
    				SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnPrecision as varchar(10)) + ',' + cast(@ColumnScale as varchar(10)) + ') ';
    	
    			 IF @ColumnType in ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
    				SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' ';
    	
			 -- Why put not nullable? Blocks some changes like new columns
    			 --IF @ColumnNullable = 0
    				--SET @CreateStatement = @CreateStatement + 'NOT '	;	
    	
    			 SET @CreateStatement = @CreateStatement + 'NULL'; 	
    		  END
    
    		  FETCH Next FROM TableColumns
    		  INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale;
	   END
    
	   SET @CreateStatement = @CreateStatement + ')';
	    	
	   -- Create audit table
	   PRINT 'Creating audit table [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']';
	   PRINT @CreateStatement;
	   EXEC (@CreateStatement);
    
	   -- Set primary key and default values
	   SET @CreateStatement = 'ALTER TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '] ADD ';
	   SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditDate] DEFAULT (GETDATE()) FOR [AuditDate]';
	   SET @CreateStatement = @CreateStatement + ',CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditUser] DEFAULT (SUSER_SNAME()) FOR [AuditUser]';
	   SET @CreateStatement = @CreateStatement + ',CONSTRAINT [PK_' + @TableName + @AuditNameExtension + '] PRIMARY KEY  CLUSTERED ([AuditId])  ON [PRIMARY]';
	   SET @CreateStatement = @CreateStatement + ',CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditApp]  DEFAULT (''App=('' + RTRIM(ISNULL(APP_NAME(),'''')) + '') '') for [AuditApp]';
	   SET @CreateStatement = @CreateStatement + ',CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditTransactionId] DEFAULT (0) FOR [AuditTransactionId]';
    
	   PRINT 'Setting primary key and default values'
	   PRINT @CreateStatement;
	   EXEC (@CreateStatement);
    
    END
    
    CLOSE TableColumns;
    DEALLOCATE TableColumns;
    
    /* Drop Triggers, if they exist */
    PRINT 'Dropping triggers'
    IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[TR_Audit_' + @TableName + '_Insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1) 
	   EXEC ('DROP TRIGGER [' + @Owner + '].[TR_Audit_' + @TableName + '_Insert]');
    
    IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[TR_Audit_' + @TableName + '_Update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1) 
	   EXEC ('DROP TRIGGER [' + @Owner + '].[TR_Audit_' + @TableName + '_Update]');
    
    IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[TR_Audit_' + @TableName + '_Delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1) 
	   EXEC ('DROP TRIGGER [' + @Owner + '].[TR_Audit_' + @TableName + '_Delete]');
    
    /* Create triggers */
    PRINT 'Creating triggers';
    EXEC ('CREATE TRIGGER TR_Audit_' + @TableName + '_Insert ON ' + @Owner + '.' + @TableName + ' FOR INSERT AS BEGIN /*DECLARE @TransactionId BIGINT; SELECT @TransactionId = transaction_id FROM sys.dm_tran_current_transaction;*/ INSERT INTO [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](AuditAction/*, AuditTransactionId*/' +  @ListOfFields + ') SELECT ''I''/*, @TransactionId*/' + @ListOfFields + ' FROM Inserted; END');
    EXEC ('CREATE TRIGGER TR_Audit_' + @TableName + '_Update ON ' + @Owner + '.' + @TableName + ' FOR UPDATE AS BEGIN /*DECLARE @TransactionId BIGINT; SELECT @TransactionId = transaction_id FROM sys.dm_tran_current_transaction;*/ INSERT INTO [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](AuditAction/*, AuditTransactionId*/' +  @ListOfFields + ') SELECT ''U''/*, @TransactionId*/' + @ListOfFields + ' FROM Inserted; END');
    EXEC ('CREATE TRIGGER TR_Audit_' + @TableName + '_Delete ON ' + @Owner + '.' + @TableName + ' FOR DELETE AS BEGIN /*DECLARE @TransactionId BIGINT; SELECT @TransactionId = transaction_id FROM sys.dm_tran_current_transaction;*/ INSERT INTO [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](AuditAction/*, AuditTransactionId*/' +  @ListOfFields + ') SELECT ''D''/*, @TransactionId*/' + @ListOfFields + ' FROM Deleted; END');

END
GO



Here a script to create/update all audit tables :

SQL
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('Utility.UpdateAllAuditTrails') IS NULL
BEGIN
	PRINT('Create procedure Utility.UpdateAllAuditTrails');
	EXEC('CREATE PROCEDURE Utility.UpdateAllAuditTrails AS SET NOCOUNT ON;')
END
ELSE
	PRINT('Alter procedure Utility.UpdateAllAuditTrails');
GO
 
ALTER PROCEDURE Utility.UpdateAllAuditTrails
     @RecreateAuditTables BIT = 0
AS
BEGIN

    DECLARE @output NVARCHAR(MAX) = N'';

    ;WITH
    Tables AS 
    (
	   SELECT
		   s.name AS [Schema]
		  ,t.name AS [Table]
	   FROM
		  sys.tables t
		  INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
	   WHERE
		  CHARINDEX(',' + s.name + ',', ',IgnoreSchema1,
								   ,IgnoreSchema2,
								   ,IgnoreSchema3,
								   ') = 0
		  AND CHARINDEX(',' + t.name + ',', ',IgnoreTable1,
									  ,IgnoreTable2,									  
									  ,IgnoreTable3,
									  ') = 0
		  AND t.name NOT LIKE '%Logs'
		  AND t.name NOT LIKE '%_Audit'
	   --ORDER BY
		  --s.name, t.name
    )

    SELECT 
	   @output += CHAR(13) + CHAR(10) + 'EXEC Utility.GenerateAuditTrail ''' + t.[Schema] + ''', ''' + t.[Table] + ''', @DropAuditTable=' + CAST(@RecreateAuditTables AS CHAR(1)) + ', @AuditDatabaseName=''' + (SELECT DB_NAME() + '_Audit') + ''';'
    FROM
	   Tables t
    ORDER BY  t.[Schema]
		   ,t.[Table]

    PRINT 'Output : ' + @output;
    EXEC sp_executesql @output;

END
GO
BugDon't use GetDate() - your audit trail will be dangerously wrong Pin
pt140115-Sep-16 11:35
pt140115-Sep-16 11:35 
QuestionSuggestion for changes in 'GenerateAudittrail' Stored Procedure Pin
VISHAL11098815-Sep-16 8:03
VISHAL11098815-Sep-16 8:03 
SuggestionAdditional fix for geography/hierarchyid/geometry types Pin
midix28-Aug-16 23:15
midix28-Aug-16 23:15 
QuestionGreat Job! One modification to update table schema if source table change Pin
miguelgalindo18-May-16 5:09
miguelgalindo18-May-16 5:09 
AnswerRe: Great Job! One modification to update table schema if source table change Pin
Patrick Lavoie14-Nov-16 11:16
Patrick Lavoie14-Nov-16 11:16 
QuestionGet hostname of client Pin
Vandrer12-Oct-15 4:49
Vandrer12-Oct-15 4:49 
QuestionParameter exceeds the maximum allowed? Pin
Member 1187227631-Jul-15 0:44
Member 1187227631-Jul-15 0:44 
QuestionError Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows) Pin
Member 174684525-Nov-14 4:20
Member 174684525-Nov-14 4:20 
AnswerRe: Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows) Pin
Mohann201517-Mar-15 21:32
Mohann201517-Mar-15 21:32 
QuestionRe: Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows) Pin
Member 1598120417-Apr-23 6:07
Member 1598120417-Apr-23 6:07 
QuestionExcellent Code Sample Pin
lylehardin11-Feb-14 8:44
lylehardin11-Feb-14 8:44 
QuestionExellent Job Pin
Marc-Olivier29-Dec-13 23:54
Marc-Olivier29-Dec-13 23:54 
GeneralMy vote of 5 Pin
Snorri Kristjansson29-Jan-13 23:27
professionalSnorri Kristjansson29-Jan-13 23:27 
GeneralMy vote of 5 Pin
nasserbr3-Oct-12 23:09
nasserbr3-Oct-12 23:09 
Questionchar & varchar vs. ncahr & nvarchar Pin
nasserbr3-Oct-12 23:08
nasserbr3-Oct-12 23:08 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 5:50
professionalKanasz Robert24-Sep-12 5:50 
GeneralPrevious post of modified code Pin
Adam Menkes12-Jul-12 11:57
Adam Menkes12-Jul-12 11:57 
Last Visit: 31-Dec-99 18:00     Last Update: 24-Apr-24 19:33Refresh123 Next ᐅ

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink
Advertise
Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Posted 27 Oct 2007

Article Copyright 2007 by Cedric Baelemans
Everything else Copyright © CodeProject, 1999-2024

Web02 2.8:2024-04-02:1