his 10-Minute Solution was prompted by a question to the Ask the SQL Pro site regarding how best to capture changes that have occurred in a database. I will address this recurring issue by sharing some work I’ve recently completed for a client. This article assumes that you are familiar with coding triggers. If you’ve never coded or used a trigger, you should first read my previous 10-Minute Solutions on the subject (they’re called “The Trouble with Triggers,” Part I, Part II, and Part III). This part of my Solution covers capturing the changes; Part II will cover applying them to another database.

The Situation
My client has a database that contains information about companies for use in an e-commerce site. The information enters the database via an administrative Web site that allows a company’s personnel to add and edit the information regarding their company. This information is viewed by the public in another portion of the Web site. These changes are not made directly to the production database. Rather, they’re made in an “Edit” database. Before any change is actually moved to the production database, an administrator from each company is given the option of reviewing each change. Only approved changes are moved to the production database.

The information is contained in approximately 25 tables with over 400 columns. What would be the best way of capturing the changes? How could the changes be stored in one location? How would we move the changes to production?

I am not averse to using someone else’s code to solve a problem. I did a quick search to see if there were any tools on the market that would fulfill my objectives. I found several tools that would automate the building of triggers for capturing changes (e.g., SQL Audit from Red Matrix Technologies). However, no tool was well-suited to my purposes.

Here’s the solution I came up with.

Storing the Data
In order to simplify matters, I decided to store an audit trail of all changes to the database in one central table. I needed to be able to store every type of column in my table, so I stored the old and new values in the text fields (included in my database). Columns from other data types would then have to be converted to character format before they could be stored in the audit table. The structure of the audit table I created is illustrated in Table 1 below.

Table 1. Audit Table Structure
Column	Type	Description
IID	Integer	Auto generated primary key
CHANGE DATE	Datetime	Date change made
COMPANY ID	Integer	ID of company whose information was changed
ROW ID	Integer	Primary key of row that was changed; all tables in the database use a single integer column for the primary key
ROW DESC	Varchar(500)	A description of the row, suitable for display to the person reviewing the changes
CHANGE TYPE	CHAR(10)	INSERTED, DELETED, or UPDATED
FIELD NAME	Varchar(300)	Name of field that was changed. (blank or null for inserted or deleted)
TABLE NAME	Varchar(300)	Name of table that was changed
USER NAME	Varchar(300)	Name of user who changed the information
OLD_VALUE	Text	Value before change
NEW_VALUE	Text	Value after change
PROCESS STATUS	Int	0 if not moved to production database; 1 if moved

Capturing the Changes
In order to capture the changes, I created a trigger on each table that would write the changes to the audit table. Triggers are used as they are only executed in response to changes to information. They also provide a view of the data before and after the change, so the old and new values are captured with ease.

The following example illustrates the ease with which a trigger for an insert action may be created:

create trigger ti_company on company for insertasdeclare @blank charselect @blank = ”insert into auditlog(companyid, rowid, changetype, tablename, fieldname, old_value, new_value, username, rowdesc)select i.iid, i.iid, ‘INSERTED’, ‘company’, @blank, @blank, @blank, i.username, ‘Company: ‘ +i.CompanyNamefrominserted I

Once I built one trigger, it was relatively simple to generate insert triggers for each table. I just changed the name of the table and the row descriptor for each table. Similarly, delete triggers can be built from the same template by changing the words INSERT and INSERTED to DELETE and DELETED, respectively.

The Update Trigger
An update trigger should not be much more difficult to create. However, in practice, I was faced with what appeared to be a very repetitive, long, and mistake-prone task. First, let’s examine the code to store an update of a single column in the database:

if update( IsExpectingFunding)insert into auditlog(companyid, rowid, changetype, username, tablename, fieldname, old_value, new_value , rowdesc)select i.iid, i.iid, ‘UPDATED’, i.username, ‘company’, ‘IsExpectingFunding’,cast(d.IsExpectingFunding as char), cast(i.IsExpectingFunding as char), ‘Company: ‘ +i.CompanyNamefrominserted i,deleted dwhere i.iid = d.iid

This snippet of code begins by calling the IF UPDATE function to check if any change has been made to the field in question. If such a change has been made, a row with the appropriate information is inserted into the audit log. As you can see, the old and new values are retrieved from the virtual inserted and deleted tables respectively. (If you’ve never heard of these tables, see my related 10-Minute Solutions on triggers.)

Generating this code for every column in the database makes this a difficult task. Even with cutting and pasting, it’s a laborious and error-prone process.

Using Information Schema to Build the Trigger
For a given table, the only necessary difference between the statements for each column was the column name itself. I decided to write a cursor that would traverse the information_schema.columns view and print out the appropriate statements for each column. Thus, my trigger was built. The information_schema views are a set of views that expose metadata about your database. The columns view has a row for every column in your database. Each row includes such information as the table name and data type. (For more information on INFORMATION_SCHEMA, see my related 10-Minute Solution, “INFORMATION_SCHEMA: A Map to Your Database“.)

