The following is a script that generates an audit table for a provided table name.
It will:
– Add 5 audit columns to an existing table (DataVersion, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy)
– Create 2 triggers on the production table, one for updates and one for deletes (inserts do not need audit info)
– Create an audit table with similar design as the production table
– Create a primary key index on the audit table matching that of the production table but with the DataVersion also added as a part of it
Prerequisites:
– The stored procedure “sp_ScriptTableForAudit” must be present in the database (provided here)
– The script must be run in the database that the production table resides.
– The script does not handle table names with other schemas than dbo
– The table must have a primary key
Please add feedback/bug reports etc as comments and I will try to fix them right away
/*
Script created by Henning Frettem, aka Lumbago @ SQLTeam, 2010-03-23
BE CAREFUL, THIS SCRIPT WILL MAKE CHANGES TO YOUR TABLES!!
*/
SET NOCOUNT ON
BEGIN TRY
DECLARE @TableName varchar(100); SET @TableName = 'myTable'
DECLARE @AuditTableName varchar(200)
--> Checking prerequisites
IF NOT EXISTS (
SELECT 1
FROM sys.index_columns sc
JOIN sys.indexes i
ON sc.object_id = i.object_id
AND sc.index_id = i.index_id
WHERE OBJECT_NAME(sc.object_id) = @TableName
AND i.is_primary_key = 1)
RAISERROR('There is no primary key on the table', 16, 1)
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_ScriptTableForAudit')
RAISERROR('The stored procedure sp_ScriptTableForAudit is not present in the database', 16, 1)
SET @AuditTableName = @TableName + '_audit'
DECLARE @SQL nvarchar(max); SET @SQL = ''
--> Add audit columns to the production table if they don't exist already
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'DataVersion')
SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD DataVersion int NOT NULL DEFAULT 1' + CHAR(13)
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'CreatedDate')
SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD CreatedDate datetime NOT NULL DEFAULT GETDATE()' + CHAR(13)
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'CreatedBy')
SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD CreatedBy nvarchar(200) NOT NULL DEFAULT SUSER_NAME()' + CHAR(13)
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'ModifiedDate')
SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD ModifiedDate datetime NOT NULL DEFAULT GETDATE()' + CHAR(13)
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'ModifiedBy')
SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD ModifiedBy nvarchar(200) NOT NULL DEFAULT SUSER_NAME()' + CHAR(13)
IF LEN(@SQL) > 0
BEGIN
PRINT @SQL
EXEC sp_executesql @SQL
SET @SQL = ''
END
--> Create the audit table with the same definition as the production table, but add the IsDeleted column
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @AuditTableName)
BEGIN
DECLARE @AuditTableDefinition table (
RowNumber int IDENTITY(1, 1),
DefinitionRow varchar(max)
)
INSERT INTO @AuditTableDefinition
EXEC sp_ScriptTableForAudit @Tablename = @TableName, @NewTablename = @AuditTableName
--> Need IsDeleted column in audit table
UPDATE @AuditTableDefinition
SET DefinitionRow = ', IsDeleted bit NOT NULL DEFAULT(0) )'
WHERE RowNumber = (SELECT MAX(RowNumber) FROM @AuditTableDefinition)
SELECT @SQL = @SQL + DefinitionRow FROM @AuditTableDefinition
--> Run the create table script in the database
PRINT @SQL
EXEC sp_executesql @SQL
SET @SQL = ''
END
--> Create Update trigger
IF NOT EXISTS (SELECT 1 FROM sys.triggers WHERE name = 'trg' + REPLACE(@TableName, '.', '') + 'Update')
BEGIN
SET @SQL = @SQL + '
CREATE TRIGGER [trg' + REPLACE(@TableName, '.', '') + 'Update] ON ' + @TableName + '
FOR UPDATE
AS
SET NOCOUNT ON
UPDATE a SET
a.DataVersion = b.DataVersion + 1,
a.ModifiedDate = GETDATE(),
a.ModifiedBy = SYSTEM_USER
FROM ' + @TableName + ' a
INNER JOIN inserted b
ON '
SELECT @SQL = @SQL + 'a.' + c.name + ' = b.' + c.name + ' AND '
FROM sys.index_columns sc
JOIN sys.columns c
ON sc.object_id = c.object_id
AND sc.column_id = c.column_id
JOIN sys.indexes i
ON sc.object_id = i.object_id
AND sc.index_id = i.index_id
WHERE OBJECT_NAME(sc.object_id) = @TableName
AND i.is_primary_key = 1
ORDER BY index_column_id ASC
SET @SQL = LEFT(@SQL, LEN(@SQL) - 4) + CHAR(13) + CHAR(13)
SET @SQL = @SQL + ' INSERT INTO ' + @AuditTableName + '
('
SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @AuditTableName
SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
SET @SQL = @SQL + ')
SELECT '
SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @AuditTableName
SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
SET @SQL = @SQL + ' = 0
FROM deleted
GO
'
PRINT @SQL
EXEC sp_executesql @SQL
SET @SQL = ''
END
--> Create delete trigger
IF NOT EXISTS (SELECT 1 FROM sys.triggers WHERE name = 'trg' + REPLACE(@TableName, '.', '') + 'Delete')
BEGIN
SET @SQL = @SQL + '
CREATE TRIGGER [trg' + REPLACE(@TableName, '.', '') + 'Delete] ON ' + @TableName + '
FOR DELETE
AS
SET NOCOUNT ON
INSERT INTO ' + @AuditTableName + '
('
SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @AuditTableName
SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
SET @SQL = @SQL + ')
SELECT '
SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @AuditTableName
SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
SET @SQL = @SQL + ' = 1
FROM deleted
GO'
PRINT @SQL
EXEC sp_executesql @SQL
SET @SQL = ''
END
END TRY
BEGIN CATCH
PRINT Error_Message()
END CATCH