Posted by dmarkle on 01/15/07 15:43

Rico:

There are 2 pseudo-tables inside an update trigger, called "inserted"
and "deleted". You can deduce through the values in these tables what
has changed. Updates look like a "delete" and an "insert" in your
triggers.

I'm not so sure about this design, though. In the long run, you may
find it to be quite limiting. I usually recommend against doing
auditing on the database level whenever I can -- I prefer to audit the
actions that users do in the application layer. Not to mention the
fact that I think your design will be slow and difficult to query when
the time comes to use it.

-Dave


Rico wrote:
> Hello,
>
> I'm creating an audit table and associated triggers to be able to capture
> any updates and deletes from various tables in the database. I know how to
> capture the records that have been updated or deleted, but is there any way
> that I can cycle through a changed record, look at the old vs new values and
> capture only the values that have changed?
>
> To give you a better idea of what I'm trying to do, instead of creating a
> copy of the original table (some tables have many fields) and creating a
> whole record if a type or bit field has been changed, I'd like to only
> capture the change in a single audit table that will have the following
> fields;
>
> AuditID int INDENTITY(1,1)
> TableName varchar(100)
> FieldName varchar(100)
> OldValue varchar(255)
> NewValue varchar(255)
> AuditDate datetime DEFAULT(GetDate())
>
> Any direction would be greatly appreciated.
>
> Thanks!
> Rick

[Back to original message]