TSQL for the Access Expert
I've been doing a lot of consulting work lately with Microsoft Access and SQL Server Express, and in one such project the client asked we add historical tables to the SQL database, both delete and update tracking. 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 aritcle assumes you're familiar with SQL Server tools and TSQL, use at your own risk!
The following content is intellectual property of Brett Kaiser, the original posting can be found 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 before you continue, delete any unncessary tables.
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! Make sure to track only main 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 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 And DATA_TYPE <> 'timestamp'
ORDER BY ORDINAL_POSITION
Print
@COLUMN_NAMES
SELECT @SQL = 'CREATE TRIGGER ' + @TABLE_SCHEMA + '_' + @TABLE_NAME + '_TR ON ' + @TABLE_SCHEMA + '.' +@TABLE_NAME
+ ' FOR UPDATE, 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 Exists (Select * From Inserted) And Exists (Select * From Deleted) '
+ ' 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 ''U'', SYSTEM_USER,
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 ''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'
Print @SQL
SELECT @sql = REPLACE(@SQL, ', Default ', ', [Default] ')
SELECT @sql = REPLACE(@SQL, ', Default) ', ', [Default]) ')
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
