Using Triggers to Track Database Action History By 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: 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. CREATE TRIGGER [insert_history] ON Account_Info FOR INSERT AS INSERT History_Account_Info (history_account_id, history_account_name, history_account_balance, modifier, modified_date, action) SELECT account_id, account_name, account_balance, modifier, GETDATE(), 'INSERTED' FROM inserted 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. CREATE TRIGGER [update_history] ON Account_Info FOR UPDATE AS INSERT History_Account_Info (history_account_id, history_account_name, history_account_balance, modifier, modified_date, action) SELECT account_id, account_name, account_balance, modifier, GETDATE(), 'BEFORE UPDATE' FROM deleted INSERT History_Account_Info (history_account_id, history_account_name, history_account_balance, modifier, modified_date, action) SELECT account_id, account_name, account_balance, modifier, GETDATE(), 'AFTER UPDATE' FROM inserted 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! By Tal Kelley

Article Information
Article Title:	Using Triggers to Track Database Action History
Article Author:	Tal Kelley
Published Date:	Wednesday, September 19, 2001
Article URL:	http://www.4GuysFromRolla.com/webtech/091901-1.shtml