Create Delete and Update Audit Tables in Microsoft SQL Server

This is a modification to an earlier tip I wrote, whereas this code will only record delete changes, my earlier article will record delete and update changes to the data.

I found code online by Brett Kaiser and I modified it for my purposes, with a mind to other Access developers I made some changes which I will explain here. Note: This article assumes you’re familiar with SQL Server tools and TSQL.

UPDATE 2012-05-15: Ben modified the script to be fully replayable and added a means of keeping the history table synchronized with the base table’s change. If a new column was added to the base table, running the script would add that needed column to the base table.

To view the original post from Brett Kaiser, please click here:

Using SQL Server Management Studio express, open a new query window pointing towards your DB and start by creating a new table that will store all of the tables you wish to implement audit triggers for:
-- CREATE A Driver Table for all Tables in your catalog you wish to audit
CREATE TABLE tblAudit(
TABLE_CATALOG sysname,
TABLE_SCHEMA sysname,
TABLE_NAME sysname
);
You can either type the table names into the new tblAudit table or you can use the following code which will insert a record for each table in the database. CAUTION: Don’t add triggers to a table unless they are mission critical, some tables you may not want to add triggers for our look up tables or tables where not everyone has access too. If you do use the code below to populate the tblAudit table then STOP and review the list of tables and delete any unncessary tables you don’t wish to track.
INSERT INTO tblAudit(
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME
)
SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'tbl%';
The script in its entirety is provided here. We’ll review critical aspect of the script section by section. First thing to note that is the script will run in a transaction and is embedded in a try/catch block. That gives us a way to avoid making incomplete changes and thus leave the database in a consistent state at all times.

Before we get started, we have some variables that may need initializing:
DECLARE @TABLE_CATALOG sysname,
@TABLE_SCHEMA sysname,
@TABLE_NAME sysname,
@OBJECT_NAME sysname,
@NEW_NAME sysname,
@GRANTEE sysname,
@DOMAIN_NAME sysname,
@COLUMN_NAMES varchar(MAX),
@SQL varchar(MAX);
SELECT @DOMAIN_NAME = 'MYDOMAIN', @GRANTEE = 'MyRoleGroup',
@COLUMN_NAMES = '', @SQL = '';
You should update the value for @DOMAIN_NAME and @GRANTEE to suit your environment. @DOMAIN_NAME is used later in the script to remove the domain prefix from the username. @GRANTEE is used to identify which role should be granted the SELECT permission on the history tables. You could choose to use PUBLIC or a specific role, depending on your security structure.

The first major step in the script is to delete all original triggers, if they exist.
DECLARE myCursor00 CURSOR FOR
SELECT DB_NAME(), TABLE_SCHEMA, TABLE_NAME
FROM dbo.tblAudit
;
OPEN myCursor00;
FETCH NEXT FROM myCursor00
INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @COLUMN_NAMES = @COLUMN_NAMES + ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = @TABLE_CATALOG
AND TABLE_SCHEMA = @TABLE_SCHEMA
AND TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION;
SELECT @SQL =
'IF OBJECT_ID(''' + @TABLE_SCHEMA + '_' + @TABLE_NAME + '_TR'') '
+ 'IS NOT NULL DROP TRIGGER '
+ @TABLE_SCHEMA + '_' + @TABLE_NAME + '_TR;';
PRINT @SQL;
EXEC(@SQL);
SELECT @SQL = '';
FETCH NEXT FROM myCursor00
INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME;
END;
CLOSE myCursor00;
DEALLOCATE myCursor00;
The next section can be broken up into 3 smaller steps which are then executed as a single dynamic SQL. Those 3 steps are executed all together in a single iteration of the cursor loop. Refer to the script at Create and Refresh History Tables for the complete code.

