CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
markpcasey
markpcasey

Reputation: 559

SQL Server automatic update datetimestamp field

In SQL Server 2008 R2" I am trying to insert a formula in SQL Server that will update the current value in the LastUpdatedTimestamp field to now i.e. getdate() every time the record is updated.

Upvotes: 1

Views: 13469

Answers (1)

marc_s
marc_s

Reputation: 754598

You can have a default constraint on your DateTime field that will cause the current date/time to be inserted when you insert a new row.

From there on, you need to work with a AFTER UPDATE trigger that will update your date/time column each time the row is updated.

You cannot do this second task (updating a date/time stamp when updating the row) using a "formula" as you said - it just doesn't work that way in SQL Server.

You need to provide a trigger something along those lines:

CREATE TRIGGER trgYourTableUpdateTimestamp
  ON dbo.YourTable FOR UPDATE
AS BEGIN
   UPDATE 
      dbo.YourTable 
   SET 
      YourTimeStampColumn = GETDATE()
   FROM 
      Inserted Ins
   WHERE
      dbo.YourTable.SomeUniqueId = Ins.SomeUniqueId
END

Upvotes: 8

Related Questions