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.

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

Answer 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

---

Answer 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

Answer 5

One important thing to consider is that you should always have the inserted / updated time for all of your tables and rows be from the same time source. There is a danger - if you do not use triggers - that different applications making direct updates to your tables will be on machines that have different times on their clocks, or that there will not be consistent use of local vs. UTC in the application layer.

Consider a case where the system making the insert or update query that directly sets the updated / modified time value has a clock that is 5 minutes behind (unlikely, but worth considering) or is using local time versus UTC. If another system is polling using an interval of 1 minute, it might miss the update.

For a number of reasons, I never expose my tables directly to applications. To handle this situation, I create a view on the table explicitly listing the fields to be accessed (including the updated / modified time field). I then use an INSTEAD OF UPDATE, INSERT trigger on the view and explicitly set the updatedAt time using the database server's clock. This way I can guarantee that the timebase for all records in the database is identical.

This has a few benefits:

1. It only makes one insert to the base table and you don't have to worry about cascading triggers being called
2. It allows me to control at the field level what information I expose to the business layer or to other consumers of my data
3. It allows me to secure the view independently from the base table

It works great on SQL Azure.

Take a look at this example of the trigger on the view:

ALTER TRIGGER [MR3W].[tgUpdateBuilding] ON [MR3W].[vwMrWebBuilding]
INSTEAD OF UPDATE, INSERT AS
BEGIN

SET NOCOUNT ON

IF EXISTS(SELECT * FROM DELETED)
BEGIN
    UPDATE [dbo].[Building]
       SET 
          ,[BuildingName] = i.BuildingName
          ,[isActive] = i.isActive
          ,[updatedAt] = getdate()
     FROM dbo.Building b
     inner join inserted i on i.BuildingId = b.BuildingId
END
ELSE
BEGIN
INSERT INTO [dbo].[Building]
           (
            [BuildingName]
           ,[isActive]
           ,[updatedAt]
           )

           SELECT 
               [BuildingName]
              ,[isActive]
              ,getdate()
              FROM INSERTED
    END
END

I hope this helps, and I would welcome comments if there are reasons this is not the best solution.

Answer 6

This solution might not work for all use cases but wherever possible its a very clean way. Create an stored procedure for inserting/updating row in table and only use this sp for modifying the table. In stored procedure you can always set created and updated column as required. e.g. setting updatedTime = GetUTCTime()