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 the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.