When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
Technology Jobs
ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs
Print this page.
Published: Wednesday, September 19, 2001

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.

- continued -

'

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 NAMETYPE/SIZE
account_id varchar (25)
account_namevarchar(50)
account_balancemoney
modifiervarchar(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 NAMETYPE/SIZE
history_account_idvarchar (25)
history_account_namevarchar(50)
history_account_balancemoney
modifiervarchar(25)
modified_datedatetime
actionvarchar(15)

Creating a trigger in MS SQL Server 7.0 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

Creating the Insert Trigger.

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.

A data dump of the History_Account_Info table.

Happy Programming!

  • By Tal Kelley

  • Software Developer / Programmer - Distributed Systems (NYC)
    Next Step Systems
    US-NY-New York

    Justtechjobs.com Post A Job | Post A Resume


    ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article