Using Triggers to Track Database Action History
By Tal KelleySeveral State and Federal contracts that I have worked on in the past have wanted to keep track of all changes to a particular record, such as who modified the record, what kind of modification took place, and when the record modification occurred. I decided to solve this problem with triggers because I wanted to capture every action that occurred on a particular table. For those unfamiliar with triggers, a trigger is a database-event that fires when a particular action occurs. For example, an INSERT trigger would fire when a particular database table has a record inserted. For a more in-depth examination of triggers, be sure to read: An Introduction to Triggers.
The Web application that needed to track all database actions was an ASP Intranet application connected to a SQL Server 7 database, using NT Challenge/Response authentication. There were many database tables in this application, but for this article, we'll just examine one particular table and how I managed to track the history for this table. Applying this technique to other database tables is trivial.
The table we'll examine was called Account_Info
and contained the following structure:
Account_Info
| |
---|---|
COLUMN NAME | TYPE/SIZE |
account_id
| varchar (25) |
account_name | varchar(50) |
account_balance | money |
modifier | varchar(25) |
Notice the modifier
column in the table. The modifier
column records who added the
row to the Account_Info
table, and is populated by the Web
application, which gets its value from the Session variable Request.ServerVariables("LOGON_USER")
,
which, when using NT Challenge/Response, holds the name of the logged on user.
Whenever an Insert or Update is performed on the table, the modifier
is tacked on to remember who made the
transaction. (Deletes are not considered in this example since most records are not allowed for deletion in
most State and Federal systems; due to historical purposes, they are merely marked Inactive.)
If you like, go ahead and created a couple of ASP pages that will Insert and Update the table.
While this technique will accurately record who inserted a record, or last updated a record, it still leaves a
lot to be desired. For example, if a particular row is altered (updated) numerous times, the row will only
contain one value in the modifier
column - who performed the last update. We may want to
know the complete history of changes. Also, we may want to have extra information, such as when the
record was initially created, and the date/time of each update to the row.
In order to overcome these limitations, we'll create a new table whose sole purpose will be to track
the history for the Account_Info
table. (Note that for each table in your database design, you
will need to add a new history table.) The history table, History_Account_Info
, has the following
database structure:
History_Account_Info
| |
---|---|
COLUMN NAME | TYPE/SIZE |
history_account_id | varchar (25) |
history_account_name | varchar(50) |
history_account_balance | money |
modifier | varchar(25) |
modified_date | datetime |
action | varchar(15) |
Note that for each column in the
Account_Info
table there is a corresponding column in the
History_Account_Info
table. Additionally, the History_Account_Info
table contains
modifier_date
and action
columns, to indicate when the change was made and what
change (and update or insert) was made.
At this point, we will create our trigger to automatically insert a record into the History_Account_Info
whenever an update or insert is made to the Account_Info
table.
I will demonstrate how to create the triggers using SQL Server 7 Enterprise Manager.
Start by right-clicking the Account_Info
table and selecting "All Tasks" and then
"Manager Triggers...".
At this point, the Trigger Properties dialog box then appears. This is where you enter your Trigger code.
Let's first create an insert trigger, one that will fire when a record is inserted into the
Account_Info
table.
|

Now click the Check Syntax button to verify that you have typed everything correctly, then click OK.
Our last trigger that we will create will be an update trigger, one that fires when a record in the
Account_Info
table is updated.
|
Well, that is it! Now simply use your application and a history is recorded of all your changes. Or simply
insert or update directly through SQL Server 7. Hope this helps some of you! The screenshot below shows
a data dump of the History_Account_Info
table after two inserts and an update.

Happy Programming!