Some database tables include a “last modified” column, which stores the date and time that the row was last updated. Each time the row is updated, the date is updated to reflect the date and time of that update.
In SQL Server, you can use a trigger to perform this update.
A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server.
You can use the CREATE TRIGGER statement to create a trigger when using T-SQL. This statement can be used to create a DML, DDL, or logon trigger.
Example
The following code creates a table, as well as a trigger that updates the ModifiedDate column whenever there’s an update.
CREATE TABLE dbo.Books (
BookId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
BookName nvarchar(1000) NOT NULL,
CreateDate datetime DEFAULT CURRENT_TIMESTAMP,
ModifiedDate datetime DEFAULT CURRENT_TIMESTAMP
);
CREATE TRIGGER trg_Books_UpdateModifiedDate
ON dbo.Books
AFTER UPDATE
AS
UPDATE dbo.Books
SET ModifiedDate = CURRENT_TIMESTAMP
WHERE BookId IN (SELECT DISTINCT BookId FROM inserted);Insert a Row
Let’s insert a row and select it to see the result of the DEFAULT constraint:
INSERT INTO Books (BookName)
VALUES ('Trigger Happy');
SELECT * FROM Books;Result (using vertical output):
-[ RECORD 1 ]------------------------- BookId | 1 BookName | Trigger Happy CreateDate | 2020-08-17 23:33:15.230 ModifiedDate | 2020-08-17 23:33:15.230
This is displayed using vertical output in order to make it easier to read (so that you don’t have to scroll sideways to read all columns).
In this case, both the CreatedDate and ModifiedDate columns contain the same value. However, if the row is updated, the ModifiedDate‘s value should change. That’s why we created the trigger.
Update the Row
Now let’s update the row and select the results.
UPDATE Books
SET BookName = 'Trigger Hippy'
WHERE BookId = 1;
SELECT * FROM Books;Result (using vertical output):
-[ RECORD 1 ]------------------------- BookId | 1 BookName | Trigger Hippy CreateDate | 2020-08-17 23:33:15.230 ModifiedDate | 2020-08-18 00:07:39.680
As expected, the ModifiedDate column is updated but the CreateDate column remains the same.
Further Explanation of the Code
Below is a more detailed explanation of the code used to create the table and its associated trigger.
The Table
The following code creates the table:
CREATE TABLE dbo.Books (
BookId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
BookName nvarchar(1000) NOT NULL,
CreateDate datetime DEFAULT CURRENT_TIMESTAMP,
ModifiedDate datetime DEFAULT CURRENT_TIMESTAMP
);The ModifiedDate column has a DEFAULT constraint which sets the default value to CURRENT_TIMESTAMP (as does the CreateDate column).
This means that when the row is first inserted, the CURRENT_TIMESTAMP is inserted into those columns.
This is fine for the initial insert, but it doesn’t cater for any subsequent updates. That’s where the trigger comes in.
The Trigger
The following code creates the trigger:
CREATE TRIGGER trg_Books_UpdateModifiedDate
ON dbo.Books
AFTER UPDATE
AS
UPDATE dbo.Books
SET ModifiedDate = CURRENT_TIMESTAMP
WHERE BookId IN (SELECT DISTINCT BookId FROM inserted);In this case, I called the trigger trg_Books_UpdateModifiedDate.
I created it on the dbo.Books database, and it runs after every UPDATE.
When it runs, it updates the ModifiedDate column to the CURRENT_TIMESTAMP (but only on the row that was updated, of course).
I’m able to determine which row was updated by checking the inserted table. The inserted table is a temporary, memory-resident table that SQL Server creates and maintains.
The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.
In addition to the inserted table, SQL Server also creates and maintains a deleted table. An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.
A Trigger for the “Date Created” Column
Using a DEFAULT constraint is a handy way to create the initial value, but you do run the risk that someone could directly update that value later on.
If you see this as an issue, you could modify the trigger to include the “Date Created” column, so that it resets it to its original value any time there’s an update to the row.
You can get the original value from the deleted table, seeing as the old rows are copied to this table first, before any updates take place.