Triggers and Auditing

for RuBoard

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 INSERT ToyAudit SELECT 'INSERT', * FROM inserted GO INSERT ToyInventory DEFAULT VALUES GO 

Here we simply record any inserts into the ToyInventory table in a second table named ToyAudit. ToyAudit includes a column that indicates the operation that produced the log record. The insert trigger on ToyInventory supplies the string 'INSERT' for it.

What if we wanted to do something a little more sophisticated? For example, what if we wanted to track the before and after image when a row is updated? Using an AFTER UPDATE trigger, this is pretty easy. Listing 8-11 shows how:

Listing 8-11 An audit trigger can capture the before and after images of a row.

 CREATE TABLE ToyInventory (Toy int identity,  Type int,  Onhand int ) GO CREATE TABLE ToyAudit (ToyAudit int identity,  Operation varchar(20),  Toy int,  Type int,  Onhand int ) GO INSERT ToyInventory (Type, Onhand) VALUES (1, 50) INSERT ToyInventory (Type, Onhand) VALUES (2, 50) INSERT ToyInventory (Type, Onhand) VALUES (3, 50) INSERT ToyInventory (Type, Onhand) VALUES (4, 50) GO CREATE TRIGGER ToyInventory_UPDATE ON ToyInventory AFTER UPDATE AS IF @@ROWCOUNT=0 RETURN INSERT ToyAudit (Operation, Toy, Type, Onhand) SELECT 'UPDATE--BEFORE', * FROM deleted ORDER BY Toy INSERT ToyAudit (Operation, Toy, Type, Onhand) SELECT 'UPDATE-AFTER', * FROM inserted ORDER BY Toy GO UPDATE ToyInventory SET Onhand = 49 GO SELECT * FROM ToyAudit ORDER BY Toy, Operation, ToyAudit 

(Results)

 ToyAudit    Operation            Toy         Type        Onhand ----------- -------------------- ----------- ----------- ----------- 1           UPDATE--BEFORE       1           1           50 5           UPDATE-AFTER         1           1           49 2           UPDATE--BEFORE       2           2           50 6           UPDATE-AFTER         2           2           49 3           UPDATE--BEFORE       3           3           50 7           UPDATE-AFTER         3           3           49 4           UPDATE--BEFORE       4           4           50 8           UPDATE-AFTER         4           4           49 

This code demonstrates several techniques worth discussing further. First, it uses the logical table containing the before image of the updated rowsthe deleted tableto insert them into the audit table. It then does the same for the after-image table, inserted, and inserts the after-image rows into the audit table. When it selects from each of the logical tables, it orders the insertion using the Toy column so that the identity values in the ToyAudit table will be generated in Toy sequence. This allows us to later select the rows in the ToyAudit table using Toy as the high-order key and ToyAudit as the low-order key. This produces the result set you see in the listing where a row's after image immediately follows its before image, even though they originally came from two different logical tables.

Note the use of a double hyphen to force the before-image audit records to sort before the after ones. Because we're sorting alphabetically on the Operation column, we need a way of sorting the word BEFORE ahead of AFTER. Inserting the second hyphen is a cheap and easy way to do this.

What if we wanted to audit which columns were changed? What if we wanted to include the list of modified columns when we added log records to the audit table? This is pretty easy to do using our earlier technique of cross-referencing the bitmap returned by COLUMNS_UPDATED() with the ORDINAL_POSITION column in INFORMATION_SCHEMA. COLUMNS . Here's the code (Listing 8-12):

Listing 8-12 Using COLUMNS_UPDATED(), an audit trigger can record the names of modified columns.

 USE tempdb GO CREATE TABLE ToyInventory (Toy int identity,  Type int,  Onhand int ) GO CREATE TABLE ToyAudit (ToyAudit int identity,  Operation varchar(20),  Toy int,  Type int,  Onhand int,  ColumnsModified varchar(7000) ) GO INSERT ToyInventory (Type, Onhand) VALUES (1, 50) INSERT ToyInventory (Type, Onhand) VALUES (2, 50) INSERT ToyInventory (Type, Onhand) VALUES (3, 50) INSERT ToyInventory (Type, Onhand) VALUES (4, 50) GO CREATE TRIGGER ToyInventory_UPDATE ON ToyInventory AFTER UPDATE AS IF @@ROWCOUNT=0 RETURN DECLARE @ChangedColumns varbinary(8000) SET @ChangedColumns=COLUMNS_UPDATED() INSERT ToyAudit (Operation, Toy, Type, Onhand, ColumnsModified) SELECT 'UPDATE--BEFORE', d.*, c.COLUMN_NAME FROM deleted d JOIN INFORMATION_SCHEMA.COLUMNS c ON ((c.TABLE_NAME='ToyInventory') AND ((@ChangedColumns & POWER(2,c.ORDINAL_POSITION-1))<>0)) ORDER BY d.Toy INSERT ToyAudit (Operation, Toy, Type, Onhand, ColumnsModified) SELECT 'UPDATE-AFTER', i.*, c.COLUMN_NAME FROM inserted i JOIN INFORMATION_SCHEMA.COLUMNS c ON ((c.TABLE_NAME='ToyInventory') AND ((@ChangedColumns & POWER(2,c.ORDINAL_POSITION-1))<>0)) ORDER BY i.Toy GO UPDATE ToyInventory SET Onhand = 49, Type=3 GO SELECT * FROM ToyAudit ORDER BY Toy, Operation, ToyAudit, ColumnsModified 

(Results)

 ToyAudit Operation        Toy   Type  Onhand ColumnsModified -------- --------------- ----- ----- ------ --------------- 1         UPDATE--BEFORE  1     1      50     Type 2         UPDATE--BEFORE  1     1      50     Onhand 9         UPDATE-AFTER    1     3      49     Type 10        UPDATE-AFTER    1     3      49     Onhand 3         UPDATE--BEFORE  2     2      50     Onhand 4         UPDATE--BEFORE  2     2      50     Type 11        UPDATE-AFTER    2     3      49     Onhand 12        UPDATE-AFTER    2     3      49     Type 5         UPDATE--BEFORE  3     3      50     Type 6         UPDATE--BEFORE  3     3      50     Onhand 13        UPDATE-AFTER    3     3      49     Type 14        UPDATE-AFTER    3     3      49     Onhand 7         UPDATE--BEFORE  4     4      50     Onhand 8         UPDATE--BEFORE  4     4      50     Type 15        UPDATE-AFTER    4     3      49     Onhand 16        UPDATE-AFTER    4     3      49     Type 

for RuBoard