Library tutorials & articles
Using Triggers to track database action history
- Introduction
- Create tables
- 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.
Always working hard!!
Related articles
Related discussion
-
Buy cheap Xanax overnight. Cheap Xanax. Overnight delivery of Xanax in US no prescription needed. Cheapest Xanax.
by asleymar (0 replies)
-
Buy Soma online without a prescription. Soma drug no prescription. How to get Soma prescription. Soma cod accepted.
by asleymar (0 replies)
-
Cheap online order Fioricet. Cheap discount Fioricet. Offshore Fioricet online. How to buy Fioricet online without a prescription.
by asleymar (0 replies)
-
Buy Ambien no visa without prescription. Not expensive Ambien prescriptions. Ambien no rx. Cod delivery Ambien.
by asleymar (0 replies)
-
Tramadol without doctor rx. Buy Tramadol over the counter cod overnight. Cheap Tramadol cod delivery. Buy Tramadol from mexico online.
by asleymar (0 replies)
Related podcasts
-
ADO.NET "Astoria" Data Services with Shawn Wildermuth
Scott chats with Shawn Wildermuth, "the ADO Guy," about ADO.NET Data Services, aka "Project Astoria." It's REST for SQL Server. Should you care? What's REST? How does this relate to WCF or ASP.NET?
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.
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
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.
This thread is for discussions of Using Triggers to track database action history.