sql - 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

I suppose you mean a default constraint instead of a computed column.

If so, that's OK. I prefer to do it like that. One possible disadvantage is that a client can override the default value, but if you don't trust the client, you might as well wrap table access in stored procedures.

Triggers will work too, but triggers tend to generate extra complexity. Coding errors that include triggers are among the hardest database issues out there.

Answer 2

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 3

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()