Want date/time of every change to row in SQL Server table

I am using SQL Server Express 2008 and have a table where I want to know the date/time when the rows in the table are modified/added.  Do I need to use a datetime field with a trigger to do this or is there a better way?  Either way I would like an example to follow.
canuckconsultingAsked:
Who is Participating?
 
Richard QuadlingConnect With a Mentor Senior Software DeveloperCommented:
If the intent is to have a physical datetime to compare against, then a datetime2 column is required and using an insert/update trigger is the way to go (http://msdn.microsoft.com/en-us/library/bb677335.aspx).

Alternatively, this sort of question can often mean "I want to know what has changed since the last time I looked". In this case the rowversion type may be better suited (http://msdn.microsoft.com/en-us/library/ms182776.aspx).

This is a completely automatic column. No trigger needed.

All you need to record is the last rowversion. At the start of the process in which you want to find the changes, you can use the @@DBTS variable (http://msdn.microsoft.com/en-us/library/ms187366.aspx) to find what the current rowversion number is and compare that with the previously stored rowversion.

Now, you can find what rows have changed between the two rowversion numbers.

If you put your work in a transaction, then you will get stale data if a row is altered whilst you are looking at the rows that have been previously tagged.

In this case it is up to you to decide to in a transaction or not.

It depends upon what you intend to do with the data.
0
 
mimran18Connect With a Mentor Commented:
0
 
Richard QuadlingSenior Software DeveloperCommented:
Please also read http://msdn.microsoft.com/en-us/library/bb839514.aspx regarding MIN_ACTIVE_ROWVERSION, which explains the issues with using rowversion data in transactions and blocks of changes.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.