How do I add a “last modified” and "created" column in a SQL Server table?

Question

I'm design a new db schema for a SQL Server 2012 database.

Each table should get two extra columns called modified and created which should be automatically change as soon a row gets inserted or updated.

I don't know how rather the best way to get there.

I assuming that trigger are the best way to handle it.

I was trying to find examples with triggers.. but the tutorials which I found insert data in another table etc.

I assumed it's a quite common scenario but I couldn't find the answer yet.

Answer 1

The created column is simple - just a DATETIME2(3) column with a default constraint that gets set when a new row is inserted:

Created DATETIME2(3) 
   CONSTRAINT DF_YourTable_Created DEFAULT (SYSDATETIME())

So when you insert a row into YourTable and don't specify a value for Created, it will be set to the current date & time.

The modified is a bit more work, since you'll need to write a trigger for the AFTER UPDATE case and update it - you cannot declaratively tell SQL Server to do this for you....

Modified DATETIME2(3)

and then

CREATE TRIGGER updateModified
ON dbo.YourTable
AFTER UPDATE 
AS
   UPDATE dbo.YourTable
   SET modified = SYSDATETIME()
   FROM Inserted i
   WHERE dbo.YourTable.PrimaryKey = i.PrimaryKey

You need to join the Inserted pseudo table which contains all rows that were updated with your base table on your primary key for that table.

And you'll have to create this AFTER UPDATE trigger for each table that you want to have a modified column in.