SQL Server Trigger After Update for a Specific Value

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


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?


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.

Successfully create the trigger

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.

Successfully test the trigger

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.

Test the trigger again

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

use MSSQLTips

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

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

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

select * from tblTriggerTest

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

select * from tblTriggerTest
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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Dallas Snider 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

Comments For This Article

Thursday, November 9, 2023 - 7:13:24 PM - Baffour Back To Top (91745)
Can we do a conditional trigger for the User doing the update? So that particular user is ignored to appropriately ignore the application to truly update. Only store the values of users doing the update directly in the database.

Tuesday, July 30, 2019 - 9:19:59 AM - Doug Back To Top (81900)

Good example and it covers the situation at hand. However (don't you hate those) the analyst mind in me is saying, "What if I accidently approved this and need to change the status back to 'Pending'?" I would consider this a real-world issue and having a row with an approved date and a Pending status could present issues to the application and/or reporting.

I might consider something like this, and possibly expand on it as necessary. The and condition in the case expression prevents updating the timestamp again if something else in the row was updated and the status was already 'Approved':

   update tblTriggerTest 
set OrderApprovalDateTime = case when i.OrderStatus = 'Approved' and i.OrderStatus <> t.OrderStatus then getdate() when i.OrderStatus = 'Pending' then NULL else OrderApprovalDateTime end from tblTriggerTest t inner join inserted i on t.pkID=i.pkID;

Friday, March 23, 2018 - 5:09:09 PM - Gidećo Back To Top (75514)

 Hi  Dr. Dallas, thank from Brazil, this helped me in my job. Thanks.

Friday, September 11, 2015 - 4:08:40 PM - MarkB Back To Top (38658)

Also, although this particular example does not have an issue, I would also warn to always think / test with a "set" update mentality - don't test with only single row updates, even if you "know" that the update(s) in question comes from your app a row at a time....



Friday, September 11, 2015 - 8:49:48 AM - PhyData DBA Back To Top (38649)

Why did you SET NOCOUNT ON for a table update trigger?  I have never seen that in a trigger or trigger example code before.

Thursday, September 10, 2015 - 4:20:28 AM - Thomas Franz Back To Top (38634)

you should consider to add an 

and (t.OrderApprovalDateTime is null or t.OrderStatus <> i.OrderStatus) -- The exact condition depends on your buisiness needs

to the WHERE condition, otherwise it would update the OrderApprovalDateTime again, if you do a "dummy-update" (set OrderStatus = OrderStatus). 


Thursday, September 10, 2015 - 12:49:02 AM - Deepak Kumar Back To Top (38630)

Hi Dr. Dallas,

A very good tip, could you please help to find a wasy, who has changed the data within this trigger you have created through I guess sys.dm_exec_connection. Because sometime the developer may ask a question, okay the data has changed, but what is the login/user details, who has changed the data. I know we could this through Auditing, but we don't want to do this through auditing, it would great if you provide us the code who has changed this data with the trigger itself.




get free sql tips
agree to terms