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 ...

Get Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.