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

Code

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

Code

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

)

Code

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

Code

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

)

Code

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

Code

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

Code

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

Code

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

Code

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

Code

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.