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

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.

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.

StackExchange Database Administrators Q#888, answer score: 9