Tech Blog
Since 2004
Generate Insert, Update, Delete Trigger for Table Audit
- February 13, 2016
Rest of the Story:
The following script will evaluate the table specified @TableName (you provide below), create a new table with {name}_Audit and also create insert, update and delete triggers. In my example my table was Dave_Test in scheme engis.
Here is a screen shot of the end result audit table with the same columns PLUS 2 additional columns to document the action executed against the source table.
Here you can see the created _Audit table and newly created _Trigger_Delete, _Trigger_Insert and _Trigger_Update
SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO
DECLARE @TableName VARCHAR(200);
SET @TableName = 'dave_test';
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--DECLARE @TABLENAME varchar(100)
DECLARE @SCHEMA VARCHAR(100);
--SET @TABLENAME = N'Company' SET @SCHEMA = N'engis';
DECLARE @Done BIT;
SET @Done = 0 DECLARE @CRLF CHAR(2);
SET @CRLF = CHAR(10); DECLARE @SQL VARCHAR(2000);
SET @SQL = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + @SCHEMA + '].[' + @TableName + '_Audit]'') AND type in (N''U'')) DROP TABLE [' + @SCHEMA + '].[' + @TableName + '_Audit] CREATE TABLE [' + @SCHEMA + '].[' + @TableName + '_Audit] (' + @CRLF; DECLARE @COLUMNID INT; SET @COLUMNID = 0; DECLARE @COLUMNNAME VARCHAR(1000); DECLARE @COLUMNTYPE VARCHAR(100); DECLARE @COLUMNSIZE INT; WHILE @Done = 0 BEGIN SELECT TOP 1 @COLUMNID = clmns.column_id , @COLUMNNAME = clmns.name , @COLUMNTYPE = usrt.name , @COLUMNSIZE = CAST(CASE WHEN baset.name IN ( N'nchar', N'nvarchar' ) AND clmns.max_length <> -1 THEN clmns.max_length / 2 ELSE clmns.max_length END AS INT) FROM sys.tables AS tbl INNER JOIN sys.all_columns AS clmns ON clmns.object_id = tbl.object_id LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = clmns.system_type_id AND baset.user_type_id = baset.system_type_id WHERE ( tbl.name = @TableName AND SCHEMA_NAME(tbl.schema_id) = @SCHEMA ) AND clmns.column_id > @COLUMNID ORDER BY clmns.column_id ASC; IF @@rowcount = 0 BEGIN SET @Done = 1; END; ELSE BEGIN SET @SQL = @SQL + ' [' + @COLUMNNAME + '] [' + @COLUMNTYPE + '] '; IF ( @COLUMNTYPE = 'nchar' OR @COLUMNTYPE = 'nvarchar' OR @COLUMNTYPE = 'varchar' ) SET @SQL = @SQL + '(' + LTRIM(STR(@COLUMNSIZE)) + ') ';
SET @SQL = @SQL + 'NULL, ' + @CRLF; END; END; SET @SQL = @SQL + ' [' + @TableName + 'UpdateDate] datetime, [' + @TableName + 'UpdateAction] nvarchar(10) ) '; --print @SQL EXEC (@SQL);
SET @SQL = ' IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[' + @SCHEMA + '].[' + @TableName + '_Trigger_Update]'')) DROP TRIGGER [' + @SCHEMA + '].[' + @TableName + '_Trigger_Update] IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[' + @SCHEMA + '].[' + @TableName + '_Trigger_Delete]'')) DROP TRIGGER [' + @SCHEMA + '].[' + @TableName + '_Trigger_Delete] IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[' + @SCHEMA + '].[' + @TableName + '_Trigger_Insert]'')) DROP TRIGGER [' + @SCHEMA + '].[' + @TableName + '_Trigger_Insert] '; --print @SQL EXEC(@SQL); SET @SQL = 'CREATE TRIGGER [' + @SCHEMA + '].[' + @TableName + '_Trigger_Update] ON [' + @SCHEMA + '].[' + @TableName + '] AFTER UPDATE AS BEGIN SET NOCOUNT ON; INSERT INTO ' + @TableName + '_Audit SELECT *,getdate(),''Update'' FROM inserted END '; EXEC (@SQL); --print @SQL SET @SQL = 'CREATE TRIGGER [' + @SCHEMA + '].[' + @TableName + '_Trigger_Delete] ON [' + @SCHEMA + '].[' + @TableName + '] AFTER DELETE AS BEGIN SET NOCOUNT ON; INSERT INTO ' + @TableName + '_Audit SELECT *,getdate(),''Delete'' FROM deleted END ';
EXEC (@SQL); --print @SQL SET @SQL = 'CREATE TRIGGER [' + @SCHEMA + '].[' + @TableName + '_Trigger_Insert] ON [' + @SCHEMA + '].[' + @TableName + '] AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO ' + @TableName + '_Audit SELECT *,getdate(),''Insert'' FROM inserted END '; EXEC (@SQL);
--print @SQL