SQL Server 2005 - ModifyDate column - Is using a Computed Column a correct way to implement this?

Question

I just want to have a "ModifyDate" column automatically populated for me when a record is either inserted or updated.

Should I be using triggers or is it OK to use a Computed Column?

(Using SSMS2005 and SQL Server 2005 Express)

Answer 1

The INSERTed part is easy - just define a default value on that column, and when you insert a new row, do not specify a value for that column.

The UPDATE part is trickier - you will have to write a trigger that fires ON UPDATE, and then updates that column. I don't think there's any other way to do this in SQL Server.

Question is: do you really need the actual calendar date? If not, if you only want to have a "marker" as to whether or not a row has changed, check out the ROWVERSION column type (formerly known as TIMESTAMP) instead.

I don't really see how you could use a computed column for this - you have to store that date somewhere, and keep it current with INSERT and each subsequent UPDATE - only a trigger will manage to do this.

Answer 2

I would suggest staying away from triggers and set the Date Modified column with a stored procedure using the server getDate function.

Example:

Update Customers Set FirstName = 'Jim', LastName = 'Jones', DateModified = getDate()