I'm design a new db schema for a SQL Server 2012 database.
Each table should get two extra columns called
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.
created column is simple - just a
DATETIME2(3) column with a default constraint that gets set when a new row is inserted:
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.
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....
CREATE TRIGGER updateModified
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.