Auditing Your Tables

I wrote this script to use at work to create triggers on all of the tables here at work so that we can audit changes. I have modified it slightly to include the Create/Alter of the audit table. Soon I will be posting queries that will allow you to easily view the information in the audits. Please note that if you want things translated in your audit table, you can do lookups in the triggers, but this will require a manual change. So, without further anticipation:

--******************************************************--
--* Author: Nathan Wheeler *--
--* Date: 3/12/08 *--
--* Description: This query will look through each *--
--* table in the used catalog and create an auditing *--
--* trigger for each table with a primary key. *--
--* It will also create the table in which to place *--
--* the audit entries. *--
--* Licensing: Feel free to use this script to create *--
--* audits on all your catalogs, just don't give it *--
--* away without this notice. *--
--* *--
--* For more stuff like this, visit: *--
--* http://maxaffinity.blogspot.com *--
--******************************************************--
--Insert catalog name here.
USE [<catalog>]
GO
SET NOCOUNT ON
--If the AuditTable is there alter it, otherwise create it.
IF OBJECT_ID(('AuditTable'), 'U') IS NOT NULL
PRINT 'CREATE TABLE [dbo].[AuditTable]'
ELSE
PRINT 'ALTER TABLE [dbo].[AuditTable]'
PRINT ' ('
PRINT ' [AuditID] [int] IDENTITY(1, 1)'
PRINT ' NOT NULL'
PRINT ' ,[TableName] [varchar](50) NOT NULL'
PRINT ' ,[KeyName] [varchar](50) NOT NULL'
PRINT ' ,[KeyValue] [varchar](50) NOT NULL'
PRINT ' ,[FieldName] [varchar](50) NOT NULL'
PRINT ' ,[FieldOldValue] [varchar](8000) NULL'
PRINT ' ,[FieldNewValue] [varchar](8000) NULL'
PRINT ' ,[ModifiedBy] [varchar](75) NOT NULL'
PRINT ' ,[ModifiedDate] [datetime] NOT NULL'
PRINT ' ,CONSTRAINT [PK_AuditTable] PRIMARY KEY CLUSTERED ([AuditID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
PRINT ' )'
PRINT 'ON [PRIMARY]'
PRINT 'GO'
DECLARE
@TableName VARCHAR(255)
,@KeyName VARCHAR(255)
,@Schema VARCHAR(255)
,@TriggerName VARCHAR(255)
,@FieldName VARCHAR(255)
DECLARE table_cursor CURSOR
FOR SELECT
KU.TABLE_NAME
,KU.COLUMN_NAME
,KU.CONSTRAINT_SCHEMA + '.'
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
WHERE
--In the "NOT IN" is where you can list any tables you DON'T want to audit.
--Be sure to put at least the AuditTable in here.
KU.TABLE_NAME NOT IN ('AuditTable')
ORDER BY KU.TABLE_NAME
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @TableName, @KeyName, @Schema
WHILE @@FETCH_STATUS = 0
BEGIN
--The trigger will be the name of the table prefixed be "audittr_".
--This can be changed by setting @TriggerName to something different.
SET @TriggerName = @Schema + 'audittr_' + @TableName
DECLARE field_cursor CURSOR
FOR SELECT
c.name
FROM
sysobjects o
INNER JOIN syscolumns c
ON o.id = c.id
WHERE
o.name = @TableName
ORDER BY
o.name
,c.colorder
--If the trigger already exists, alter it instead of create.
IF OBJECT_ID((@TriggerName), 'TR') IS NOT NULL
PRINT 'ALTER TRIGGER ' + @TriggerName
ELSE
PRINT 'CREATE TRIGGER ' + @TriggerName
PRINT ' ON ' + @Schema + @TableName
PRINT ' AFTER UPDATE'
PRINT 'AS '
PRINT ''
PRINT ' INSERT INTO AuditTable'
OPEN field_cursor
--Get the first field name.
FETCH NEXT FROM field_cursor INTO @FieldName
PRINT ' SELECT ' + QUOTENAME(@TableName, '''') + ' AS TableName,'
PRINT ' ' + QUOTENAME(@KeyName, '''') + ' AS KeyName,'
PRINT ' INSERTED.' + @KeyName + ' AS KeyValue,'
PRINT ' ' + QUOTENAME(@FieldName, '''') + ' AS FieldName,'
PRINT ' CAST(DELETED.' + @FieldName + ' AS VARCHAR(MAX)) AS FieldOldValue,'
PRINT ' CAST(INSERTED.' + @FieldName + ' AS VARCHAR(MAX)) AS FieldNewValue,'
PRINT ' SYSTEM_USER AS ModifiedBy,'
PRINT ' getdate() AS ModifiedDate'
PRINT ' FROM INSERTED'
PRINT ' INNER JOIN DELETED'
PRINT ' ON INSERTED.' + @KeyName + ' = DELETED.' + @KeyName
PRINT ' WHERE CAST(DELETED.' + @FieldName + ' AS VARCHAR(MAX)) <> CAST(INSERTED.' + @FieldName + ' AS VARCHAR(MAX))'
--If there is another field (can't see why you'd want just one)
--get the next field name.
FETCH NEXT FROM field_cursor INTO @FieldName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' UNION ALL'
PRINT ' SELECT ' + QUOTENAME(@TableName, '''') + ','
PRINT ' ' + QUOTENAME(@KeyName, '''') + ','
PRINT ' INSERTED.' + @KeyName + ','
PRINT ' ' + QUOTENAME(@FieldName, '''') + ','
PRINT ' CAST(DELETED.' + @FieldName + ' AS VARCHAR(MAX)),'
PRINT ' CAST(INSERTED.' + @FieldName + ' AS VARCHAR(MAX)),'
PRINT ' SYSTEM_USER,'
PRINT ' getdate()'
PRINT ' FROM INSERTED'
PRINT ' INNER JOIN DELETED'
PRINT ' ON INSERTED.' + @KeyName + ' = DELETED.' + @KeyName
PRINT ' WHERE CAST(DELETED.' + @FieldName + ' AS VARCHAR(MAX)) <> CAST(INSERTED.' + @FieldName + ' AS VARCHAR(MAX))'
-- Get the next field.
FETCH NEXT FROM field_cursor INTO @FieldName
END
PRINT 'GO'
--Close and deallocate the field cursor for the next table.
CLOSE field_cursor
DEALLOCATE field_cursor
--Get the next table.
FETCH NEXT FROM table_cursor INTO @TableName, @KeyName, @Schema
END
--Close and deallocate the table cursor.
CLOSE table_cursor
DEALLOCATE table_cursor