Auto generate SQL Server UPDATE triggers for data auditing

By:   |   Updated: 2009-06-11   |   Comments (5)   |   Related: More > Scripts

You have a new requirement that all data changes to your tables need to be audited, so the old value is stored whenever a change is made.  One way of doing this is to create audit tables for each of the tables that you need to audit and write to these audit tables when data in the main table has changed. These triggers can be created manually one by one, but in this tip I show you how you can automate the creation of the triggers for each of the tables you need to audit by using a script to generate scripts.

In this tip we will go over creating UPDATE triggers for tables that need to be audited.

So what are triggers?
Here is a definition from SQL Server Books Online.  A trigger is a special type of stored procedure that automatically runs when a language event executes. SQL Server includes two general types of triggers: data manipulation language (DML) and data definition language (DDL) triggers. DML triggers can be used when INSERT, UPDATE, or DELETE statements modify data in a specified table or view. DDL triggers fire stored procedures in response to a variety of DDL statements, which are primarily statements that begin with CREATE, ALTER, and DROP. DDL triggers can be used for administrative tasks, such as auditing and regulating database operations.

This scripts below generate an UPDATE trigger where you have to insert the updated row into another table like an audit table only if at least one of the columns was changed.

Lets put the script generator to work. create these two sample tables.

IF OBJECT_ID('dbo.EmployeeTriggerTest'IS NOT NULL
DROP TABLE dbo.EmployeeTriggerTest
CREATE TABLE dbo.EmployeeTriggerTest(
EmployeeID INT
,EmployeeName VARCHAR(100)
Department VARCHAR(50)
CONSTRAINT PKEmployeeTriggerTest 

IF OBJECT_ID('dbo.EmployeeTriggerTest_Audit'IS NOT NULL
DROP TABLE dbo.EmployeeTriggerTest_Audit
CREATE TABLE dbo.EmployeeTriggerTest_Audit(
EmployeeID INT
,EmployeeName VARCHAR(100)
Department VARCHAR(50)
ModifiedDate smalldatetime 
CONSTRAINT DF_EmployeeTriggerTest_Audit_ModifiedDate DEFAULT GETDATE()
ModifiedBy        NVARCHAR(256CONSTRAINT DF_EmployeeTriggerTest_Audit_ModifiedBy DEFAULT SUSER_SNAME()

INSERT INTO dbo.EmployeeTriggerTest (EmployeeIDEmployeeNameDepartment)
VALUES (1,'John Smith','Sales')

INSERT INTO dbo.EmployeeTriggerTest (EmployeeIDEmployeeNameDepartment)
VALUES (2,'Ram Kumar','Finance')

INSERT INTO dbo.EmployeeTriggerTest (EmployeeIDEmployeeNameDepartment)
VALUES (3,'John Doe','IT')

SELECT FROM dbo.EmployeeTriggerTest

The update trigger script generator code is below.

Assign values like this: @tablename = 'EmployeeTriggerTest' and @audittable = 'EmployeeTriggerTest_Audit' and execute the script.

This script generates a update trigger where you have to insert the updated row in another table like an audit table only if any one of the column was changed. 
The reason I had to have multiple varchar(max) variables is that the results window will not print more than 8192 characters.

DECLARE @tablename VARCHAR(100)
DECLARE @audittable VARCHAR(100)
DECLARE @quote CHAR(1)
SET @quote = CHAR(39)

SET @tablename 'Trigger Table Name'        --Replace this with the table name for which you want to write the update trigger
SET @audittable 'Audit Table Name'        --Replace this with the audit table you want to insert the changed data

--this is just the header info  for the trigger
SET @sqlHeader 'IF OBJECT_ID('+@quote+''+@tablename+'_U'+@quote+') IS NOT NULL
       DROP TRIGGER dbo.'

ON dbo.'
+@tablename+' FOR update
* Update trigger for '
* 01/01/2000 xxx New
AS '
PRINT @sqlHeader

--select insert into
SELECT @sqlInsert COALESCE(@sqlInsert+' ,' '') + name CHAR(13)+ CHAR(9)  FROM sys.syscolumns WHERE OBJECT_NAME(id@tablename ORDER BY colid
SET @sqlInsert 'insert into dbo.'+@audittable+'('+CHAR(13) +CHAR(9)+@sqlInsert +')'
PRINT @sqlInsert

-- select col list
SELECT @sqlColumns COALESCE(@sqlColumns+' ,' '') +'d.'name CHAR(13) + CHAR(9FROM sys.syscolumns WHERE OBJECT_NAME(id@tablename ORDER BY colid
SET @sqlColumns 'select '+CHAR(13) +CHAR(9)+ @sqlColumns 

--strip the last linebreak
SET @sqlColumns LEFT(@sqlColumns, (LEN(@sqlColumns)-2))
PRINT @sqlColumns

--generate the join condition between Inserted and Deleted tables if the table has Primary key
@sqlJoin ''
SELECT @sqlJoin COALESCE(@sqlJoin '') + 'd.'column_name ' = i.'column_name CHAR(13)+CHAR(9) +' and ' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name @tablename AND constraint_name LIKE '%PK%'
SET @sqlJoin 'from ' CHAR(13) + CHAR(9) + ' deleted d join inserted i on ' @sqlJoin 
--strip off the last 'and'
SET @sqlJoin LEFT(@sqlJoin, (LEN(@sqlJoin)-6))
@sqlJoin 'from deleted d, inserted i'

PRINT @sqlJoin

--generate the != clause where you check if atleast one column is changed...
DECLARE @coltype VARCHAR(100)
DECLARE @colname VARCHAR(100)
SET @sqlWhereFinal 'where'  

FROM sys.syscolumns sc JOIN sys.systypes st ON sc.xtype st.xtype 
(SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE   WHERE table_name  @tablename AND constraint_name LIKE '%PK%')

OPEN colcursor
FETCH next FROM colcursor INTO @coltype @colname
WHILE @@fetch_status 0
@sqlWhere ''
PRINT @sqlWhereFinal
SET @sqlWhereFinal         ''
SET @sqlWhere CASE WHEN @coltype IN('smalldatetime','datetime','sql_variant','ntext','varbinary','varchar','binary','char','timestamp','nvarchar','nchar','xml','sysname')
THEN  @sqlWhere CHAR(9) + 'isnull(d.'@colname +','''') != isnull(i.'@colname ','''') or'  
@sqlWhere CHAR(9) + 'isnull(d.'@colname +',-1) != isnull(i.'@colname ',-1) or'
@sqlWhereFinal @sqlWhereFinal @sqlWhere 
FETCH next FROM colcursor INTO @coltype @colname
DEALLOCATE colcursor

--remove the last 'or'
SET @sqlWhereFinal LEFT(@sqlWhereFinal, (LEN(@sqlWhereFinal)-3))
PRINT @sqlWhereFinal

The create update trigger script will generate a script like in the results window below. Copy the script into another query windows and execute it to create the new trigger.

The where clause in the update trigger checks if at least one column is changed for an entry to be made in the audit table.

IF OBJECT_ID('EmployeeTriggerTest_U'IS NOT NULL
DROP TRIGGER dbo.EmployeeTriggerTest_U
CREATE TRIGGER dbo.EmployeeTriggerTest_U
ON dbo.EmployeeTriggerTest FOR UPDATE

* Update trigger for EmployeeTriggerTest
* 01/01/2000 xxx New
deleted d JOIN inserted i ON d.EmployeeID i.EmployeeID
ISNULL(d.EmployeeName,'') != ISNULL(i.EmployeeName,'') OR
ISNULL(d.Department,'') != ISNULL(i.Department,'')

Let's say someone in HR changes the department of John Smith to IT from Sales and then from IT to MIS.  The update trigger will capture the previous values in the EmployeeTriggerTest_Audit table.

UPDATE dbo.EmployeeTriggerTest SET Department 'IT' WHERE EmployeeID 1
UPDATE dbo.EmployeeTriggerTest SET Department 'MIS' WHERE EmployeeID 1

-- You can see the EmployeeTriggerTest_Audit table will hold the old values for EmployeeID = 1
SELECT FROM dbo.EmployeeTriggerTest_Audit

Here is the output from the audit table showing the previous values for Department.

Next Steps

  • To make your life easy, having these kind of simple scripts to generate code saves lot of time.
  • Any repetitive task you do must be evaluated if it can be automated. Automation should be the goal of every DBA.
  • Take this process to the next step and add code to generate the audit table creation too.

Last Updated: 2009-06-11

get scripts

next tip button

About the author
MSSQLTips author Ranga Narasimhan Ranga Narasimhan has been in IT for over 10 years working on Siebel, Visual Basic, Crystal Reports and SQL Server.

View all my tips

Comments For This Article

Wednesday, November 14, 2018 - 2:51:50 AM - Morajaa Almalky Back To Top (78236)


 This tip was very helpfull and I was searching for such automation which saves time and effort. I am also searching for practical fundamentals for creating and relating tables for a certain database application. I mean GENERAL IDEAS. Thank you very much.

Hope that allow me to dierct contact for specific hints.

Wednesday, April 22, 2015 - 2:11:19 AM - jaydip joshi Back To Top (36995)




I am Jaydip Joshi from India.

I have try to create trigger for know DML Command Performance history.

But some information i can't get. Like(UserName,CommandPerformance,DB Name,HostName) in one quary.

So, I can know that is possible in SQL Server or Not.?

if any kind of update please replay on my E-Mail.





Wednesday, March 25, 2015 - 1:55:19 AM - ashraf Back To Top (36676)

Thanks a lot. its a magical works very effectively on the given example. can u please guide me how can I implement it on an existing database. the example you have shown is starts with creation of tables. In my case tables already exists in database (excluding the _AUDIT tables) and now I want to implement your script. how to go about it. thanks in advance

Tuesday, October 08, 2013 - 1:18:30 AM - rochie riva Back To Top (27073)

Sir, i have a question about sql server 2005 under views which is f5 for refresh.

is it possible we can create a trigger that will automatically refresh in every 30 minutes on our database?


Thanks and god speed

Monday, July 06, 2009 - 10:39:17 AM - dguillory Back To Top (3698)

This is the output I get from the code generator.


IF OBJECT_ID('dbo.EmployeeTriggerTest_U') IS NOT NULL
       DROP TRIGGER dbo.dbo.EmployeeTriggerTest_U

CREATE TRIGGER dbo.dbo.EmployeeTriggerTest_U
ON dbo.dbo.EmployeeTriggerTest FOR update
* Update trigger for dbo.EmployeeTriggerTest
* 01/01/2000 xxx New
from deleted d, inserted i


Recommended Reading

Run same command on all SQL Server databases without cursors

Recursive Queries using Common Table Expressions (CTE) in SQL Server

Searching and finding a string value in all columns in a SQL Server table

List columns and attributes for every table in a SQL Server database

SQL Server Row Count for all Tables in a Database

get free sql tips
agree to terms