What's the best way to track Updated By, Updated On

Question

In MS SQL Server, what's the best way to update an Updated By, Updated On set of fields on a table? I've seen it done it triggers, in the code. etc. The upside I've seen to triggers is that it all happens in the same place. On the downside there are occasions when an administrator has to bulk fix a table and doesn't want to obliterate the username/time of the last user update.

Note: I don't want a timestamp. I want the windows user ID and a human readable date/time of the last change.

Looking for what you recommend and WHY it's a good choice.

Answer 1

1) Your trigger idea is good: we use it sometimes.

It is foolproof mostly except:

• issues you mentioned
• you don't always have SUSER_SNAME if folk connect via a web server or middle tier

You can mitigate this with SET CONTEXT_INFO to control admin behaviour, or more carefully to pass in some end user details.

2) All writes via stored procedures. We use this method mostly.

We also use defaults on UpdatedOn so...

UPDATE
   ...
   UpdatedBy = whoever, UpdatedOn = DEFAULT

3) Let your client do it. I won't take this any further...

Finally, we implemented "AffectedBy", and "AffectedOn" fields to track admin/indirect updates.

Answer 2

In SQL 2008 there is a much silent way to check all the modifications done on a table, on specified fields. Though it will not show the user who did it. SQL does something good but forgets to complete it in order to be really good! The feature is named change data capture. the cdc feature must be enabled on the database, than on a certain table for certain fields. After that one new table will be created for changes to be followed and a job which will keep the changes as long as you need. This is customizable by you. Check this for more details. Personally I hate triggers because of many locks and deadlocks cause by these.