SQL Server Trigger After Update for a Specific Value

By: Dallas Snider   |   Comments (7)   |   Related: > Triggers

Problem

I need to create a SQL Server trigger that will execute when a column value is updated to a specific value. How can I do this?

Solution

In this tip, we show you how to write T-SQL statements that will create a SQL Server trigger that will execute after we update a column value to a specific value.

In the image below we see where we create our example table named tblTriggerTest with a primary key column named pkID, a date column named OrderApprovalDateTime and a varchar column named OrderStatus. We populate the table with three test records with the OrderApprovalDateTime set to NULL and the OrderStatus set to "Pending".

In this example, we want our trigger to populate the OrderApprovalDateTime with the current date provided by the getdate() function after the OrderStatus is updated to "Approved". The T-SQL for creating this trigger is shown below. It is very important to include the INNER JOIN to the INSERTED table so that only the updated rows are affected. Using the clause WHERE OrderStatus='Approved' by itself to limit the rows updated will actually result in all rows with an OrderStatus value of Approved being updated at the same time.

To test the trigger, we will execute a T-SQL UPDATE statement to set the OrderStatus value to "Approved" for the first row in the table (pkID = 1). After the T-SQL UPDATE command, we then execute a T-SQL SELECT query to make sure the trigger executed correctly. The output from the UPDATE and SELECT statements are shown below.

Notice above that only the first row had its OrderApprovalDateTime set to the current date, which is our desired behavior. However, we need to run a second test to ensure that the correct number of rows have been updated. In our second test, we will execute a T-SQL UPDATE statement to set the OrderStatus value to "Approved" for the second row in the table (pkID = 2). After selecting all records in the table, notice how the OrderApprovalDateTime for the second row does not equal the OrderApprovalDateTime for the first row. Again, this is our desired behavior and all appears well.

The complete code for this tip is in the box below.

use MSSQLTips
go

create table tblTriggerTest
(
  pkID integer Identity(1,1) primary key,
  OrderApprovalDateTime datetime,
  OrderStatus varchar(20)
)

insert into tblTriggerTest values (NULL, 'Pending')
insert into tblTriggerTest values (NULL, 'Pending')
insert into tblTriggerTest values (NULL, 'Pending')

select * from tblTriggerTest
go

create trigger trTriggerTest on tblTriggerTest
after update
as
begin
   set nocount on;
   update tblTriggerTest set OrderApprovalDateTime=getdate()
   from  tblTriggerTest t 
   inner join inserted i on t.pkID=i.pkID 
   and i.OrderStatus='Approved'
end
go

update tblTriggerTest set OrderStatus='Approved' where pkID=1
go

select * from tblTriggerTest
go

update tblTriggerTest set OrderStatus='Approved' where pkID=2
go

select * from tblTriggerTest
go

Next Steps

Take a few minutes to explore what happens when you change the INNER JOIN or add more columns to the table and trigger.

Also, check out more tips on triggers in SQL Server on MSSQLTips.com.

• Forcing Trigger Firing Order in SQL Server
• Prevent accidental update or delete commands of all rows in a SQL Server table
• Auditing when Triggers are Disabled or Enabled for SQL Server

About the author

Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips