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

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.

Solution 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.

Solution 2:

Generally, you can have the following columns:

• LastModifiedBy
• LastModifiedOn
• CreatedBy
• CreatedOn

where LastModifiedBy and CreatedBy are references to a users table (UserID) and the LastModifiedOn and CreatedOn columns are date and time columns.

You have the following options:

1. Solution without triggers - I have read somewhere that "The best way to write triggers is not to write such." and you should know that generally they are hurting the performance. So, if you can avoid them it is better to do so, even using triggers may look the easiest thing to do in some cases.

   So, just edit all you INSERT and UPDATE statements to include the current UserID and current date and time. If such user ID can not be defined (anonymous user) you can use 0 instead and the default value of the columns (in case no user ID is specified will be NULL). When you see NULL values are inserted you should find the "guilty" statements and edit it.

2. Solution with triggers - you can created AFTER INSERT, UPDATE trigger and populated the users columns there. It's easy to get the current date and time in the context of the trigger (use GETUTCDATE() for example). The issue here is that the triggers do not allowed passing/accepting parameters. So, as you are not inserting the user ID value and you are not able to pass it to the trigger. How to find the current user?

   You can use SET CONTEXT_INFO and CONTEXT_INFO. Before all you insert and update statements you must use the SET CONTEXT_INFO to add the current user ID to the current context and in the trigger you are using the CONTEXT_INFO function to extract it.

So, when using triggers you again need to edit all your INSERT and UPDATE clauses - that's why I prefer not to use them.

Anyway, if you need to have only date and time columns and not created/modified by columns, using triggers is more durable and easier as you are not going to edit any other statements now and in the future.

With SQL Server 2016 we can now use the SESSION_CONTEXT function to read session details. The details are set using sp_set_session_context (as read-only or read and write). The things are a little bit user-friendly:

EXEC sp_set_session_context 'user_id', 4;  
SELECT SESSION_CONTEXT(N'user_id');  

A nice example.

Solution 3:

Attention, above works fine but not in all cases, I lost a lot of time and found this helpfull:

create TRIGGER yourtable_update_insert
ON yourtable
AFTER UPDATE 
as
begin
   set nocount on;
   update yourtable set modified=getdate(), modifiedby = suser_sname()
   from  yourtable t 
   inner join inserted i on t.uniqueid=i.uniqueid 
end
go

set nocount on; is needed else you get the error:

Microsoft SQL Server Management Studio

No row was updated.

The data in row 5 was not committed. Error Source: Microsoft.SqlServer.Management.DataTools. Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows).

Correct the errors and retry or press ESC to cancel the change(s).

OK Help

Solution 4:

CREATE TRIGGER [dbo].[updateModified]
   ON  [dbo].[Transaction_details] 
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.Transaction_details
    SET ModifedDate = GETDATE() FROM dbo.Transaction_details t JOIN inserted i ON 
    t.TransactionID = i.TransactionID--SYSDATETIME()
END