SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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

store lastupdatdate on sql table

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.







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems