Create an audit table on the fly


The following is a script that generates an audit table for a provided table name.

It will:
– Add 5 audit columns to an existing table (DataVersion, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy)
– Create 2 triggers on the production table, one for updates and one for deletes (inserts do not need audit info)
– Create an audit table with similar design as the production table
– Create a primary key index on the audit table matching that of the production table but with the DataVersion also added as a part of it

Prerequisites:
– The stored procedure “sp_ScriptTableForAudit” must be present in the database (provided here)
– The script must be run in the database that the production table resides.
– The script does not handle table names with other schemas than dbo
– The table must have a primary key

Please add feedback/bug reports etc as comments and I will try to fix them right away

/*
Script created by Henning Frettem, aka Lumbago @ SQLTeam, 2010-03-23
BE CAREFUL, THIS SCRIPT WILL MAKE CHANGES TO YOUR TABLES!!
*/

SET NOCOUNT ON

BEGIN TRY

	DECLARE @TableName varchar(100); SET @TableName = 'myTable'
	DECLARE @AuditTableName varchar(200)

	--> Checking prerequisites
	IF NOT EXISTS (
			SELECT 1
			FROM sys.index_columns sc
			  JOIN sys.indexes i
				ON sc.object_id = i.object_id
				  AND sc.index_id = i.index_id
			WHERE OBJECT_NAME(sc.object_id) = @TableName 
			  AND i.is_primary_key = 1)
		RAISERROR('There is no primary key on the table', 16, 1)

	IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_ScriptTableForAudit')
		RAISERROR('The stored procedure sp_ScriptTableForAudit is not present in the database', 16, 1)

	SET @AuditTableName = @TableName + '_audit'

	DECLARE @SQL nvarchar(max); SET @SQL = ''

	--> Add audit columns to the production table if they don't exist already
	IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'DataVersion')
	  SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD DataVersion int NOT NULL DEFAULT 1' + CHAR(13)

	IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'CreatedDate')
	  SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD CreatedDate datetime NOT NULL DEFAULT GETDATE()' + CHAR(13)

	IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'CreatedBy')
	  SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD CreatedBy nvarchar(200) NOT NULL DEFAULT SUSER_NAME()' + CHAR(13)

	IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'ModifiedDate')
	  SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD ModifiedDate datetime NOT NULL DEFAULT GETDATE()' + CHAR(13)

	IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'ModifiedBy')
	  SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD ModifiedBy nvarchar(200) NOT NULL DEFAULT SUSER_NAME()' + CHAR(13)

	IF LEN(@SQL) > 0
	  BEGIN
		PRINT @SQL
		EXEC sp_executesql @SQL
		SET @SQL = ''
	  END

	--> Create the audit table with the same definition as the production table, but add the IsDeleted column
	IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @AuditTableName)
	  BEGIN
		DECLARE @AuditTableDefinition table (
		  RowNumber int IDENTITY(1, 1),
		  DefinitionRow varchar(max)
		  )

		INSERT INTO @AuditTableDefinition
		EXEC sp_ScriptTableForAudit @Tablename = @TableName, @NewTablename = @AuditTableName

		--> Need IsDeleted column in audit table
		UPDATE @AuditTableDefinition
		  SET DefinitionRow = ', IsDeleted bit NOT NULL DEFAULT(0) )'
		WHERE RowNumber = (SELECT MAX(RowNumber) FROM @AuditTableDefinition)

		SELECT @SQL = @SQL + DefinitionRow FROM @AuditTableDefinition

		--> Run the create table script in the database
		PRINT @SQL
		EXEC sp_executesql @SQL
		SET @SQL = ''
	  END

	--> Create Update trigger
	IF NOT EXISTS (SELECT 1 FROM sys.triggers WHERE name = 'trg' + REPLACE(@TableName, '.', '') + 'Update')
	  BEGIN
		SET @SQL = @SQL + '
		  CREATE TRIGGER [trg' + REPLACE(@TableName, '.', '') + 'Update] ON ' + @TableName + '
		  FOR UPDATE
		  AS

		  SET NOCOUNT ON

		  UPDATE a SET
			a.DataVersion = b.DataVersion + 1,
			a.ModifiedDate = GETDATE(),
			a.ModifiedBy = SYSTEM_USER
		  FROM ' + @TableName + ' a
			INNER JOIN inserted b
			  ON '

		SELECT @SQL = @SQL + 'a.' + c.name + ' = b.' + c.name + ' AND '
		FROM sys.index_columns sc
		  JOIN sys.columns c
			ON sc.object_id = c.object_id
			  AND sc.column_id = c.column_id
		  JOIN sys.indexes i
			ON sc.object_id = i.object_id
			  AND sc.index_id = i.index_id
		WHERE OBJECT_NAME(sc.object_id) = @TableName
		  AND i.is_primary_key = 1
		ORDER BY index_column_id ASC

		SET @SQL = LEFT(@SQL, LEN(@SQL) - 4) + CHAR(13) + CHAR(13)

		SET @SQL = @SQL + '  INSERT INTO ' + @AuditTableName + '
			('

		SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @AuditTableName

		SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
		SET @SQL = @SQL + ')
		  SELECT '

		SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @AuditTableName

		SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
		SET @SQL = @SQL + ' = 0
		  FROM deleted

		  GO
		  '

		PRINT @SQL
		EXEC sp_executesql @SQL
		SET @SQL = ''
	  END

	--> Create delete trigger
	IF NOT EXISTS (SELECT 1 FROM sys.triggers WHERE name = 'trg' + REPLACE(@TableName, '.', '') + 'Delete')
	  BEGIN
		SET @SQL = @SQL + '
		  CREATE TRIGGER [trg' + REPLACE(@TableName, '.', '') + 'Delete] ON ' + @TableName + '
		  FOR DELETE
		  AS

		  SET NOCOUNT ON

		  INSERT INTO ' + @AuditTableName + '
			('

		SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @AuditTableName

		SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
		SET @SQL = @SQL + ')
		  SELECT '

		SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @AuditTableName

		SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
		SET @SQL = @SQL + ' = 1
		  FROM deleted

		  GO'

		PRINT @SQL
		EXEC sp_executesql @SQL
		SET @SQL = ''
	  END
END TRY
BEGIN CATCH
    PRINT Error_Message()
END CATCH
Advertisements

18 thoughts on “Create an audit table on the fly

  1. Hi, I am using SQL server 2005. I have created a table with name ‘tblTest’ and tried to execute the above code but it gives error. can you please provide me error free code.

    Thank you.

    Below is the error message:-

    Msg 139, Level 15, State 1, Line 0
    Cannot assign a default value to a local variable.
    Msg 137, Level 15, State 2, Line 12
    Must declare the scalar variable “@TableName”.
    Msg 139, Level 15, State 1, Line 0
    Cannot assign a default value to a local variable.
    Msg 137, Level 15, State 2, Line 17
    Must declare the scalar variable “@Tablename”.
    Msg 137, Level 15, State 2, Line 18
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 20
    Must declare the scalar variable “@Tablename”.
    Msg 137, Level 15, State 2, Line 21
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 23
    Must declare the scalar variable “@Tablename”.
    Msg 137, Level 15, State 2, Line 24
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 26
    Must declare the scalar variable “@Tablename”.
    Msg 137, Level 15, State 2, Line 27
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 29
    Must declare the scalar variable “@Tablename”.
    Msg 137, Level 15, State 2, Line 30
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 32
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 34
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 35
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 1, Line 36
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 40
    Must declare the scalar variable “@AuditTableName”.
    Msg 137, Level 15, State 2, Line 48
    Must declare the scalar variable “@TableName”.
    Msg 137, Level 15, State 2, Line 55
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 58
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 59
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 1, Line 60
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 64
    Must declare the scalar variable “@TableName”.
    Msg 137, Level 15, State 2, Line 66
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 81
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 93
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 95
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 98
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 102
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 103
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 106
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 110
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 111
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 117
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 118
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 1, Line 119
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 123
    Must declare the scalar variable “@TableName”.
    Msg 137, Level 15, State 2, Line 125
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 135
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 139
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 140
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 143
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 147
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 148
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 153
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 2, Line 154
    Must declare the scalar variable “@SQL”.
    Msg 137, Level 15, State 1, Line 155
    Must declare the scalar variable “@SQL”.

    • My bad…sorry about that. Declaring variables with a default value is a 2008 feature. I have changed the script to work for 2005 as well.

  2. Thank you so much for your quick reply.

    But I am still getting error.. please help me out.

    ALTER TABLE tblTest ADD DataVersion int NOT NULL DEFAULT 1
    ALTER TABLE tblTest ADD CreatedDate datetime NOT NULL DEFAULT GETDATE()
    ALTER TABLE tblTest ADD CreatedBy nvarchar(200) NOT NULL DEFAULT SUSER_NAME()
    ALTER TABLE tblTest ADD ModifiedDate datetime NOT NULL DEFAULT GETDATE()
    ALTER TABLE tblTest ADD ModifiedBy nvarchar(200) NOT NULL DEFAULT SUSER_NAME()

    CREATE TABLE tblTest_audit(
    EnpID INT NOT NULL ,
    EmpCode NVARCHAR(50) NOT NULL ,
    EmpName NVARCHAR(50) NOT NULL ,
    DataVersion INT NOT NULL ,
    CreatedDate DATETIME NOT NULL ,
    CreatedBy NVARCHAR(200) NOT NULL ,
    ModifiedDate DATETIME NOT NULL ,
    ModifiedBy NVARCHAR(200) NOT NULL , IsDeleted bit NOT NULL DEFAULT(0) )

    CREATE TRIGGER [trgtblTestUpdate] ON tblTest
    FOR UPDATE
    AS

    SET NOCOUNT ON

    UPDATE a SET
    a.DataVersion = b.DataVersion + 1,
    a.ModifiedDate = GETDATE(),
    a.ModifiedBy = SYSTEM_USER
    FROM tblTest a
    INNER JOIN inserted b

    INSERT INTO tblTest_audit
    ([EnpID], [EmpCode], [EmpName], [DataVersion], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [IsDeleted],)
    SELECT [EnpID], [EmpCode], [EmpName], [DataVersion], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [IsDeleted], = 0
    FROM deleted

    GO

    Msg 156, Level 15, State 1, Procedure trgtblTestUpdate, Line 14
    Incorrect syntax near the keyword ‘INSERT’.
    Msg 102, Level 15, State 1, Procedure trgtblTestUpdate, Line 15
    Incorrect syntax near ‘)’.
    Msg 102, Level 15, State 1, Procedure trgtblTestUpdate, Line 16
    Incorrect syntax near ‘=’.

    CREATE TRIGGER [trgtblTestDelete] ON tblTest
    FOR DELETE
    AS

    SET NOCOUNT ON

    INSERT INTO tblTest_audit
    ([EnpID], [EmpCode], [EmpName], [DataVersion], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [IsDeleted],)
    SELECT [EnpID], [EmpCode], [EmpName], [DataVersion], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [IsDeleted], = 1
    FROM deleted

    GO
    Msg 102, Level 15, State 1, Procedure trgtblTestDelete, Line 9
    Incorrect syntax near ‘)’.
    Msg 102, Level 15, State 1, Procedure trgtblTestDelete, Line 10
    Incorrect syntax near ‘=’.

  3. Hay Henning … plz help me out on the issues… i think a block of code which is generating update trigger code is not returning any lines. plz refer above lines of code.

      • Hi Henning,

        PK is there then also its not working. Error i am getting is :

        “There is no primary key on the table”

  4. I have changed the @TableName = ‘myTable’ and (sc.object_id) = ‘EmployeeDemo’

    now it works fine. but how would i find the trigger in my database.

    • I know I may be wasting your valuable time but i was waiting since yesterday for your reply. need your help on above topic.

  5. Hi Henning,

    I have made some slightest change in your code and it worked fine.

    Below is Your Working code… Thanx a lot and plz keep posting such a informative, useful and nice article.

    (script removed by thefirstsql.com, post updated instead)

    • Thank you Nash for pointing this out! I removed the code from your comment and updated the script instead…this comments thread was getting pretty long :)

  6. Hi Henning,

    Thank you so much for your support. You can delete all comment made by me as it increases the length of page. anyway one more thing i wanna ask you.. can we compare two rows (each from main table and audit table) and show only the changed records data and column from the audit table?

    E.g.
    table1
    EmpName -‘Jack’
    Department – ‘Sales’
    Designation – ‘Jr. Clerk’

    table1_audit

    EmpName – ‘Jackson’
    Department – ‘Sales’
    Designation – ‘Sr. Clerk’

    I wanna show the records as (Only the records which has been changed)

    EmpName – ‘Jackson’
    Designation – ‘Sr. Clerk’

    • Well…the presentation layer can do a far better (and more effective) job than the database in cases like this. You can i.e. write a query like this:

      select *
      from table1
      union all
      select *
      from table1_audit
      order by id, dataversion desc

      …and then let the presentation layer hide the columns that are equal.

      • Hi, That’s rite.. I can handle this in Presentation layer but If i could do this in Database layer then I don’t have to code further.

        I will just pass the data table to data grid and show the difference in data which has been changed..

        I think the topic is goin interesting, I wanna do it in SQL. Is it possible?

  7. Hi, do you have a sample of querying the audit tables for how the main table looked on a certain day?

    • The audit tables are not optimized for this purpose, but I created a script that will do what you want. This is using a table called “ExampleTable2” and a PK-column called ExampleColumn:

      DECLARE @PointInTime datetime = '2012-05-29 09:00:00.000'

      ;WITH cte AS (
      SELECT *, IsDeleted = 0, ValidFrom = ModifiedDate, ValidTo = '9999-12-31'
      FROM ExampleTable2
      UNION ALL
      SELECT a.*, ValidFrom = a.ModifiedDate, ValidTo = b.ModifiedDate
      FROM ExampleTable2_audit a
      INNER JOIN (
      SELECT *, IsDeleted = 0 from ExampleTable2
      UNION ALL
      SELECT * FROM ExampleTable2_audit
      ) AS b
      ON a.ExampleColumn = b.ExampleColumn
      AND a.DataVersion = b.DataVersion - 1
      )
      SELECT *
      FROM cte
      WHERE @PointInTime BETWEEN ValidFrom AND ValidTo

      • Thank you for all your help. I’ve included the row creation date as part of each audit table’s index so hopefully performance should not be an issue.
        Once again, thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s