O'Reilly logo

Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The by Ken Henderson

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Triggers and Auditing

I've already touched on this a bit in a few of the examples, but this is a popular enough use of triggers that it bears further discussion. AFTER triggers are frequently used to create an audit trail of modifications to a table. You can simply record the modification act itself, or you can store the actual changes that were made. Here's a simple example of auditing implemented through triggers (Listing 8-10):

Listing 8-10. A simple auditing trigger.
 USE tempdb GO CREATE TABLE ToyInventory (Toy int identity, Type int, Onhand int ) CREATE TABLE ToyAudit (ToyAudit int identity, Operation varchar(10), Toy int, Type int, Change int ) GO CREATE TRIGGER ToyInventory_INSERT ON ToyInventory AFTER INSERT AS IF @@ROWCOUNT=0 RETURN ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required