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?

Answer 1

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

Answer 2

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.

Answer 3

do this...

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