SQL Server Trigger After Update

Overview

Continuing with the model from the previous chapter, now we are going to use a trigger that fires when an update statement is executed.

Explanation

Sample Test Scenario

As you may remember from previous chapters, triggers that handle update statements differ from those that only deal with insert statements, because they make use of the deleted pseudo table as well. This is because an update statement can be considered as a delete followed by an insert.

But having both the deleted and inserted tables at your disposal does not mean that you have to always use them. As you will see in the next sample, we will create a trigger that handles update events to disallow updates on items from orders that were already processed. You will find this trigger very similar to the one we made in the previous chapter to handle insert statements.

CREATE TRIGGER TR_U_PurchaseOrderDetail
ON dbo.PurchaseOrderDetail
FOR UPDATE
AS
IF EXISTS (   SELECT 0
              FROM   dbo.PurchaseOrderHeader POH
                     INNER JOIN Inserted I ON I.PurchaseOrderID = POH.PurchaseOrderID
              WHERE  POH.IsProcessed = 'Y') 
    BEGIN
        ; THROW 51000, 'Purchase Order Items can only be updated before processing order ', 1;
    END;

Now let’s see the actual items on PurchaseOrderHeader and PurchaseOrderDetail tables so we can test our trigger.

Let’s see what happens when we try to update an approved purchase order, like order number 1.

UPDATE dbo.PurchaseOrderDetail 
 SET OrderQty = 2 
 WHERE PurchaseOrderID = 1
 AND PurchaseOrderDetailID = 1

As you can see in the image below, the update fails with an error message saying that “Purchase Order Items can only be updated before processing order.”

Now let’s see what happens when we update items on a purchase order that has not been approved yet, like purchase order number 3.

UPDATE dbo.PurchaseOrderDetail 
 SET OrderQty = 2 
 WHERE PurchaseOrderID = 3
 AND PurchaseOrderDetailID = 4

In the image below, we can see that there were no errors and one row was affected.

Finally, if we query the PurchaseOrderHeader and PurchaseOrderDetail tables, we can see that the quantity for item 4 in order 3 was changed.

Using the deleted table in a UPDATE trigger

Suppose that we need to check that in case an item’s price is updated, the new price cannot be lower than 90 percent of the previous price. In other words, only 10 percent discounts are allowed. In order to code this, we only need to add an IF block to our trigger.

ALTER TRIGGER TR_U_PurchaseOrderDetail
ON dbo.PurchaseOrderDetail
FOR UPDATE
AS
IF EXISTS (   SELECT 0
              FROM   dbo.PurchaseOrderHeader POH
                     INNER JOIN Inserted I ON I.PurchaseOrderID = POH.PurchaseOrderID
              WHERE  POH.IsProcessed = 'Y' )
    BEGIN
        THROW 51000, 'Purchase Order Items can only be updated before processing order ', 1;
    END;
 
IF EXISTS (   SELECT 0
              FROM   Inserted I
                     INNER JOIN Deleted D ON D.PurchaseOrderDetailID = I.PurchaseOrderDetailID
              WHERE  I.UnitPrice < D.UnitPrice * 0.9 )
    BEGIN
        THROW 51000, 'Only up to 10% discount price is allowed', 1;
    END;
GO

In the code, you will see that we can join inserted and deleted pseudo tables to compare the change in the data with the execution of the update statement.

Let’s try to update the unit price of apples (PurchaseOrderDetailID = 4) in purchase order number 3, which is $20, and try to set it to $5.

UPDATE dbo.PurchaseOrderDetail
SET    UnitPrice = 5
WHERE  PurchaseOrderID = 3
       AND PurchaseOrderDetailID = 4;

As you can see in the image below, we see an error message telling us that discounts are only allowed up to 10 percent.

Since apples have a unit price of $20, we cannot update the price to a value lower than $18. Let’s try to set its price to $19.

UPDATE dbo.PurchaseOrderDetail
SET    UnitPrice = 19
WHERE  PurchaseOrderID = 3
       AND PurchaseOrderDetailID = 4;

In the image below, we can see that we were able to change the price.

Additional Information

• SQL Server Trigger After Update for a Specific Value
• Create a Simple SQL Server Trigger to Build an Audit Trail

Daniel Farina

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning. He studied at Universidad de Buenos Aires. Daniel started working as a programmer at a young age. Over the years he specialized in databases, particularly SQL Server and Oracle. Now with 30 years of age, his work experience includes working with various technologies like VB, C, .NET, web development, Windows and Linux systems. He likes to read about science, psychology, philosophy and many other things. In his spare time, he trains powerlifting aiming to compete.

• MSSQLTips Awards: Author of the Year – 2018 | Champion (100+ tips) – 2018 | Author Contender – 2015-2017, 2019