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
-
Codeine on sale cheap online. Codeine fedex. Cheap online order Codeine. Where can I buy Codeine without a prescription.
by rawert (4 replies)
-
Cheap online order Fioricet. Cheap discount Fioricet. Offshore Fioricet online. How to buy Fioricet online without a prescription.
by rawert (12 replies)
-
Online pharmacy Amoxicillin cod. Ordering Amoxicillin online without prescription. Buy Amoxicillin FDA DEA approved. Amoxicillin legal FDA DEA approved.
by rawert (4 replies)
-
Percocet money order. Cheap non prescription Percocet. Order Percocet cod next day delivery. Canada Percocet no prescription.
by rawert (3 replies)
-
Online pharmacy fedex C.O.D Adderall. Adderall buy in UK. How to get Adderall prescription. Adderall next day cod fedex.
by rawert (3 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.