Using Triggers to track database action history

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.

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.

Note:  A database trigger only fires once per statement; therefore, if you perform a mass update or mass delete then those instances will not be recorded for each record. Keep this in mind when you develop your own triggers.