Library tutorials & articles

Using Triggers to track database action history

By Super Tal, published on 12 Jan 2003
Super Tal Always working hard!!
Page 1 of 3
  1. Introduction
  2. Create tables
  3. Create triggers

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.

Related tags

sql server

Comments

  1. vasudev varma
    yesterday

    Hai Tal

    This Complete structure of the trigger and the table is fine...... this works fine and it will be good for one table.... but it will more fine and efficient if u embed all the operation info into one single trigger and run it up al at once.........

  2. Jeff Moden
    13 Feb 2008 at 06:22

    Oh, no.... I didn't say anything about using a cursor.  There's no need for the RBAR (pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row").  Do it all using proper set based techniques.

  3. Sven M. Dietze
    14 Jan 2005 at 03:07

    Hi Tal,


    Jeff is right. Just declare a cursor that iterates over the inserted/deleted table. It should fetch at least your PKs, which you can use to retrieve the inserted/deleted lines - one after another.


    However, your example is very sufficent and helped me a lot.


    Thanks, Sven

  4. Jeff Moden
    12 May 2004 at 21:04

    Hi Tal,


    You wrote "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. "


    If you write your audit triggers to do an INSERT/SELECT from the INSERTED or DELETED table available in the trigger, then all records of a "mass update" or "mass delete" will be properly recorded in the audit table.

  5. Developer Fusion Bot
    01 Jan 1999 at 00:00

    This thread is for discussions of Using Triggers to track database action history.

Leave a comment

Sign in or Join us (it's free).

AddThis

Related articles

Ask a question

Related discussion

Related podcasts

  • Stack Overflow: Podcast #28

    This is the twenty-eighth episode of the StackOverflow podcast, where
Joel and Jeff discuss Windows Azure, SQL Server 2008 full text search, Bayesian filtering, porn detection, and project management — among other things. 

Jeff met the inestimable Joey DeVilla aka Accordion Guy...

Related jobs

Events coming up

  • Mar 23

    DevWeek 2009

    London, United Kingdom

    DevWeek is Europe’s leading independent conference for software developers, database professionals and IT architects, and features expert speakers on a wide range of topics, including .NET Framework 4.0, Silverlight 2, WCF 4.0, Visual Studio 2010, RESTful services, Windows Workflow, ASP.NET AJAX 4.0, SQL Server 2008, LINQ, C# 3, .NET Patterns, Ruby, and more.

Languages

Web Development

Frameworks & Architecture

Other major sections