Hi Wendy!

I'll give you a general answer and if you'd like a more specific one post DDL for the main table and I (or someone else) will see what we can do.

Yes a trigger can be used but there's also an advanced auditing feature available in SQL Server which I can't offhand remember the name of. Personally I wouldn't set up the audit table to be a mirror of the main table because you'll constantly be storing lots of data that hasn't changed.

Instead, I'd set up a table with these fields:

- Table name

- Column name (that changed)

- Date/time of change

- User making the change

- Old value

- New value

Old and new values could be a large VARCHAR (or NVARCHAR) that covers the largest width field you've got to deal with.

In the UPDATE trigger, you can use the 2 pseudo tables INSERTED and DELETED to identify what fields have changed and then use CROSS APPLY VALUES to UNPIVOT (see the first article in my signature links for details on this) the changed fields into the Old and New Value columns in the audit table. Don't forget that when you write the trigger, a bulk update (multiple rows) returns multiple rows in the pseudo tables, and the trigger only fires once for each SQL UPDATE (or MERGE). You'll also need to remember to CAST or CONVERT certain field types to the VARCHAR format you want (like for DATETIME to make it easier to unravel later).

All that sounds complicated maybe, but it is really quite simple once you see it.