How to implement automatic timestamps for record changes (SQL Server)

Question

I am used to using "changed_at" fields in my MySQL database that Ruby on Rails automatically updates when changing a record.

Now I am using ASP.NET MVC with SQL Server 2008 and I am wondering, how would I proceed to get the same functionality. Does SQL Server have an option for auto updating a field when updating?

Solution 1

The "timestamp" data type gives you a binary value that automatically gets updated every time your field changes, but it won't give you a nice date/time value.

Solution 2

You could use a trigger or just update it in an sp that does the update.

Solution 3

do this...

UPDATE YourTable
    SET Column1=@...
        ,@Column2=@....
        ,@Column3=@...
        ,changed_at=GETDATE()
    WHERE ...

Comments

[KM.]

you'll have to update the row an extra time if you want a trigger to do it because you can't update INSERTED. Just set the value when you update the other columns and be done with it.

[Ankit Soni]

I would agree with updating the field when you are updating the rest of the record, but a trigger can be used to accomplish the "auto" update of the field.