Go to Top

Create Delete and Update 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.

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

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:

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.

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:

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.

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:

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.

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.

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.

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.

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.

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.

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He’s a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

Leave a Reply

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

 

Contact Us
[gravityform id="16" title="false" description="false"]