Implementing generic audit trail trigger in sql server

How do you write a generic audit trail trigger in SQL Server? You want to keep track of inserts, deletes and updates of all of the tables in your database.

First of all we create our audit table will have a generic structure to store any kind of audited value.

1
2
3
4
5
6
7
8
9
10
CREATE TABLE [dbo].[Audit](
	[TYPE] [CHAR](1) NULL,
	[TableName] [VARCHAR](128) NULL,
	[PK] [VARCHAR](1000) NULL,
	[FieldName] [VARCHAR](128) NULL,
	[OldValue] [VARCHAR](1000) NULL,
	[NewValue] [VARCHAR](1000) NULL,
	[UpdateDate] [datetime] NULL,
	[UserName] [VARCHAR](128) NULL
) ON [PRIMARY]

and then, we create new trigger in the any tables which we want to audit.

dont forget the replace ‘YourTableName’ text in the trigger script to your real table name.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
CREATE TRIGGER [dbo].[xxx] ON [dbo].[yyy] 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) ,
	@PKSelect VARCHAR(1000)
 
	SELECT @TableName = 'YourTableName'
 
	-- 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 select for insert
	SELECT @PKSelect = COALESCE(@PKSelect+'+','') + '''<' + COLUMN_NAME + '=''+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 @FIELD = 0, @maxfield = MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
	while @FIELD < @maxfield
	BEGIN
		SELECT @FIELD = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @FIELD
		SELECT @bit = (@FIELD - 1 )% 8 + 1
		SELECT @bit = POWER(2,@bit - 1)
		SELECT @CHAR = ((@FIELD - 1) / 8) + 1
		IF SUBSTRING(COLUMNS_UPDATED(),@CHAR, 1) & @bit > 0 OR @TYPE IN ('I','D')
		BEGIN
			SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION = @FIELD
			SELECT @SQL = 		'insert Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)'
			SELECT @SQL = @SQL + 	' select ''' + @TYPE + ''''
			SELECT @SQL = @SQL + 	',''' + @TableName + ''''
			SELECT @SQL = @SQL + 	',' + @PKSelect
			SELECT @SQL = @SQL + 	',''' + @fieldname + ''''
			SELECT @SQL = @SQL + 	',convert(varchar(1000),d.' + @fieldname + ')'
			SELECT @SQL = @SQL + 	',convert(varchar(1000),i.' + @fieldname + ')'
			SELECT @SQL = @SQL + 	',''' + @UpdateDate + ''''
			SELECT @SQL = @SQL + 	',''' + @UserName + ''''
			SELECT @SQL = @SQL + 	' from #ins i full outer join #del d'
			SELECT @SQL = @SQL + 	@PKCols
			SELECT @SQL = @SQL + 	' where i.' + @fieldname + ' <> d.' + @fieldname 
			SELECT @SQL = @SQL + 	' or (i.' + @fieldname + ' is null and  d.' + @fieldname + ' is not null)' 
			SELECT @SQL = @SQL + 	' or (i.' + @fieldname + ' is not null and  d.' + @fieldname + ' is null)' 
			EXEC (@SQL)
		END
	END


Views (23150)

Tags: , , , , ,

15 Comments to "Implementing generic audit trail trigger in sql server"

  1. baidu883 says:

    very good news,your are good boy!

  2. Naveed says:

    by the way. sql server has its on logging that has all this information..:)

  3. Bijoy Jacob says:

    HI…Good post..

    However, I want to show the stored records from trigger as a single. But the trigger saves each column as a row. Is there a way to do it?

    How will we make this more presentable?

  4. Jan says:

    I’ve implemented it to our database, and it runs without problems on the database. However my php application gets an error when I try to insert a record. The error I’ve got back is:
    Incorrect syntax near the keyword ‘and’. (severity 15)
    Any ideas?

  5. Sushant says:

    Hi,
    does this support tables with multicolumn PKs?
    Thanks

  6. Heather says:

    I blog as well and I’m authoring something comparable to this excellent posting, “Implementing generic audit
    trail trigger in sql server | Soft Code Article”.

    Would you mind in the event I personallywork with some of your own
    tips? Many thanks ,Nila

  7. Andrew says:

    Question, My table has a PK but it’s telling me it doesn’t, does it have to be a specific datatype or name? I know this post is older but hopefully someone is watching and or responding

  8. Chris Diesel says:

    Bug – Not all updates are logged. Can’t use the field’s ORDINAL_POSITION with COLUMNS_UPDATED() function per Microsoft. Use the ColumnId instead – COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + ‘.’ + TABLE_NAME), COLUMN_NAME, ‘ColumnID’)

  9. Dmitry says:

    @Sushant: This solution does support composite PKs (in case you did not get your answer by now).
    The table Audit has only one field named PK because the primary key of the table being audited is saved as a string:

  10. Raj says:

    Please confirm using this trigger will affect the performance of the tables?
    Since while loop is involved, more the columns will have impact in performance
    Kindly confirm

  11. wannabeDBA says:

    Is there a way to narrow the auditing to a particular user or group ?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

SoftCodeArticle was formed to help developers solve common software issues.