Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Trigger After Update for a Specific Value


By:   |   Read Comments (4)   |   Related Tips: More > Triggers

Attend these FREE MSSQLTips webcasts >> click to register


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.

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



Last Update:


signup button

next tip button



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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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

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

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

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.

Thanks

Deepak

 


Learn more about SQL Server tools