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.
Best Answer
The
created
column is simple - just aDATETIME2(3)
column with a default constraint that gets set when a new row is inserted:So when you insert a row into
YourTable
and don't specify a value forCreated
, 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 theAFTER UPDATE
case and update it - you cannot declaratively tell SQL Server to do this for you....and then
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 amodified
column in.