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.