Basically, I used string variables to store the necessary portions of the statements, including the insert portion, the from clause, and the row descriptor. For each column, I combined the various clauses with the proper column name in one select statement. Following is the code I used for a table in my database called “patents”:

godeclare @table_name varchar(30), @column_name varchar(50), @data_type char(30), @from varchar(2048), @insert varchar(2048), @values varchar(2048), @rowid varchar(250)  set @insert = ‘insert into auditlog( companyid, rowid, changetype, username, tablename, fieldname, old_value, new_value , rowdesc)’select @rowid = “‘Patent Name: ‘ + i.patentname”select @values = “select i.fk_company, i.iid, ‘UPDATED’, i.username, “select @from = ‘frominserted i,deleted dwhere i.iid = d.iid’select @table_name = ‘patents’declare mycolumns cursor for select column_name,data_typefrom information_schema.columnswheretable_name = @table_name andcolumn_name <> ‘IID’ andcolumn_name not like ‘fk%’ order by data_type– iid is the primary key and columns that begin with fk are used for — foreign keys. No one edits these fields.open mycolumnsfetch next from mycolumns into @column_name,@data_typewhile @@fetch_status = 0begin   select ‘/* ‘ + @column_name + ‘ ‘[email protected]_type + ‘*/’ + char(13) +  ‘if update( ‘ + @column_name + ‘)’ + char(13) + @insert + char(13)  + @values + “‘” + @table_name + “‘,”+ “‘” + @column_name + “‘” + ‘,cast(d.’ + @column_name + ‘ as char), cast(i.’ + @column_name + ‘ as char),’ + @rowid + @from   fetch next from mycolumns into @column_name,@data_typeendclose mycolumnsdeallocate mycolumns

Here is a portion of the results. Other than the “create trigger” statement that I need to attach to the beginning, I am done. I also inserted “char(13)” for a carriage return at appropriate places so the output would be readable.

/* IsPatentIssued bit */if update( IsPatentIssued)insert into auditlog( companyid, rowid, changetype, username, tablename, fieldname, old_value, new_value, rowdesc)select i.fk_company, i.iid, ‘UPDATED’, i.username, ‘patents’, ‘IsPatentIssued’, cast(d.IsPatentIssued as char), cast(i.IsPatentIssued as char), ‘Patent Name: ‘ + i.patentnamefrominserted i,deleted dwhere i.iid = d.iid/* FilingDate datetime */if update( FilingDate)insert into auditlog( companyid, rowid, changetype, username, tablename, fieldname, old_value, new_value, rowdesc)select i.fk_company, i.iid, ‘UPDATED’, i.username, ‘patents’, ‘FilingDate’, cast(d.FilingDate as char), cast(i.FilingDate as char), ‘Patent Name: ‘ + i.patentnamefrominserted i,deleted dwhere i.iid = d.iid

I could have replaced the cursor with a simple select statement from information_schema. But when I originally wrote the code, I thought I would have different processing for each data type or would be using more than one select statement. Therefore, it was more appropriate to use a cursor. By the time I realized I didn’t need the cursor, the code was already working.

Query Analyzer Setting
When I first ran my code, I only saw part of the trigger statement that should have been generated. It appeared chopped off. As it turns out, there is a query analyzer option that controls the maximum number of characters displayed per column. The option is set to 256 characters by default. This setting can be changed by choosing Query > Current Connections Options and choosing the table titled Advanced. There you can increase the value.

Text Fields Cause Problems
With the above code, it was simple to generate triggers and to plug in different table names, the necessary joins, and the row descriptors. However, I ran into a problem as soon as I hit a table that contained a text field. Text fields cannot be referenced in the inserted or deleted tables. If this is attempted, SQL Server returns an error. I therefore needed to obtain the new value by referencing the actual table in which the change had been made. But in order to obtain the old value, I therefore needed to go back to the production database and obtain the original row before any edits had been made. The following snippet of code from a trigger on the “websites” table illustrates this point:

if update(url)insert into auditlog( companyid, rowid, changetype, tablename, fieldname, old_value, new_value, username, rowdesc)select i.fk_company, i.iid, ‘updated’, ‘websites’, ‘url’, old.url,new.url ,i.username, ‘url: ‘ + i.url frominserted iinner join companyedit.dbo.websites newon(i.iid = new.iid)left joincompanyinfo.dbo.websites oldon(i.iid = old.iid)

I was careful to use a left join for the old value. This was to protect against the insertion of a new row into the edit database followed by an update. The “old” value would not be found in the production database. An inner join would have caused the select statement to return no rows, leaving no audit trail. Using a left join, I created something of an audit trail even if I couldn’t record the old value.

In Part II, I will describe how I used dynamic SQL statements to move changes from the audit log to the production database.