getting last modified time in sql

I have a datetime column in sql2005. it's default value is getdate(). Ho开发者_运维问答w can I update it's value automatically when others value update?

Use a trigger on the table!

CREATE TRIGGER updateDate
ON dbo.Table
AFTER UPDATE 
AS 
UPDATE Table
SET ModifiedDate = GetDate() -- or sysdatetimeoffset()
where table.Id = inserted.Id

You can use timestamp variable, basicly it updates itself everytime the row is changed and you can have only one timestamp variable per specific table.

Take a look at: http://msdn.microsoft.com/en-us/library/aa260631(SQL.80).aspx

You can make a trigger for this, but depending on how often you update it could be cumbersome.

Are most of your updates batch updates or individual records? Is it ad-hoc or done through a stored proc? If it is a stored proc and/or batch updates, it may be more performant to declare a variable for the current datetime, and use that to update this value.

Simply create a trigger.

After update, set this value to getdate(). I currently can't find some articles about the syntax, but I'm sure you can do better ;)

This is base of SilverSkin answer. I just update his statement to include the necessary JOIN.

CREATE TRIGGER updateDate
ON dbo.Table
AFTER UPDATE 
AS 
UPDATE Table
SET ModifiedDate = GetDate() -- or sysdatetimeoffset()
JOIN Table t JOIN Inserted i ON t.Id = i.Id

This is a syntax for SQL Server 2005.