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".
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
Take a few minutes to explore what happens when you change the INNER JOIN or add more columns to the table and trigger.
- 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
Last Update: 9/10/2015
About the author
View all my tips