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 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 2017
download SQL Server 2016
download SQL Server 2014



Log Price Changes using SQL Server Trigger in SQL Server 2008

In this sql tutorial, I want to give SQL Server trigger example in SQL Server 2008 R2.
SQL developers can also use this SQL trigger example code with a minor change in MS SQL Server 2008 and in MS SQL Server 2005.
Within this sql trigger tutorial, you will find Create Trigger in SQL Server script, where you can use the syntax in any SQL Server version.

In this SQL Server trigger example, the aim of the sql trigger is to keep history of changes on price column in a table.
When a new record is inserted the SQL Server trigger will also create a new row in sql log table.
When the data row is updated in the sql table, sql trigger will check if certain column is also updated. Here in this example, sql trigger will check if price column is updated. If certain column is updated or changed, sql trigger will also update the log table.
If data row in main sql database table is deleted, then log table will also be updated to reflect this sql delete operation in log table.

T-SQL developers will realize that in the t-sql codes of this SQL Server trigger sql variable @TimeStamp has been assigned to GETDATE() value by default in the sql variable syntax.
This t-sql feature is a new t-sql enhancement in MS SQL Server 2008. T-SQL syntax enables developers to define a sql variable and assign a default value at the same time in Microsoft SQL Server 2008.
In order to run successfully this sql trigger in SQL Server 2005, the sql variable definition and value assignment to it should be done in separate lines.





SQL Trigger Example to Log Data Changes

Now sql developers, we can continue this sql trigger tutorial with some T-SQL code. The scenario is build on to keep track changes on Price column of Books table.
In Books sql database table, data about each book entity is stored.
The log table also stored the change date and time.

Here sql developers will find the sql database design and sql table create scripts.
T-SQL developers who want to continue with examples can run below sql codes before Create Trigger script.

Create Table Books (
 BookId int identity(1,1),
 CategoryId smallint,
 Title nvarchar(1000),
 Author nvarchar(1000),
 Price decimal(18,2),
 Stock_Quantity smallint
)
GO
Create Table Books_Price_Log (
 Id int identity(1,1),
 BookId int,
 Price decimal(18,2),
 Valid_From datetime,
 Valid_To datetime
)
GO

Now here is the SQL Server trigger code which will be defined on Books sql table.
Since the trigger will execute after INSERT, UPDATE or DELETE sql statements are executed on Books table, t-sql developers will create AFTER INSERT, UPDATE, DELETE sql trigger.

Note that it is a common mistake, new t-sql developers think that this sql trigger will only work after a single row or a single record is inserted, updated or deleted.
In this case, whenever a mass update, or an INSERT statement which inserts more than 1 row, or when more than 1 record is deleted, the trigger will not work as desired.
Note that the following SQL Server trigger code is designed considering this issue and will work succesfully with DML statements affecting more than 1 row.

CREATE TRIGGER dbo.Log_Books_Price_Changes
 ON dbo.Books
AFTER INSERT, UPDATE, DELETE
AS

IF NOT EXISTS(
 SELECT * FROM Deleted
)
 INSERT INTO Books_Price_Log (
  BookId, Price, Valid_From
 )
 SELECT
  BookId, Price, GETDATE()
 FROM Inserted
ELSE IF NOT EXISTS (
 SELECT * FROM Inserted
)
 UPDATE Books_Price_Log
 SET
  Valid_To = GETDATE()
 FROM Books_Price_Log L
 INNER JOIN Deleted ON Deleted.BookId = L.BookId
 WHERE Valid_To IS NULL
ELSE
 BEGIN
  Declare @TimeStamp Datetime = GETDATE()
  UPDATE Books_Price_Log
  SET
   Valid_To = @TimeStamp
  FROM Books_Price_Log L
  INNER JOIN Deleted ON Deleted.BookId = L.BookId
  INNER JOIN Inserted
   ON Inserted.BookId = Deleted.BookId
   AND Inserted.Price <> Deleted.Price
  WHERE Valid_To IS NULL

  INSERT INTO Books_Price_Log (
   BookId, Price, Valid_From
  )
  SELECT
   Inserted.BookId, Inserted.Price, @TimeStamp
  FROM Inserted
  INNER JOIN Deleted
   ON Inserted.BookId = Deleted.BookId
   AND Inserted.Price <> Deleted.Price

 END
GO

After executing above Create Trigger in SQL 2008, now t-sql developers can test SQL Server AFTER INSERT, UPDATE, DELETE sql trigger using below DML (Data Manipulation Language) commands.

First let's insert sample data into sql table Books, and view what happened on main table and in log table.

Insert Into Books (
 CategoryId, Title, Author, Price, Stock_Quantity
) VALUES (
 1,
 N'Microsoft SQL Server 2008 Internals',
 N'Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, Conor Cunningham, Adam Machanic',
 37.79, 40
)

And let's see what has happened on sql tables.

sql-server-trigger-sample-data-in-log-table