First, we create temporary copies of original history tables if they exists:
IF OBJECT_ID(
@TABLE_CATALOG + '.' + @TABLE_SCHEMA + '.' + @TABLE_NAME + '_H'
) IS NOT NULL
BEGIN
SELECT
@OBJECT_NAME =
@TABLE_CATALOG + '.' + @TABLE_SCHEMA + '.' + @TABLE_NAME + '_H',
@NEW_NAME = '#' + @TABLE_NAME + '_H'
;
SELECT @SQL = ']CRLF[ SELECT * INTO ' + @NEW_NAME
+ ' FROM ' + @OBJECT_NAME
+ ';]CRLF[ DROP TABLE ' + @OBJECT_NAME + ';';
PRINT 'RENAMED ' + @OBJECT_NAME + ' TO ' + @NEW_NAME;
END;
Next, we create the new history table definition based on the current base table’s definition, thus picking up all changes that have been introduced to the base table since last time we created the audit table.
SELECT @SQL = @SQL + ']CRLF[ CREATE TABLE '
+ @TABLE_CATALOG + '.' + @TABLE_SCHEMA + '.' + @TABLE_NAME + '_H ( ]CRLF['
+ ' HIST_ADD_DT datetime DEFAULT (getDate()), HIST_ADD_TYPE char(1) NOT NULL ]CRLF['
+ ', HIST_ADD_SYSTEM_USER sysname NOT NULL'
+ ', HIST_ADD_USER_NAME sysname NOT NULL ]CRLF['
+ ', HIST_ADD_HOSTNAME sysname NOT NULL, HIST_ADD_SPID int NOT NULL'
+ ', HIST_ADD_DESC varchar(50) ]CRLF[';
SELECT @COLUMN_NAMES = @COLUMN_NAMES + ', ' + COLUMN_NAME + ' ' + DATA_TYPE
+ CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '(' + CONVERT(varchar(20),CHARACTER_MAXIMUM_LENGTH) + ')'
WHEN DATA_TYPE = 'decimal'
THEN '(' + CONVERT(varchar(20),NUMERIC_PRECISION) + ','
+ CONVERT(varchar(20),NUMERIC_SCALE) + ')'
ELSE ''
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = @TABLE_CATALOG
AND TABLE_SCHEMA = @TABLE_SCHEMA
AND TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION;

