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

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.

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.