https://www.mssqltips.com/sqlservertip/4024/sql-server-trigger-after-update-for-a-specific-value/

September 10, 2021

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".

Create the table and insert example data

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