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 sidebar, page 25, 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:
- an UPDATE that attempts to modify the primary key
- a DELETE that attempts to delete more than one row
- any INSERT issued before 8:00 am or after 5:00 pm
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.
Prev. page
[1]
2
next page 
