Using Triggers to track database action history

Introduction

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.

You might also like...

Comments

About the author

Super Tal

Super Tal United States

Always working hard!!

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“We better hurry up and start coding, there are going to be a lot of bugs to fix.”