Last Update Date using SQL Trigger in SQL Server Database Table
It is important to keep last update date on a database table row to see the last time when the current record is updated. Database administrators and SQL developers generally put InsertDate and UpdateDate columns in table definitions during their CREATE TABLE scripts. I use SQL Serve Update Trigger or SQL Server Instead Of Update Trigger to keep last update date field up-to date.
Either for audit purposes or for database application requirements, last updated date is a valuable information for database professionals. But it is not always easy to maintain last update date for each row if your application is huge and complex containing many stored procedures which update target database tables.
Here in this SQL tutorial, I'll demonstrate a solution which is common among SQL developers and SQL Server administrators to kepp Last Updated Date field up-to-date. The solution of this sql problem benefits from use of SQL trigger (like SQL Update trigger and SQL Server Instead of Triggers - Instead of Update trigger).
OrderItems table is our sample database table in this SQL tutorial. Assume that it is important for sql developers or database application users to keep and see the last update date field up-to-date.
First use DEFAULT for the LastUpdateDate column with defining the default value GETDATE().
This default value will be assigned to each new row when they are inserted to the sample sql table OrderItems.
It is important to be aware that DEFAULT option will only work for a new record in database table.
So when an INSERT statement is executed, LastUpdateDate column values will be set to GETDATE() function value.
Create Table OrderItems (
OrderId int,
ItemId int,
ProductId int,
Quantity int,
LastUpdateDate datetime default GETDATE()
)
If you add new rows to our database table, you will see that the LastUpdateDate is populated with the transaction date value correctly.
insert into OrderItems (OrderId, ItemId, ProductId, Quantity) select 1,1,1,1
insert into OrderItems (OrderId, ItemId, ProductId, Quantity) select 1,2,2,2
insert into OrderItems (OrderId, ItemId, ProductId, Quantity) select 2,1,3,3
But this is half of the solution we are looking for. To fullfill the requirement, after an update statement the LastUpdateDate field should keep the UPDATE date. But if you execute an sql UPDATE command on one of the table rows for quantity for example, you will see that the OrderItems LastUpdateDate column value is unchanged.
SQL developers can solve the remaining problem only with using a SQL Server trigger. In fact a SQL Server Update Trigger can help us for solution. SQL Update triggers are executed when an UPDATE command is called on the database table.
For SQL Server UPDATE trigger, we have two options two use : SQL Server AFTER UPDATE trigger or SQL Server INSTEAD OF UPDATE trigger.
Let's start with SQL After UPDATE trigger codes. Create the sql trigger using the following t-sql create script.
CREATE TRIGGER tr_OrderItems_LastUpdateDate ON OrderItems AFTER UPDATE
AS
UPDATE OrderItems
SET LastUpdateDate = GetDate()
FROM OrderItems o
INNER JOIN Inserted i
ON o.OrderId = i.OrderId
AND o.ItemId = i.ItemId
GO
Now you can run sample UPDATE commands to trace the results if the LastUpdateDate column value is up-to-date
update OrderItems set Quantity = 5 where OrderId = 1 and ItemId = 2
If you run SELECT command on OrderItems table in our sql tutorial, you will see that LastUpdateDate field value is populated with transaction datetime value as required.
Now let's build an INSTEAD OF UPDATE SQL Server trigger solution. Before you continue with new SQL trigger, let's drop the current database trigger tr_OrderItems_LastUpdateDate.
DROP TRIGGER tr_OrderItems_LastUpdateDate
Create SQL Server INSTEAD OF UPDATE trigger executing the following t-sql command.
CREATE TRIGGER tr_OrderItems_LastUpdateDate ON OrderItems INSTEAD OF UPDATE
AS
UPDATE OrderItems
SET
OrderId = i.OrderId,
ItemId = i.ItemId,
ProductId = i.ProductId,
Quantity = i.Quantity,
LastUpdateDate = GetDate()
FROM Inserted i
INNER JOIN OrderItems o
ON o.OrderId = i.OrderId
AND o.ItemId = i.ItemId
GO
Now you can run example UPDATE commands over sample sql database table to see if the last updated date column is storing the latest update date and time value in it.