sqlsql-serversql-server-2005-express

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

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)

Solution

  • 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.