tsql - Need a datetime column in SQL Server that automatically updates when the record is modified

Question

I need to create a new DATETIME column in SQL Server that will always contain the date of when the record was created, and then it needs to automatically update whenever the record is modified. I've heard people say I need a trigger, which is fine, but I don't know how to write it. Could somebody help with the syntax for a trigger to accomplish this?

In MySQL terms, it should do exactly the same as this MySQL statement:

ADD `modstamp` timestamp NULL 
    DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

Here are a few requirements:

• I can't alter my UPDATE statements to set the field when the row is modified, because I don't control the application logic that writes to the records.
• Ideally, I would not need to know the names of any other columns in the table (such as the primary key)
• It should be short and efficient, because it will happen very often.

Answer 1

SQL Server doesn't have a way to define a default value for UPDATE.

So you need to add a column with default value for inserting:

ADD modstamp DATETIME2 NULL DEFAULT GETDATE()

And add a trigger on that table:

CREATE TRIGGER tgr_modstamp
ON **TABLENAME**
AFTER UPDATE AS
  UPDATE **TABLENAME**
  SET ModStamp = GETDATE()
  WHERE **ID** IN (SELECT DISTINCT **ID** FROM Inserted)

And yes, you need to specify a identity column for each trigger.

CAUTION: take care when inserting columns on tables where you don't know the code of the application. If your app have INSERT VALUES command without column definition, it will raise errors even with default value on new columns.