Audit Triggers SQL 2000

In the past I have used a poor mans audit trigger for my SQL tables. I basically created another identical table with "_Audit" at the end of it with the same fields. I simply did a trigger like this to copy the entire contents of the Row being updated, deleted, or Inserted.

CREATE TRIGGER fileAudit ON [dbo].[Table1]
FOR INSERT, UPDATE, DELETE
AS
insert Table1_audit
select
*
from inserted i
return

This is bad in several ways and I know that, but it was giving me Audit history that was better than nothing. The one thing I hate most about the above approach is that it copies the enitre record set even if only one field was updated or deleted and the fact I have an audit table for every table in the database.

I want to enhance my Audit Trail to include only one Audit_Table. I would like to mimic this approach that is using CLR for SQL 2005. My Only problem is I am forced to use SQL 2000 for legacy application on the database server. I do not have the ability to migrate to SQL 2005 at this time.

http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

All of my Tables have an Auto-increment PK

Could some one point me in the right way to accomplish this.

I recommend to use the following structure;

TableName NVarchar(1000)

OperationDateTime DateTime

Operation smallint (1-Insert, 2-Update, 3-Delete)

Data Text (Common Delimited or XML value of the affected row)

The real challenge here is getting back – querying - the data. For that probably you can design your own UI where you can parse these values for display.

But again these audit trail operations are additional overhead on your data manipulation.

|||

TableName NVarchar(1000)

OperationDateTime DateTime

Operation smallint (1-Insert, 2-Update, 3-Delete)

Data Text (Common Delimited or XML value of the affected row)

Structure should depends on the purposes of audit. Because using this structure it is possible that you could never answer the question who changed some order or what have done John with another orders.
|||

don't use the '*' for auditting or you have to be very carefull of the datatype. you can't read timestamps, text, ... columns from the virtual inserted and deleted tables...

regards

skafever