Auditing Advice

When you need to audit data changes, you face several challenges. The course of audit action you take might depend on the number of affected rows, the type of statement that modified the data, and whether you want to audit both successful events and failed events. Also, you might want to audit each attribute value change in a separate audit row. Dealing with such auditing issues can be tricky, and as you'll see later, you need to apply logic to come up with solutions. We'll look at a strategy for auditing data changes, then move on to specific techniques you can use to accomplish different aspects of your auditing requirements. Remember to check The Logical Puzzle for the solution to July's logic puzzle, "Calculating the Arithmetic Maximum," and try your hand at August's puzzle, "Covering a Chessboard with Domino Tiles."

Auditing Data Changes

Some company policies require auditing of all data changes and storing the audit information in audit tables. Typically, you need to audit the fact that a change took place, who performed the change, when it took place, and so on. Depending on the company's auditing policy, you might also need to keep track of the data that was modified. Let's look at some examples that use a table called T1 with a simple schema: keycol is the primary key, intcol is an integer data column, and varcharcol is a varchar data column. Both data columns allow NULLs. Suppose you need to keep track of the fact that a modification took place in T1 and store general information about the change in a header audit table, where each row represents one modification event. You also need to store the actual data changes that took place against T1 in detail audit tables, where each event in the header table might have multiple related rows in a detail table because multiple rows/attributes were affected.

Run the code in Listing 1 to create the table T1, and the audit tables T1Audit Header, T1AuditInsDelDetail, and T1AuditUpdDetail, and ignore any max row size warnings you might get. As you can see in Listing 1, T1AuditHeader will contain a row for each modification event with nine attributes. Id (Event ID) is an autonumber generated by an IDENTITY property; dt represents the event date and time. The other seven attributes are loginname, app, host, failed (a bit column specifying whether the event was accepted or rolled back), dmltype, which represents the statement type (i.e., I=Insert, U=Update, D=Delete), row_count (the number of affected rows), and comment, which specifies the reason for failing the event.

For each inserted or deleted row, T1AuditInsDelDetail will hold a row with an id of the row, the id of the related header row, and the actual data row attributes. T1AuditUpdDetail will hold a row for each modified attribute value that has an id, the id of the related header row, the primary key, the changed column name, the value before the change, and the value after the change.

You need to write a trigger that audits all INSERT, UPDATE, and DELETE statements. The trigger must first record an event for each statement attempting to modify data in the T1AuditHeader table. Then, it fails/rolls back the following modification attempts:

For failed events, the audit row should contain a 1 in the failed column. If an UPDATE attempts to modify the primary key, you don't audit anything in the detail table. In all other cases, you'll fully audit the change (or change attempt) in the detail tables.

As I mentioned, you'll face several challenges when writing the audit trigger. The four most important ones are capturing @@rowcount, auditing failed events, identifying the statement type, and auditing updates.

Capturing @@rowcount

From the auditing requirements, you'll realize that you need to take various actions based on the number of rows the modifying statement affected. Typically, you don't want to do anything if no rows were affected—there's nothing to audit in that case, not even the header row. If the statement is a DELETE and it affected more than one row, you want to roll back the modification. Of course, you can check inserted and deleted to find the number of rows affected, but when you access these tables, you're basically scanning part of the transaction log.

To avoid this unnecessary waste of resources, you can get the number of affected rows by running the @@rowcount function.

DECLARE @rc AS int;SET @rc = @@rowcount;IF @rc = 0 RETURN;

The function holds the number of rows affected by the previous statement—that is, the triggering statement. But you must declare a variable and capture @@row count's value as the first two lines of code in your trigger. Any other statement besides DECLARE will change @@rowcount's value. Then, check whether any rows were affected; if none, you break from the trigger by running the RETURN command.

Auditing Failed Events

In this case, the auditing requirements demand that you fail (roll back) events in certain cases but still audit them. The request is tricky; you need to audit the change attempt by copying data from inserted and deleted to the audit tables. If you copy them before you issue the rollback, the rollback will also undo the audit. If you copy them after the rollback, the original modification is undone, and deleted and inserted become empty. You're in a catch-22 situation. Fortunately, a rollback doesn't affect variables, including table variables. All you need to do is copy the content of inserted and deleted into table variables before the rollback. Then, query the table variables to load information to the audit tables after the rollback in a new transaction within the trigger:

