Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
+1 I want to add that this feature is available only on the Enterprise, Developer, and Evaluation editions of SQL Server 2008 and later –  garik Jan 26 '11 at 18:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.