Using Triggers to Track Database Action HistoryBy Tal Kelley
Several 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:
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
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
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
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
whenever an update or insert is made to the
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
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
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.