I was looking at creating an audit trail for data changes in a SQL Server database. There are many mechanisms for doing this and I decided on having a duplicate table to store the audit trail in. So for example:
Base Table:
User.Person
PersonID int
Name varchar(100)
Audit Table:
Audit.Person
AuditID int IDENTITY(1,1)
Action char(1)
Date datetime
PersonID int
Name varchar(100)
So the idea is that for each action that occurs (
INSERT,
UPDATE,
DELETE), the data as it was prior to the action is stored into the associated audit table. Obviously in the case of an
INSERT the new ID is stored as there was no data prior to the action.
Each base table has a matching
TRIGGER on it. This trigger looks like this:
CREATE TRIGGER [User].[TRG_Person_AuditHandle]
ON [User].[Person]
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
IF (SELECT COUNT(1) FROM deleted) > 0 AND (SELECT COUNT(1) FROM inserted) > 0
INSERT INTO [Audit].[Person] ([Action], [PersonID], [Name])
SELECT 'U', [PersonID], [Name] FROM deleted
ELSE IF (SELECT COUNT(1) FROM inserted) > 0
INSERT INTO [Audit].[Person] ([Action], [PersonID])
SELECT 'I', [PersonID] FROM inserted
ELSE IF (SELECT COUNT(1) FROM deleted) > 0
INSERT INTO [Audit].[Person] ([Action], [PersonID], [Name])
SELECT 'D', [PersonID], [Name] FROM deleted
END
This now gives a full audit trail to the
Person table.
Here's a wicked script to create all the audit tables and the triggers for the base tables:
/*
Creates the audit data structure for all requried tables
+ Creates the audit table to hold the changes in
+ Creates the trigger on the base table to add to the audit table
*/
DECLARE @SQL_DROP_AUDIT_TABLE varchar(MAX),
@SQL_CREATE_AUDIT_TABLE varchar(MAX),
@SQL_DROP_AUDIT_TRIGGER varchar(MAX),
@SQL_CREATE_AUDIT_TRIGGER varchar(MAX)
SET @SQL_DROP_AUDIT_TABLE =
'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[DF_#TABLE_NAME#_Date]'') AND type = ''D'')
ALTER TABLE [Audit].[#TABLE_NAME#] DROP CONSTRAINT [DF_#TABLE_NAME#_Date]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[Audit].[#TABLE_NAME#]'') AND type in (N''U''))
DROP TABLE [Audit].[#TABLE_NAME#]
GO'
SET @SQL_CREATE_AUDIT_TABLE =
'CREATE TABLE [Audit].[#TABLE_NAME#](
[AuditID] int IDENTITY(1,1) NOT NULL,
[Action] char(1) NOT NULL,
[Date] datetime NOT NULL,
#COLUMNS#
CONSTRAINT [PK_Audit_#TABLE_NAME#] 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]
) ON [PRIMARY]
GO
ALTER TABLE [Audit].[#TABLE_NAME#] ADD CONSTRAINT [DF_Audit_#TABLE_NAME#_Date] DEFAULT (getdate()) FOR [Date]
GO'
SET @SQL_DROP_AUDIT_TRIGGER =
'IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[#SCHEMA_NAME#].[TRG_#TABLE_NAME#_AuditHandle]''))
DROP TRIGGER [#SCHEMA_NAME#].[TRG_#TABLE_NAME#_AuditHandle]
GO'
SET @SQL_CREATE_AUDIT_TRIGGER =
'CREATE TRIGGER [#SCHEMA_NAME#].[TRG_#TABLE_NAME#_AuditHandle]
ON [#SCHEMA_NAME#].[#TABLE_NAME#]
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
IF (SELECT COUNT(1) FROM deleted) > 0 AND (SELECT COUNT(1) FROM inserted) > 0
INSERT INTO [Audit].[#TABLE_NAME#] ([Action],#COLUMNS#)
SELECT ''U'',#COLUMNS# FROM deleted
ELSE IF (SELECT COUNT(1) FROM inserted) > 0
INSERT INTO [Audit].[#TABLE_NAME#] ([Action], [#ID_COLUMN#])
SELECT ''I'', [#ID_COLUMN#] FROM inserted
ELSE IF (SELECT COUNT(1) FROM deleted) > 0
INSERT INTO [Audit].[#TABLE_NAME#] ([Action],#COLUMNS#)
SELECT ''D'',#COLUMNS# FROM deleted
END
GO'
-- Get all tables
DECLARE @SQL varchar(MAX), @SQLAuditTrigger varchar(MAX)
DECLARE @Schema varchar(255), @Table varchar(255), @SchemaTable varchar(255)
DECLARE cTables CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT TABLE_SCHEMA, TABLE_NAME, '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS [SCHEMA_TABLE]
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('PUT_SCHEMA_NAME_FILTERS_HERE_IF_REQUIRED')
AND TABLE_NAME NOT IN ('PUT_TABLE_NAME_FILTERS_HERE_IF_REQUIRED')
ORDER BY TABLE_SCHEMA, TABLE_NAME
OPEN cTables
-- Loop each table
FETCH NEXT FROM cTables INTO @Schema, @Table, @SchemaTable
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
-- Used to hold the column SQL that gets built for the CREATE statement
DECLARE @ColumnSQL varchar(MAX) = '', @TriggerColumnSQL varchar(MAX) = ''
-- Get all columns for a table
DECLARE @Column varchar(255), @Position int, @DataType varchar(255), @DataLength float
DECLARE cColumns CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @Schema
AND TABLE_NAME = @Table
ORDER BY ORDINAL_POSITION
OPEN cColumns
-- Loop each column
FETCH NEXT FROM cColumns INTO @Column, @Position, @DataType, @DataLength
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
-- Set up if it's the first column of a table
IF @Position = 1
BEGIN
-- Compile the drop statments and execute
SET @SQL = REPLACE(@SQL_DROP_AUDIT_TABLE, N'#TABLE_NAME#', @Table)
PRINT @SQL
--EXEC(@SQL)
SET @SQL = REPLACE(REPLACE(@SQL_DROP_AUDIT_TRIGGER, N'#TABLE_NAME#', @Table), N'#SCHEMA_NAME#', @Schema)
PRINT @SQL
--EXEC(@SQL)
-- Prepare the create statement (no columns in it at this point
SET @SQL = REPLACE(@SQL_CREATE_AUDIT_TABLE, N'#TABLE_NAME#', @Table)
--PRINT @SQL
SET @SQLAuditTrigger = REPLACE(REPLACE(REPLACE(@SQL_CREATE_AUDIT_TRIGGER, N'#TABLE_NAME#', @Table), N'#SCHEMA_NAME#', @Schema), N'#ID_COLUMN#', @Column)
--PRINT @SQLAuditTrigger
END
-- Build in the columns
SET @TriggerColumnSQL = @TriggerColumnSQL + ' [' + @Column + '],'
SET @ColumnSQL = @ColumnSQL + '[' + @Column + '] '
SET @ColumnSQL = @ColumnSQL +
CASE
WHEN @DataType = 'varchar' OR @DataType = 'nvarchar' OR @DataType = 'char' OR @DataType = 'nchar' THEN
CASE
WHEN (@DataType = 'varchar' OR @DataType = 'nvarchar') AND @DataLength = -1 THEN
@DataType + '(MAX)'
ELSE
@DataType + '(' + CAST(@DataLength AS varchar(MAX)) + ')'
END
ELSE
@DataType
END
SET @ColumnSQL = @ColumnSQL + ' NULL, '
-- If it's the last column then compile and execute the CREATE statements
IF (SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @Schema AND TABLE_NAME = @Table) = @Position
BEGIN
SET @SQL = REPLACE(@SQL, N'#COLUMNS#', @ColumnSQL)
PRINT @SQL
--EXEC(@SQL)
SET @SQL = REPLACE(@SQLAuditTrigger, N'#COLUMNS#', SUBSTRING(@TriggerColumnSQL, 1, LEN(@TriggerColumnSQL) - 1))
PRINT @SQL
--EXEC(@SQL)
END
END
FETCH NEXT FROM cColumns INTO @Column, @Position, @DataType, @DataLength
END
END
-- Tidy columns
CLOSE cColumns
DEALLOCATE cColumns
FETCH NEXT FROM cTables INTO @Schema, @Table, @SchemaTable
END
-- Tidy tables
CLOSE cTables
DEALLOCATE cTables
You'll see I'm using schemas - obviously if you are not, then change it ;) It assumes that the first column in the table is the unique identifier to the row.