SELECT @SQL = @SQL + @COLUMN_NAMES + ‘);]CRLF[‘;
Thirdly, we move the data from temporary copy into the new history table. Because audit table has no constraints, any columns that isn’t present in the original history table will be simply NULL. Note that the script assumes that there is no drastic change in column’s data types in such way that implicit conversion may fail. In order to be able to list what columns we can map between the temporary copy and the new history table, we have to join them and that requires executing a dynamic SQL in a dynamic SQL string.
IF LEN(@NEW_NAME) > 0
BEGIN
SELECT @SQL = @SQL + 'DECLARE @COLUMN_NAMES varchar(MAX)'
+ ' , @SQL varchar(MAX); ]CRLF['
+ 'SELECT @COLUMN_NAMES = '''', @SQL = '''';]CRLF['
+ 'SELECT @COLUMN_NAMES = @COLUMN_NAMES + '', '' +'
+ ' n.COLUMN_NAME ]CRLF['
+ 'FROM ( ]CRLF[ '
+ ' SELECT COLUMN_NAME, ORDINAL_POSITION ]CRLF['
+ ' FROM INFORMATION_SCHEMA.COLUMNS ]CRLF['
+ ' WHERE TABLE_CATALOG = ''' + @TABLE_CATALOG + ''' ]CRLF['
+ ' AND TABLE_SCHEMA = ''' + @TABLE_SCHEMA + ''' ]CRLF['
+ ' AND TABLE_NAME = ''' + @TABLE_NAME + '_H'' ]CRLF['
+ ' AND DATA_TYPE <> ''timestamp'' ]CRLF[
+ ' AND DATA_TYPE <> ''rowversion'' ]CRLF['
+ ') AS n ]CRLF['
+ 'INNER JOIN ( ]CRLF['
+ 'SELECT COLUMN_NAME ]CRLF['
+ 'FROM tempdb.INFORMATION_SCHEMA.COLUMNS ]CRLF['
+ 'WHERE TABLE_CATALOG = ''tempdb'' ]CRLF['
+ ' AND TABLE_SCHEMA = ''' + @TABLE_SCHEMA + ''' ]CRLF['
+ ' AND TABLE_NAME LIKE ''#' + @TABLE_NAME + '_H%'' ]CRLF['
+ ') AS o ]CRLF['
+ 'ON n.COLUMN_NAME = o.COLUMN_NAME ]CRLF['
+ 'ORDER BY n.ORDINAL_POSITION; ]CRLF['
+ 'IF (LEN(@COLUMN_NAMES) > 0) ]CRLF['
+ 'BEGIN '
+ ' SELECT @SQL = @SQL + '' INSERT INTO ' + @OBJECT_NAME + ' ( '' '
+ ' + '' ]['' + @COLUMN_NAMES + '' '' '
+ ' + '') SELECT ]['' + @COLUMN_NAMES + '' '' '
+ ' + ''FROM ' + @NEW_NAME + ';''
+ ' + ''DROP TABLE ' + @NEW_NAME + ';'''
+ ' SELECT @SQL = REPLACE(@SQL, ''][,'', '''');'
+ ' PRINT @SQL;'
+ ' EXEC (@SQL);'
+ 'END;'
END;
Once we’ve completed those 3 sub-steps, we then execute the @SQL to copy the history table, drop & recreate the history table and if needed synchronize the original audit data to new history table. We just need to clean up the @SQL by replacing the DEFAULT and put in line breaks so that the printout will generate user-readable SQL.
SELECT @SQL = REPLACE(@SQL,']CRLF[',CHAR(13) + CHAR(10));
SELECT @SQL = REPLACE(@SQL, '-1', 'MAX');
SELECT @SQL = REPLACE(@SQL, ', Default ', ', [Default] ');
PRINT @SQL;
EXEC(@SQL);
Next major step was added by Juan and not part of the original article, in order for users to be able to see changes to the data, they are going to need security rights to select data from the audit tables. The following code will cycle through all of the tables in the database that end in ‘_h’ and assign the rights to the designated group. If you want to allow all users to view the audit table, assign string ‘PUBLIC’ to @GRANTEE variable at start of the script.
SELECT @SQL = 'GRANT SELECT ON '
+ @TABLE_CATALOG + '.' +@TABLE_SCHEMA + '.' + @TABLE_NAME
+ '_H TO ' + @GRANTEE + ';';
PRINT @SQL;
EXEC(@SQL);
SELECT @SQL = 'GRANT INSERT ON '
+ @TABLE_CATALOG + '.' + @TABLE_SCHEMA+ '.' + @TABLE_NAME
+ '_H TO ' + @GRANTEE + ';';
EXEC(@SQL);
Now, we’re in 3rd and last step of the script; creating the audit triggers on the base table. Juan modified the original code so that it will not record changes to the rowversion (aka timestamp) field and code that will encapsulate the word DEFAULT correctly when it’s encountered in the table. Juan also don’t wish to save the domain name along with the username in the table, so @DOMAIN_NAME is used to remove the domain name from the returned username.
SELECT @COLUMN_NAMES = @COLUMN_NAMES + ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = @TABLE_CATALOG
AND TABLE_SCHEMA = @TABLE_SCHEMA
AND TABLE_NAME = @TABLE_NAME
AND (DATA_TYPE <> 'timestamp'
AND DATA_TYPE <> 'rowversion')
ORDER BY ORDINAL_POSITION;
SELECT @SQL = 'CREATE TRIGGER ' + @TABLE_SCHEMA + '_' + @TABLE_NAME
+ '_TR ]CRLF['
+ 'ON ' + @TABLE_SCHEMA + '.' + @TABLE_NAME
+ ' FOR UPDATE, DELETE AS ]CRLF['
+ 'BEGIN ]CRLF['
+ ' DECLARE @HOSTNAME sysname, @DESC varchar(50); ]CRLF['
+ ' SELECT @HOSTNAME = hostname ]CRLF['
+ ' FROM master.dbo.sysprocesses ]CRLF['
+ ' WHERE spid = @@SPID; ]CRLF['
+ ' IF EXISTS(SELECT * FROM ' + @TABLE_NAME + ') ]CRLF['
+ ' SELECT @DESC = ' + '''' + '''' + ';]CRLF['
+ ' ELSE ]CRLF['
+ ' SELECT @DESC = ' + '''' + 'MASS DELETE' + ''';]CRLF['
+ ' IF EXISTS (SELECT * FROM inserted) ' +
+ ' AND EXISTS (SELECT * FROM deleted) ]CRLF['
+ ' INSERT INTO ' + @TABLE_NAME + '_H ( ]CRLF['
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, '
+ ' HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC ]CRLF['
+ ' ' + @COLUMN_NAMES + ') ]CRLF['
+ ' SELECT ''U'', SYSTEM_USER, '
+ ' REPLACE(USER_NAME(),''' + @DOMAIN_NAME + ''',''''),'
+ ' @HOSTNAME, @@SPID, @DESC ]CRLF['
+ ' ' + @COLUMN_NAMES + ']CRLF['
+ ' FROM deleted;]CRLF['
+ ' IF NOT EXISTS (SELECT * FROM inserted) '
+ ' AND EXISTS (SELECT * FROM deleted) '
+ ' AND @DESC = '''' ]CRLF['
+ ' INSERT INTO ' + @TABLE_NAME + '_H ( ]CRLF['
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME,'
+ ' HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC ]CRLF['
+ ' ' + @COLUMN_NAMES + ') ]CRLF['
+ ' SELECT ''D'', SYSTEM_USER, '
+ ' REPLACE(USER_NAME(),''' + @DOMAIN_NAME + ''',''''),'
+ ' @HOSTNAME, @@SPID, @DESC ]CRLF['
+ ' ' + @COLUMN_NAMES + ']CRLF['
+ ' FROM deleted;]CRLF['
+ ' IF NOT EXISTS (SELECT * FROM inserted)
+ ' AND EXISTS (SELECT * FROM deleted)
+ ' AND @DESC <> '''' ]CRLF['
+ ' INSERT INTO ' + @TABLE_NAME + '_H ( ]CRLF['
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME,'
+ ' HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC ]CRLF['
+ ' ' + @COLUMN_NAMES + ') ]CRLF['
+ ' SELECT TOP 1 ''D'', SYSTEM_USER, '
+ ' REPLACE(USER_NAME(),''' + @DOMAIN_NAME + ''',''''),'
+ ' @HOSTNAME, @@SPID, @DESC ]CRLF['
+ ' ' + @COLUMN_NAMES + ']CRLF['
+ ' FROM deleted;]CRLF['
+ 'END;'
;
SELECT @SQL = REPLACE(@SQL, ']CRLF[',CHAR(13) + CHAR(10));
SELECT @SQL = REPLACE(@SQL, ', Default ', ', [Default] ');
SELECT @SQL = REPLACE(@SQL, ', Default) ', ', [Default]) ');
PRINT @SQL;
EXEC(@SQL);
Ok, you’re done! But what if you make a mistake? No problem, just run the script again once you’ve corrected the error. Because the script is wrapped in a transaction, everything about it must succeed all together or fail all together. It also prints out the generated dynamic SQL, so you can review and verify that it’s generating the expected output and if there’s an error, it’ll print out the error at the end in addition to rollbacking the transaction and clearing out the cursors.