Overview
In the previous chapters of this tutorial, we have seen the fundamentals of triggers. This chapter introduces a real-life scenario where we will see how to implement a trigger to handle insert events.
Explanation
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 has not yet been 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 has been 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 threw 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 haven’t been 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.
Additional Information
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