The main sql table Books has the data row we inserted.
The sql log table Books_Price_Log has the data inserted by sql trigger.

The below t-sql code block will run within the SQL trigger code example when an SQL INSERT statement execute on Books sql table.
The sql codes are decided by existence of DELETED rows in sql trigger.
If DELETED rows do not exist during sql trigger execution, this means an INSERT command is being executed.

IF NOT EXISTS(
 SELECT * FROM Deleted
)
 INSERT INTO Books_Price_Log (
  BookId, Price, Valid_From
 )
 SELECT
  BookId, Price, GETDATE()
 FROM Inserted

Here is an other sample sql data which we will use in this sql trigger tutorial.

Insert Into Books (
 CategoryId, Title, Author, Price, Stock_Quantity
) VALUES (
 1,
 N'Inside Microsoft SQL Server 2008: T-SQL Querying',
 N'Itzik Ben-Gan, Lubor Kollar, Dejan Sarka, Steve Kass',
 31.49, 50
)

sql-trigger-tutorial-sample-data

T-SQL developers can see that the data in Books table executed the sql trigger. And sql trigger create an other row in log table without affecting the previous log data.

Let's now assume that we sell 1 item from each book title. So we will update the Stock Quantity sql field to decrease it by 1.

Update Books Set Stock_Quantity = Stock_Quantity - 1

You will realize that since Price sql column field is not updated, the SQL trigger will not insert a log record into sql database table Books_Price_Log.
Now let's check which t-sql code block is executed and how we can decide an sql field is updated or not.

Since when an sql UPDATE command is executed, the SQL Server trigger will have two logical tables INSERTED and DELETED which store the values before table rows are updated and after rows are updated.
So the IF NOT EXISTS(SELECT * FROM Deleted) condition will not work.
It is the same case for IF NOT EXISTS(SELECT * FROM Inserted) condition. This code block will not run either.
Since both INSERTED and DELETED sql triggers exist the ELSE code block will be processed during the trigger execution after an sql UPDATE command executes on main database table Books.

BEGIN
 Declare @TimeStamp Datetime = GETDATE()
 UPDATE Books_Price_Log
 SET
  Valid_To = @TimeStamp
 FROM Books_Price_Log L
 INNER JOIN Deleted ON Deleted.BookId = L.BookId
 INNER JOIN Inserted
  ON Inserted.BookId = Deleted.BookId
  AND Inserted.Price <> Deleted.Price
 WHERE Valid_To IS NULL

 INSERT INTO Books_Price_Log (
  BookId, Price, Valid_From
 )
 SELECT
  Inserted.BookId, Inserted.Price, @TimeStamp
 FROM Inserted
 INNER JOIN Deleted
  ON Inserted.BookId = Deleted.BookId
  AND Inserted.Price <> Deleted.Price
END

In the above t-sql script, the INNER JOIN between INSERTED and DELETED tables with "Inserted.Price <> Deleted.Price" condition checks whether Price field is updated or not.

Let's update the price field of a book.

Update Books Set Price = 35 Where BookId = 1

sql-trigger-in-order-to-log-specific-updated-column-value

You see since the price has been altered, the validity of previous price for related book or books has ended at the execution time.
So in log table the previous price entry is updated for the Valid_To field.
And a new log entry has been inserted indicating the new price validity start date.
Note that execution time has been set using @TimeStamp sql datetime variable which is assigned to GETDATE() value.

Up to now, we have only updated single rows in the sql table Books.
Let's now decrease the value in sql field price by 20% for all records or for all rows in the table and check how will the SQL Server trigger Log_Books_Price_Changes work.

Update Books Set Price = Price * 0.8

As you see in the below output of two sql tables Books and Log_Books_Price_Changes, we have two new rows with Valid_From date equals to current datetime value. The price in the new sql rows are equal to new prices indicating the decrease in price of the books.
The previous two sql rows which have Valid_To sql field value equal to current date and time indicate the previous price end of validity date.

sql-server-trigger-after-update-multiple-rows

So we can say that our sql trigger in SQL Server 2008 seems to be working successfully for multiple rows updates as well as single row updates in sql database table.
So the create trigger script is successfull for multiple row after update statements.

We have one single test to see our sample Sql Server trigger in action.
Let's test how the sql trigger will work when we delete all rows from Books sql table.
This means the end of last valid price entries in the sql log table.

Delete from Books

And the following screenshot is showing that there is no sql row with Valid_To date is NULL.
This is because, in the main sql table Books, there are no Books left. So actually there can not be a still valid price for non existing books.
And all these updates are managed by the above SQL Server trigger whose t-sql codes are given.

sql-trigger-example-in-sql-server-in-delete-action

I hope t-sql trigger developers will benefit from this sql tutorial with the given example case.
For more information about SQL Server triggers, AFTER INSERT, UPDATE, DELETE trigger for DML commands and another example case please refer to sql tutorial SQL Trigger Example in SQL Server 2008.






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









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