SQL Server Trigger After Insert


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

Sample Tables initial load of test tables.

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.

Enforcing bussiness logic with a trigger

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.

Inserting an item in an unprocessed order.
Additional Information

Last Update: 8/2/2021




Comments For This Article





download














get free sql tips
agree to terms