Instant Doc #46713: Auditing Advice

Last Post 04 Jan 2006 05:48 AM by BurKaZoiD. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
BurKaZoiD
New Member
New Member

--
03 Jan 2006 10:43 AM
I found a problem with the 'update' part of the trigger. Say for instance we are using a varchar(11) as a unique primary key, and the primary key itself is changed (which can happen, SSN#, ISBN, etc). In this case an audit header row will be written, but there will be no audit detail rows because of the failure to match up primary keys in @inserted and @deleted. Is there anything anyone can suggest to work around this?

mwesch
New Member
New Member

--
03 Jan 2006 06:45 PM
Think you need to explain more about your audit logging algorithm for any help. What sort of detail rows are you referring to?
BurKaZoiD
New Member
New Member

--
04 Jan 2006 05:48 AM
Yes, my initial post is quite brief. I don't know how to explain the whole situation without boring you to tears, but I will try.

A sql2k database I now maintain currently performs hand-rolled audits (i.e., no triggers) to store a complete copy of the pre-modified, and post-modified row data into one single row in an audit table. There is one of these audit tables for each source table in the database (around 60+). It's a nightmare to maintain this handrolled code, especially when database objects are changed, so, based on Instant Doc #46713 I prepared an alternative to present to the powers that be. I modified the table design in the Instant Doc to use the same detail table for inserts, updates, and deletes, so there is now an "audit header" table, and an "audit detail" table with columns for pre-modified and post-modified data values. The table design looks like so:


CREATE TABLE [dbo].[AuditDetail] (
[AuditDetailId] [int] IDENTITY (1, 1) NOT NULL ,
[HeaderId] [int] NOT NULL ,
[KeyColumn] [sql_variant] NOT NULL ,
[ColumnName] [varchar] (128) NOT NULL ,
[OldValue] [sql_variant] NULL ,
[NewValue] [sql_variant] NULL
);

CREATE TABLE [dbo].[AuditHeader] (
[AuditHeaderId] [int] IDENTITY (1, 1) NOT NULL ,
[DateAndTime] [smalldatetime] NOT NULL ,
[LoginName] [varchar] (128) NOT NULL ,
[App] [varchar] (128) NOT NULL ,
[Host] [varchar] (128) NOT NULL ,
[TableName] [varchar] (128) NOT NULL ,
[StatementType] [char] (1) NOT NULL ,
[RowCount] [int] NOT NULL
);

So now, instead of every source table having its own audit table, there are two audit tables (header & detail) that are shared, and there are appropriate triggers on each source table to determine which columns were changed, and that information is then stored in the above audit tables.

I did a presentation of this with a working example for my department. An associate remarked that the handrolled auditing method in place is superior, and that based on the above design "if primary key values are changed, the audit will fail to be written", which is true. However, the handrolled method is just as faulty since, in it's current design, there is no way to log who performs a delete. I tried dutifully to explain that our end users would not be able to change primary keys, and that the only person who could do so would be a db admin. Moreover, I explained that the fact that not being able to match up 'inserted' and 'deleted' rows in a trigger (due to a primary key update) was simply the nature of the beast, and the best we could do was "program out" the ability to do this by the end user.

Anyway, long story short, I'm just seeking the opinion of other knowledgeable sql professionals. Is there any way around the scenario off audit details being lost due to primary key update? For my particular situation, using an IDENTITY as a surrogate key isn't an option.
mwesch
New Member
New Member

--
04 Jan 2006 02:20 PM
Okay, I understand your issue now. I don't have a perfect solution, but will offer a few comments that might help you.

(1) Your proposed audit method, which I now recall from the magazine article will be able to log audit detail records, although they will appear as a series of inserts and a series of deletes instead of as updates. As you indicate, a trigger won't be able to link the inserts to the deletes through the inserted/deleted tables.

(2) You could still eliminate much of your hand-rolled audit code and leave only hand-rolled code for logging updates. This reduces the number of lines of code, but not necessarily the number of code objects.

(3) Although there are issues with the proposed solution capturing updates to the primary key field, how often is the primary key field updated? And when it is updated how likely are those updates going to affect multiple records per update statement? You could never do this:

update MyTable
set Pkey = {NewValue}
where Pkey between {MinRange} and {MaxRange}

Doing so would violate key uniqueness. Although possible, I can't imagine many scenarios where you would do this:

update MyTable
set Pkey = Pkey + 10
where Pkey between {MinRange} and {MaxRange}

More likely, when updating a primary key value, you are probably updating a single record. You could add code into the trigger to check the value of @@rocount to see how many rows were changed by the triggering statement. If only 1 row was changed and there are records in both inserted and deleted tables, then it must be an update statement. This would be true even if the pkey values do not match between inserted and deleted. You could then recombine them into a single audit detail record.


Acceptable Use Policy
---