SQL Server Trigger After Delete


By:
Overview

In the previous chapters we have seen how to create triggers that fire after insert and update statements. This chapter will focus on using triggers with delete statements.

Sample Test Scenario

We are going to create a trigger on the PurchaseOrderDetail table that fires when a delete statement is executed to adjust the TotalDue column of PurchaseOrderHeader table by subtracting the LineTotal value from the deleted rows. Of course, first we are going to validate that the order wasn't processed yet.

Take a look at the code below and you will see that this delete trigger is composed with an IF EXISTS statement block where we check the purchase order processing status and throw an exception in case there is an order item referencing to a processed order. Otherwise, in the ELSE clause we proceed to update the PurchaseOrderHeader table and set the TotalDue with the result of resting it to the sum of the LineTotal from each item in the purchase order.

CREATE TRIGGER TR_D_PurchaseOrderDetail
ON dbo.PurchaseOrderDetail
FOR DELETE
AS
IF EXISTS (   SELECT 0
              FROM   dbo.PurchaseOrderHeader POH
                     INNER JOIN Deleted D ON D.PurchaseOrderID = POH.PurchaseOrderID
              WHERE  POH.IsProcessed = 'Y' )
    BEGIN
        ; THROW 51000, 'Purchase Order Items can only be updated before processing order ', 1;
    END;
ELSE
    BEGIN
        UPDATE dbo.PurchaseOrderHeader
        SET    TotalDue = TotalDue - S.LineTotal
        FROM   dbo.PurchaseOrderHeader POH
               INNER JOIN (   SELECT   D.PurchaseOrderID ,
                                       SUM(D.LineTotal) LineTotal
                              FROM     Deleted D
                              GROUP BY D.PurchaseOrderID ) S ON S.PurchaseOrderID = POH.PurchaseOrderID;
    END;
GO

Before testing this trigger let's add another purchase order to our database.

INSERT INTO dbo.PurchaseOrderHeader ( IsProcessed, EmployeeID, VendorID, OrderDate, TotalDue )
VALUES ( 'N'  , 5 , 1 , '2019-07-09 11:29:53.000' , 190)
 
INSERT INTO dbo.PurchaseOrderDetail ( PurchaseOrderID ,  OrderQty ,  Product , UnitPrice , LineTotal )
VALUES ( 4 , 3 ,  'Orange' , 30 , 90 )
 
INSERT INTO dbo.PurchaseOrderDetail ( PurchaseOrderID ,  OrderQty ,  Product , UnitPrice , LineTotal )
VALUES ( 4 , 2 , 'Apple' ,  20 , 40 )
 
INSERT INTO dbo.PurchaseOrderDetail ( PurchaseOrderID ,  OrderQty ,  Product , UnitPrice , LineTotal )
VALUES ( 4 , 2 , 'Lemmon' ,  30 , 60 )

In the image below you will see that we added a new purchase order with 3 items: Orange, Apple and Lemon.

Testing trigger after delete State of test tables before executing the delete statement.

Now let's delete Apple and Lemon from the order. (Note: please consider that you may have different values for the PurchaseOrderDetailID column if you are following along with your own system.)

DELETE FROM dbo.PurchaseOrderDetail
WHERE PurchaseOrderID = 4
      AND PurchaseOrderDetailID IN ( 16, 17 );

As you can see in the next image our delete trigger worked as expected by resetting the TotalDue for the order from $190 to $90.

Testing trigger after delete 2 State of test tables after executing the delete statement.
Additional Information

Last Update: 8/2/2021




Comments For This Article





download














get free sql tips
agree to terms