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

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