DECLARE @inserted TABLE(keycol int...);INSERT INTO @inserted SELECT * FROM inserted;DECLARE @deleted TABLE(keycol int...);INSERT INTO @deleted SELECT * FROM deleted;

This code shows you how to copy the content of inserted and deleted into your own table variables.

Identifying Statement Type

As part of the audit requirements, you need to audit the type of statement that modified the data (INSERT, UPDATE, or DELETE). Also, some of the auditing activity is the same for all statement types (e.g., auditing header row), while some activity is different for each statement type. For example, the detail audit table for updates would be T1AuditUpdDetail and for inserts and deletes would be T1AuditInsDelDetail. Also, each statement type has a different failure scenario.

Of course, you could write three separate triggers, one for each statement type, so that you wouldn't need to identify the type of statement. However, implementing three separate triggers would also mean duplicating all the logic that is similar for all statement types. Every time you need to change that logic, you'll need to revise three different routines.

You can write one trigger for all statement types, using EXISTS predicates to check whether rows exist in inserted and deleted to figure out which type of statement fired the trigger:

DECLARE @dmltype AS char(1);IF EXISTS(SELECT * FROM inserted)  IF EXISTS(SELECT * FROM deleted) SET @dmltype = 'U';  ELSE SET @dmltype = 'I';ELSE SET @dmltype = 'D';

If rows exist in inserted, the statement is either an INSERT or an UPDATE. Existence of rows in deleted would determine whether it's an INSERT (deleted is empty) or an UPDATE (deleted has rows). If inserted is empty, the statement must be a DELETE. The above code sets the @dmltype variable with a character representing the type of statement. Later on in the trigger, you can inspect @dmltype to determine a course of action when the action is dependent on the type of statement.

Auditing Updates

Auditing the detail rows for inserts and deletes is a no-brainer—just copy the content of @inserted and @deleted to T1AuditInsDelDetail. However, auditing the detail rows for updates to T1AuditUpdDetail is tricky because you want to record each attribute value that actually changed to a separate target row in T1AuditUpdDetail. The trick is to use an unpivoting technique to rotate each row from @inserted and @deleted into multiple rows—one for each data column in the table. (For details about unpivoting, see my August 2004 T-SQL 2005 column, "UNPIVOT," InstantDoc ID 43589.) The query in Listing 2 at callout A performs the unpivoting technique that returns one row for each attribute value that actually changed.

The query joins @inserted and @deleted based on a primary key match. Then the query joins the result of the previous join with the auxiliary table C, which contains a row for each column in T1. This join duplicates each row from the previous join once for each column. The SELECT that creates the derived table D extracts the old and new column values for each column by using CASE expressions that inspect the colname value. Or in more simple terms, the SELECT list uses CASE expressions to extract the old and new values. Finally, the outer query keeps only the rows where the new value is different from the old value, also taking NULLs into consideration.

Implementing the Audit Trigger

I've explained the tricky parts of the audit trigger; all the rest is straightforward. You're now ready to implement the full trigger by running the code that Listing 2 shows. This code creates one trigger for all INSERT, UPDATE, and DELETE statements. The trigger takes these steps:

Now that you've created the trigger, you can issue modifications to test it. First, set your computer's clock to a time later than or equal to 8:00 a.m. and earlier than 5:00 p.m. Then, run the code in Listing 3, which contains three INSERT statements, one UPDATE statement, and one DELETE statement. They should all run successfully.

Now set your computer's clock to a time earlier than 8:00 a.m. or later than or equal to 5:00 p.m. and run the code in Listing 4, which contains DELETE, UPDATE, and INSERT statements that should all fail. Remember that detail rows should be audited except in the case of the failed UPDATE, which tried to modify the primary key.

Next, run the code in Listing 5 to query T1 and all audit tables. When I ran this code, I got the results shown in Tables 1 through 4. Table 1 shows the contents of the T1 table after all modifications: two rows. Table 2 shows eight audit header rows: five successful modifications and three failed. Table 3 shows seven audit detail rows for inserts and deletes. And finally, Table 4 shows two audit detail rows for updates.

The Logic of Auditing Auditing data changes involves overcoming several tricky obstacles. You need to be able to identify the number of rows that the modification affected, identify the type of modification, audit data for failed events, and audit each updated value in a separate row. By applying logic and techniques that I've demonstrated in the past, such as unpivoting data, you can handle all these obstacles.