Create Deleted Records Audit Tables in 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.
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)
GO
— Now replace the code above with the code below in the query window. 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%’
— NOTE: Review the list of tables before going to the next step below
— Now you’re ready to create the audit tables with the code below:
DECLARE myCursor99 CURSOR
FOR
SELECT  TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
  FROM tblAudit
DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(MAX), @sql varchar(MAX)
SELECT @COLUMN_NAMES = ”
OPEN myCursor99
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
WHILE @@FETCH_STATUS = 0
  BEGIN
 SELECT @SQL = ‘CREATE TABLE ‘ + @TABLE_CATALOG + ‘.’ + @TABLE_SCHEMA + ‘.’ +  @TABLE_NAME + ‘_H (‘
 + ‘  HIST_ADD_DT datetime DEFAULT (getDate()), HIST_ADD_TYPE char(1) NOT NULL’
 + ‘, HIST_ADD_SYSTEM_USER sysname NOT NULL, HIST_ADD_USER_NAME sysname NOT NULL’
 + ‘, HIST_ADD_HOSTNAME sysname NOT NULL, HIST_ADD_SPID int NOT NULL, HIST_ADD_DESC varchar(50) ‘
 , @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 + ‘)’
 SELECT @sql = REPLACE(@SQL, ‘-1’, ‘MAX’)
 SELECT @sql = REPLACE(@SQL, ‘, Default ‘, ‘, [Default] ‘)
 SELECT SQL = @sql
 EXEC(@SQL)
 SELECT @SQL = ”, @COLUMN_NAMES = ”
 FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
  END
CLOSE myCursor99
DEALLOCATE myCursor99
GO
— The code below was added by me 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 the audit tables: Insert and Select. The following code will cycle through all of the tables in the database that end in ‘_h’ and assign the rights to the Public group. (All users)

DECLARE
myCursor99 CURSOR
FOR
SELECT
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM tblAudit

DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(MAX), @sql varchar(MAX)

SELECT @COLUMN_NAMES = ”
OPEN
myCursor99

FETCH
NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA,@TABLE_NAME

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL = ‘GRANT SELECT ON ‘ + @TABLE_CATALOG + ‘.’ +@TABLE_SCHEMA + ‘.’ + @TABLE_NAME + ‘_H TO PUBLIC’
SELECT SQL = @sql
EXEC(@SQL)

SELECT @SQL = ‘GRANT INSERT ON ‘ + @TABLE_CATALOG + ‘.’ + @TABLE_SCHEMA+ ‘.’ + @TABLE_NAME + ‘_H TO PUBLIC’
EXEC(@SQL)

FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG,@TABLE_SCHEMA, @TABLE_NAME
END

CLOSE myCursor99
DEALLOCATE myCursor99

GO

 — Now Lets create the audit TRIGGERS. Note: I’ve modified the original code so that it will not record changes to the Timestamp field and code that will encapsulate the word DEFAULT correctly when it’s encountered in the table. I also don’t wish to save the domain name along with the username in the table, so I’ve added code for that as well.
DECLARE myCursor99 CURSOR
FOR
SELECT  TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM tblAuditDECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(8000), @sql varchar(8000)

SELECT @COLUMN_NAMES = ”

OPEN myCursor99

FETCH NEXT FROM myCursor99 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 And DATA_TYPE <> ‘timestamp’
ORDER BY ORDINAL_POSITION

SELECT @SQL = ‘CREATE TRIGGER ‘ + @TABLE_SCHEMA + ‘_’ + @TABLE_NAME + ‘_TR ON ‘ + @TABLE_SCHEMA + ‘.’ +@TABLE_NAME
+ ‘ FOR DELETE AS ‘
+ ‘ DECLARE @HOSTNAME sysname, @DESC varchar(50) ‘
+ ‘ SELECT @HOSTNAME = hostname from master.dbo.sysprocesses where spid = @@SPID ‘
+ ‘ IF EXISTS(SELECT * FROM ‘ + @TABLE_NAME + ‘) SELECT @DESC = ‘
+ ”” + ”” + ‘ ELSE SELECT @DESC = ‘ + ”” + ‘MASS DELETE’ + ””
+ ‘ If Not Exists (Select * From Inserted) And Exists (Select * From Deleted) And @DESC = ”” ‘
+ ‘ INSERT INTO ‘ + @TABLE_NAME + ‘_H ( ‘
+ ‘ HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC’
+ @COLUMN_NAMES + ‘)’
+ ‘ SELECT ”D”, SYSTEM_USER, REPLACE (USER_NAME(),”DOMAINNAME”,””) , @HOSTNAME, @@SPID, @DESC’
+ @COLUMN_NAMES + ‘ FROM deleted’
+ ‘ If Not Exists (Select * From Inserted) And Exists (Select * From Deleted) And @DESC <> ”” ‘
+ ‘ INSERT INTO ‘ + @TABLE_NAME + ‘_H ( ‘
+ ‘ HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC’
+ @COLUMN_NAMES + ‘)’
+ ‘ SELECT TOP 1 ”D”, SYSTEM_USER, REPLACE(USER_NAME(),”DOMAINNAME”,””), @HOSTNAME, @@SPID, @DESC’
+ @COLUMN_NAMES + ‘ FROM deleted’

SELECT @sql = REPLACE(@SQL, ‘, Default ‘, ‘, [Default] ‘)
SELECT @sql = REPLACE(@SQL, ‘, Default) ‘, ‘, [Default]) ‘)
Print @SQL
Select TRIGGERSQL = @sql
EXEC(@SQL)
SELECT @SQL = ”, @COLUMN_NAMES = ”

FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
END

CLOSE myCursor99
DEALLOCATE myCursor99

GO

— Ok, you’re done! But what if you make a mistake? You can easily get rid of ALL triggers created earlier by running the following TSQL and starting the process anew
— *********** DROP TRIGGER CODE FOLLOWS ************
— Use this code in case you need to drop any triggers created earlier, in case
DECLARE myCursor99 CURSOR
FOR
SELECT  TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
  FROM tblAudit
DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(8000), @sql varchar(8000)
SELECT @COLUMN_NAMES = ”
OPEN myCursor99
FETCH NEXT FROM myCursor99 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 = ‘DROP ‘ + @TABLE_SCHEMA + ‘_’ +  @TABLE_NAME + ‘_TR ON ‘ +  @TABLE_SCHEMA + ‘.’ +@TABLE_NAME
Select @SQL = ‘DROP TRIGGER ‘ + @TABLE_SCHEMA + ‘_’ +  @TABLE_NAME + ‘_TR’
 Select SQL = @SQL
 
 EXEC(@SQL)
 SELECT @SQL = ”
 FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
 
  END
CLOSE myCursor99
DEALLOCATE myCursor99
GO

Leave a Reply

Your email address will not be published. Required fields are marked *

XHTML: You can use these tags <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>