Good article, by coincidence I wrote a script recently that generates three triggers for every table in a database for tracking change history of tablecontents into one single _Changehistory table using the xml datatype.
This script should enable you to implement data change auditing with little effort. Please read preconditions and limitations.
-- ==========================================================================================
-- Author:Hans Michiels
-- Create date: 4 June 2008
-- Description:
--Generate triggers to detect inserts, deletes and updates in a database
-- PRECONDITIONS:
-- * Tablenames do not contain spaces
-- * A table _ChangeHistory exists in your database, if not this script will create this table.
-- * All tables have a single column that is primary key or has a unique index, if not a column
-- called 'id' will be added to the table by this script
-- * After schema changes in your database (e.g. new columns or tables) you should rerun this script to
-- make sure that the triggers contain these new columns and new triggers are made for new tables.
-- LIMITATIONS:
-- * Impact on performance has not been tested, be careful when using this in a production
-- environment!
-- * Changes of columns of type text, ntext and image are not stored in table [_ChangeHistory]
-- * It seems like only first 8000 characters of changes of columns of type varchar(max),
-- nvarchar(max) and varbinary(max) are stored in table [_ChangeHistory], but this needs more testing.
-- ==========================================================================================
DECLARE @Cursor1 CURSOR
DECLARE @Cursor2 CURSOR
DECLARE @Tablename nvarchar(255)
DECLARE @SQL nvarchar(max)
DECLARE @SourceDb nvarchar(50)
DECLARE @KeyColumn varchar(50)
DECLARE @ColumnName varchar(50)
DECLARE @ColumnNames varchar(max)
DECLARE @SQLHead nvarchar(255)
DECLARE @Newline varchar(2)
DECLARE @ColIndent varchar(8)
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
Use YourDatabaseNameHere
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[_ChangeHistory]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[_ChangeHistory](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](50) NOT NULL,
[Action] [char](6) NOT NULL,
[DateCreated] [datetime] NOT NULL CONSTRAINT [DF__ChangeHistory_DateCreated] DEFAULT (getdate()),
[KeyValue] [varchar](255) NULL,
[DeletedRecord] [xml] NULL,
[InsertedRecord] [xml] NULL,
[Username] [varchar](50) NOT NULL CONSTRAINT [DF__ChangeHistory_Username] DEFAULT (user_name())
) ON [PRIMARY];
SET @Newline = CHAR(13) + CHAR(10)
SET @ColIndent = SPACE(8)
SET @SQLHead = '-- ============================================='
+ @Newline + '-- Author: Hans Michiels'
+ @Newline + '-- Create date: ' + CONVERT(nvarchar(25), getdate(),103)
+ @Newline + '-- Description: This trigger is generated with a'
+ @Newline + '-- script. Do not modify it by hand.'
+ @Newline + '-- ============================================='
SET NOCOUNT ON
SET @Cursor1 = CURSOR STATIC FOR
SELECT CONVERT(varchar(255), so.name) AS tablename
FROM dbo.sysobjects AS so
WHERE so.type = 'U' and so.name <> '_ChangeHistory' -- and so.name = '_TestTypes'
ORDER BY so.name
OPEN @Cursor1
FETCH NEXT FROM @Cursor1 INTO @Tablename
WHILE @@FETCH_STATUS = 0
BEGIN
--\
---> Get the primary key field name
--/
SELECT @KeyColumn = NULL;
SELECT @KeyColumn = c.name
FROM sys.objects AS o INNER JOIN
sys.indexes AS i ON o.object_id = i.object_id INNER JOIN
sys.index_columns AS ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id INNER JOIN
sys.columns AS c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
WHERE (o.type = 'U')
AND (o.name = @Tablename)
AND ((i.is_primary_key = 1) OR (i.is_unique = 1 AND i.ignore_dup_key = 0 AND i.is_unique_constraint = 1))
ORDER BY i.is_primary_key DESC, i.type
IF @KeyColumn IS NULL
BEGIN
-- If the table has no primary key, a column named 'id' will be added as primary key. It is defined as IDENTITY (autonumber)
SELECT @SQL = 'ALTER TABLE [dbo].[' + @Tablename + '] ADD id INT IDENTITY CONSTRAINT PK_' + @Tablename + '_ID PRIMARY KEY'
EXEC sp_executesql @SQL
SELECT @KeyColumn = 'id'
END
--\
---> Cursor2 for building string with all fieldnames.
--/
SET @Cursor2 = CURSOR STATIC FOR
SELECT c.name as columnname
FROM sys.objects AS o
INNER JOIN sys.columns AS c on o.object_id = c.object_id
WHERE o.name = @Tablename
AND o.type = 'U'
AND (c.system_type_id <> 34) -- image } These types cannot be used as source
AND (c.system_type_id <> 35) -- text } column in SQL statement that uses
AND (c.system_type_id <> 99) -- ntext } FOR XML AUTO
ORDER BY c.column_id
OPEN @Cursor2
SET @ColumnNames = ''
FETCH NEXT FROM @Cursor2 INTO @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @ColumnNames = @ColumnNames + ', [' + @ColumnName + ']' + @Newline + @ColIndent
FETCH NEXT FROM @Cursor2 INTO @ColumnName
END
DEALLOCATE @Cursor2
-- Remove first comma:
SELECT @ColumnNames = SUBSTRING(@ColumnNames, 3, 9999)
--\
---> UPDATE trigger - DROP current
--/
SELECT @SQL = 'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trgChgHis_' + @Tablename + 'Update]'') AND OBJECTPROPERTY(id, N''IsTrigger'') = 1) DROP TRIGGER dbo.trgChgHis_' + @Tablename + 'Update'
EXEC sp_executesql @SQL
--\
---> UPDATE trigger - CREATE new
--/
SELECT @SQL = @SQLHead
+ @Newline + 'CREATE TRIGGER dbo.trgChgHis_' + @Tablename + 'Update ON dbo.' + @Tablename + ' AFTER UPDATE AS '
+ @Newline + 'BEGIN '
+ @Newline + ' SET NOCOUNT ON;'
+ @Newline + ' INSERT INTO [dbo].[_ChangeHistory]([TableName],[Action],[KeyValue],[DeletedRecord],[InsertedRecord])'
+ @Newline + ' SELECT '
+ @Newline + ' ''' + @Tablename + ''' AS [TableName]'
+ @Newline + ' , ''UPDATE'' AS [Action]'
+ @Newline + ' , CONVERT(varchar(255), [' + @KeyColumn + ']) AS [KeyValue]'
+ @Newline + ' , (Select '
+ @Newline + ' ' + @ColumnNames
+ 'From deleted as ' + @Tablename + 'Record'
+ @Newline + ' Where [' + @Tablename + 'Record].[' + @KeyColumn + '] = [maindel].[' + @KeyColumn + ']'
+ @Newline + ' FOR XML AUTO, TYPE, ROOT(''Deleted'') ) AS [DeletedRecord]'
+ @Newline + ' , (Select '
+ @Newline + ' ' + @ColumnNames
+ 'From inserted as ' + @Tablename + 'Record'
+ @Newline + ' Where [' + @Tablename + 'Record].[' + @KeyColumn + '] = [maindel].[' + @KeyColumn + ']'
+ @Newline + ' FOR XML AUTO, TYPE, ROOT(''Inserted'') ) AS [InsertedRecord]'
+ @Newline + ' FROM deleted AS maindel;'
+ @Newline + 'END'
EXEC sp_executesql @SQL
--\
---> INSERT trigger - DROP current
--/
SELECT @SQL = 'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trgChgHis_' + @Tablename + 'Insert]'') AND OBJECTPROPERTY(id, N''IsTrigger'') = 1) DROP TRIGGER dbo.trgChgHis_' + @Tablename + 'Insert'
EXEC sp_executesql @SQL
--\
---> INSERT trigger - CREATE new
--/
SELECT @SQL = @SQLHead
+ @Newline + 'CREATE TRIGGER dbo.trgChgHis_' + @Tablename + 'Insert ON dbo.' + @Tablename + ' AFTER INSERT AS '
+ @Newline + 'BEGIN '
+ @Newline + ' SET NOCOUNT ON;'
+ @Newline + ' INSERT INTO [dbo].[_ChangeHistory]([TableName],[Action],[KeyValue],[DeletedRecord],[InsertedRecord])'
+ @Newline + ' SELECT '
+ @Newline + ' ''' + @Tablename + ''' AS [TableName]'
+ @Newline + ' , ''INSERT'' AS [Action]'
+ @Newline + ' , CONVERT(varchar(255), [' + @KeyColumn + ']) AS [KeyValue]'
+ @Newline + ' , NULL AS [DeletedRecord]'
+ @Newline + ' , (Select '
+ @Newline + ' ' + @ColumnNames
+ 'From inserted as ' + @Tablename + 'Record'
+ @Newline + ' Where [' + @Tablename + 'Record].[' + @KeyColumn + '] = [mainins].[' + @KeyColumn + ']'
+ @Newline + ' FOR XML AUTO, TYPE, ROOT(''Inserted'') ) AS [InsertedRecord]'
+ @Newline + ' FROM inserted AS mainins;'
+ @Newline + 'END'
EXEC sp_executesql @SQL
--\
---> DELETE trigger - DROP current
--/
SELECT @SQL = 'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[trgChgHis_' + @Tablename + 'Delete]'') AND OBJECTPROPERTY(id, N''IsTrigger'') = 1) DROP TRIGGER dbo.trgChgHis_' + @Tablename + 'Delete'
EXEC sp_executesql @SQL
--\
---> DELETE trigger - CREATE new
--/
SELECT @SQL = @SQLHead
+ @Newline + 'CREATE TRIGGER dbo.trgChgHis_' + @Tablename + 'Delete ON dbo.' + @Tablename + ' AFTER DELETE AS '
+ @Newline + 'BEGIN '
+ @Newline + ' SET NOCOUNT ON;'
+ @Newline + ' INSERT INTO [dbo].[_ChangeHistory]([TableName],[Action],[KeyValue],[DeletedRecord],[InsertedRecord])'
+ @Newline + ' SELECT '
+ @Newline + ' ''' + @Tablename + ''' AS [TableName]'
+ @Newline + ' , ''DELETE'' AS [Action]'
+ @Newline + ' , CONVERT(varchar(255), [' + @KeyColumn + ']) AS [KeyValue]'
+ @Newline + ' , (Select '
+ @Newline + ' ' + @ColumnNames
+ 'From deleted as ' + @Tablename + 'Record'
+ @Newline + ' Where [' + @Tablename + 'Record].[' + @KeyColumn + '] = [maindel].[' + @KeyColumn + ']'
+ @Newline + ' FOR XML AUTO, TYPE, ROOT(''Deleted'') ) AS [DeletedRecord]'
+ @Newline + ' , NULL AS [InsertedRecord]'
+ @Newline + ' FROM deleted AS maindel;'
+ @Newline + 'END'
EXEC sp_executesql @SQL
FETCH NEXT FROM @Cursor1 INTO @Tablename
END
DEALLOCATE @Cursor1
GO