By: Daniel Farina
Overview
In the previous chapters of this tutorial we have seen the fundamentals of triggers. This chapter will introduce a real-life scenario where we will see how to implement a trigger to handle insert events.
Sample Test Scenario
Assume we have an application that handles purchase orders and its data is stored in two tables PurchaseOrderHeader and PurchaseOrderDetail. Let's suppose that the user can change the quantity of an item only if the purchase order was not yet processed.
The following code section creates and fills both tables with sample data.
CREATE TABLE [dbo].[PurchaseOrderHeader] ( [PurchaseOrderID] [int] NOT NULL IDENTITY(1, 1), [IsProcessed] CHAR(1) NOT NULL, [EmployeeID] [int] NOT NULL, [VendorID] [int] NOT NULL, [OrderDate] [datetime] NOT NULL, [TotalDue] [money] NOT NULL, PRIMARY KEY CLUSTERED (PurchaseOrderID) ) ON [PRIMARY] GO CREATE TABLE [dbo].[PurchaseOrderDetail] ( [PurchaseOrderID] [int] NOT NULL, [PurchaseOrderDetailID] [int] NOT NULL IDENTITY(1, 1), [OrderQty] [smallint] NOT NULL, [Product] VARCHAR(50) NOT NULL, [UnitPrice] [money] NOT NULL, [LineTotal] [money] NOT NULL, PRIMARY KEY CLUSTERED (PurchaseOrderID,PurchaseOrderDetailID) ) ON [PRIMARY] GO ALTER TABLE [PurchaseOrderDetail] ADD CONSTRAINT FK_PurchaseOrderID FOREIGN KEY (PurchaseOrderID) REFERENCES dbo.PurchaseOrderHeader(PurchaseOrderID) GO INSERT INTO dbo.PurchaseOrderHeader ( IsProcessed, EmployeeID, VendorID, OrderDate, TotalDue ) VALUES ( 'Y' , 10 , 1 , '2019-04-01 14:39:20.000' , 40) INSERT INTO dbo.PurchaseOrderDetail ( PurchaseOrderID , OrderQty , Product , UnitPrice , LineTotal ) VALUES ( 1 , 2 , 'Apple' , 20 , 40) INSERT INTO dbo.PurchaseOrderHeader ( IsProcessed, EmployeeID, VendorID, OrderDate, TotalDue ) VALUES ( 'Y' , 5 , 1 , '2019-04-05 17:54:24.000' , 30) INSERT INTO dbo.PurchaseOrderDetail ( PurchaseOrderID , OrderQty , Product , UnitPrice , LineTotal ) VALUES ( 2 , 1 , 'Orange' , 30 , 30 ) INSERT INTO dbo.PurchaseOrderHeader ( IsProcessed, EmployeeID, VendorID, OrderDate, TotalDue ) VALUES ( 'N' , 5 , 1 , '2019-04-15 10:29:53.000' , 130) INSERT INTO dbo.PurchaseOrderDetail ( PurchaseOrderID , OrderQty , Product , UnitPrice , LineTotal ) VALUES ( 3 , 3 , 'Orange' , 30 , 90 ) INSERT INTO dbo.PurchaseOrderDetail ( PurchaseOrderID , OrderQty , Product , UnitPrice , LineTotal ) VALUES ( 3 , 2 , 'Apple' , 20 , 40 )
The next image shows both tables with data.
Let's create a trigger to enforce that the items on Purchase Orders cannot be added after an order was processed.
CREATE TRIGGER TR_I_PurchaseOrderDetail
ON dbo.PurchaseOrderDetail
FOR INSERT
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 added before processing order ', 1;
END;
As you can see in the trigger's code, to obtain the status of purchase orders on which there were new items added, I joined the Inserted pseudo table with the PurchaseOrderHeader table and throw an exception in case there were orders already processed.
Let's see what happens when we try to add a row to the PurchaseOrderDetail table with a new item for Purchase Order number 2 which has already been processed.
INSERT INTO dbo.PurchaseOrderDetail ( PurchaseOrderID , OrderQty , Product , UnitPrice , LineTotal ) VALUES ( 2 , 3 , 'Lemon' , 30 , 90 )
The execution of the code has the following result, which is as expected. The error message tells us that we can only add items to orders that aren't processed yet.
Now let's see what happens if we try to insert a new item on a non-processed order.
INSERT INTO dbo.PurchaseOrderDetail ( PurchaseOrderID , OrderQty , Product , UnitPrice , LineTotal ) VALUES ( 3 , 2 , 'Pineapple' , 30 , 60 ) SELECT * FROM dbo.PurchaseOrderDetail
As you can see in the next screen capture we were able to add the new row.
