Problem
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.
Solution
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.
Create Test Tables and Data
This scripts below generate some test tables and some sample data.
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 PRIMARY KEY CLUSTERED(EmployeeID)
)
GO
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(256) CONSTRAINT DF_EmployeeTriggerTest_Audit_ModifiedBy DEFAULT SUSER_SNAME()
)
GO
Create some test data.
INSERT INTO dbo.EmployeeTriggerTest (EmployeeID, EmployeeName, Department)
VALUES (1,'John Smith','Sales')
INSERT INTO dbo.EmployeeTriggerTest (EmployeeID, EmployeeName, Department)
VALUES (2,'Ram Kumar','Finance')
INSERT INTO dbo.EmployeeTriggerTest (EmployeeID, EmployeeName, Department)
VALUES (3,'John Doe','IT')
SELECT * FROM dbo.EmployeeTriggerTest
Script to Generate the Update Trigger
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 @sqlInsert VARCHAR(MAX)
DECLARE @sqlColumns VARCHAR(MAX)
DECLARE @sqlJoin VARCHAR(MAX)
DECLARE @sqlWhere VARCHAR(MAX)
DECLARE @sqlWhereFinal VARCHAR(MAX)
DECLARE @sqlHeader VARCHAR(MAX)
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.'+@tablename+'_U
GO
CREATE TRIGGER dbo.'+@tablename+'_U
ON dbo.'+@tablename+' FOR update
/**************************************************************
* Update trigger for '+@tablename+'
*
* MODIFICATIONS
* 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(9) FROM 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
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name = @tablename AND constraint_name LIKE '%PK%')
BEGIN
SET @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))
END
ELSE
SET @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'
DECLARE colcursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT st.name, sc.name
FROM sys.syscolumns sc JOIN sys.systypes st ON sc.xtype = st.xtype
WHERE OBJECT_NAME(sc.id) = @tablename AND sc.name NOT IN
(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
BEGIN
SET @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'
ELSE
@sqlWhere + CHAR(9) + 'isnull(d.'+ @colname +',-1) != isnull(i.'+ @colname + ',-1) or'
END
SET @sqlWhereFinal = @sqlWhereFinal + @sqlWhere
FETCH next FROM colcursor INTO @coltype , @colname
END
CLOSE colcursor
DEALLOCATE colcursor
--remove the last 'or'
SET @sqlWhereFinal = LEFT(@sqlWhereFinal, (LEN(@sqlWhereFinal)-3))
PRINT @sqlWhereFinal
Sample Trigger Creation
The create update trigger script will generate a script like in the results window below. Copy the script that is generated into another query window and execute it to create the new trigger. Note, 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
GO
CREATE TRIGGER dbo.EmployeeTriggerTest_U
ON dbo.EmployeeTriggerTest FOR UPDATE
/**************************************************************
* Update trigger for EmployeeTriggerTest
*
* MODIFICATIONS
* 01/01/2000 xxx New
**************************************************************/
AS
INSERT INTO dbo.EmployeeTriggerTest_Audit(
EmployeeID
,EmployeeName
,Department
)
SELECT
d.EmployeeID
,d.EmployeeName
,d.Department
FROM
deleted d JOIN inserted i ON d.EmployeeID = i.EmployeeID
WHERE
ISNULL(d.EmployeeName,'') != ISNULL(i.EmployeeName,'') OR
ISNULL(d.Department,'') != ISNULL(i.Department,'')
Test the Update Trigger
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
GO
UPDATE dbo.EmployeeTriggerTest SET Department = 'MIS' WHERE EmployeeID = 1
GO
-- 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.
Notes
This script assumes the table is in the “dbo” schema, so to use this with other schemas parts of the script will need to be updated.
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.
Ranga Narasimhan has been in IT for about 10 years. He started as a Visual Basic / Crystal Reports developer and then started coding in SQL Server. He has been a SQL Server developer and for the last few years doing SQL server production support operations. In his last job, Ranga was the primary DBA for the Siebel call center database and has learned a lot from the challenges that a very large database can bring and an application where queries are built dynamically. He enjoys spending time with his family. His interests are playing tennis and golf.
Thanks Red. I updated the article to include information that this just works with the “dbo” schema.
-Greg
Code still works, the @tableName and @AuditTable cannot include the schema, just the table name or you will get the error from the last comment. If your table name exists in multiple schemas then you will have to modify the code to find the correct Object_ID.