By: Daniel Farina
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.
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.
