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:
- Store the number of affected rows in the variable @rc and return if @rc = 0.
- Declare the @inserted and @deleted table variables and copy to them the content of inserted and deleted.
- Identify the type of statement that fired the trigger and store it in the @dmltype variable.
- Check for integrity violation and set the variables @failed, @abort, and @comment accordingly.
- If the trigger needs to fail the transaction, issue a rollback and raise an error.
- Insert a row to the audit header table.
- If the trigger needs to abort from the trigger, return.
- Insert the detail data rows to T1AuditInsDelDetail if the statement type is insert or delete and to T1AuditpdDetail if it's an update.
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.