SQL Server Triggers Tips & Tricks: Working on multiple rows
This is the second blog post in the series dedicated to the Tips and Tricks of DML Triggers development. The first part can be found here: SQL Server Triggers Tips & Tricks: The activation order.
One of the most frequent mistake I have seen during my experience in Triggers debugging and tuning is: the Author of the Trigger doesn’t consider that his Trigger, sooner or later, will work on multiple rows! I have seen many Triggers, especially those ones that implement domain integrity constraints, which were not thought to work on multiple rows. This mistake, in certain cases, produces the storing of incorrect data (an example will follow).
Suppose that you have to develop a DML AFTER INSERT Trigger to avoid to store values lower than 10 in the SafetyStockLevel column of the Production.Product table in the AdventureWorks database. This customized business logic may be required to guarantee no production downtime in your company when a supplier is late in delivering.
The following piece of T-SQL code shows the CREATE statement for the Trigger Production.TR_Product_StockLevel.
USE [AdventureWorks];
GOCREATE TRIGGER Production.TR_Product_StockLevel ON Production.Product
AFTER INSERT AS
BEGIN
/*
Avoid to insert products with value of safety stock level
lower than 10
*/
BEGIN TRY
DECLARE @SafetyStockLevel SMALLINT; SELECT
@SafetyStockLevel = SafetyStockLevel
FROM
inserted; IF (@SafetyStockLevel < 10)
THROW 50000, N’Safety Stock Level cannot be lower than 10!’, 1;
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK;
THROW; -- Re-Throw
END CATCH;
END;
GO
A very good habit, before applying Triggers and changes (in general) in the production environment, is to spend time to test the Trigger code, especially for the borderline cases and values. So, in this example you have to test if this Trigger is able to reject each INSERT statement that tries to store values lower than 10 into SafetyStockLevel column of the Production.Product table. The first test you can do, for example, is trying to insert one wrong value to observe the error caught by the Trigger. The following statement tries to insert a product with SafetyStockLevel lower than 10.
USE [AdventureWorks];
GO-- Test one: Try to insert one wrong product
INSERT INTO Production.Product
(
Name
, ProductNumber
, MakeFlag
, FinishedGoodsFlag
, SafetyStockLevel
, ReorderPoint
, StandardCost
, ListPrice
, DaysToManufacture
, SellStartDate
, rowguid
, ModifiedDate
)
VALUES
(
N'Carbon Bar 1'
, N'CB-0001'
, 0
, 0
, 3 /* SafetyStockLevel */
, 750
, 0.0000
, 78.0000
, 0
, GETDATE()
, NEWID()
, GETDATE()
);
As you expect, SQL Server has rejected the INSERT statement because the value assigned to SafetyStockLevel is lower than 10 and the Trigger Production.TR_Product_StockLevel has blocked the statement. The output shows that Trigger worked well…
Msg 50000, Level 16, State 1, Procedure TR_Product_StockLevel, Line 17Safety Stock Level cannot be lower than 10!
Now you have to test the Trigger for statements that try to insert multiple rows. The following statement tries to insert two products: the first product has a wrong value for SafetyStockLevel column, whereas the value in second one is right. Let’s see what happens.
USE [AdventureWorks];
GO-- Test two: Try to insert two products
INSERT INTO Production.Product
(
Name
, ProductNumber
, MakeFlag
, FinishedGoodsFlag
, SafetyStockLevel
, ReorderPoint
, StandardCost
, ListPrice
, DaysToManufacture
, SellStartDate
, rowguid
, ModifiedDate
)
VALUES
(
N'Carbon Bar 2'
, N'CB-0002'
, 0
, 0
, 4 /* SafetyStockLevel */
, 750
, 0.0000
, 78.0000
, 0
, GETDATE()
, NEWID()
, GETDATE()
),
(
N'Carbon Bar 3'
, N'CB-0003'
, 0
, 0
, 15 /* SafetyStockLevel */
, 750
, 0.0000
, 78.0000
, 0
, GETDATE()
, NEWID()
, GETDATE()
);
GO
The output shows that the Trigger has worked well again, SQL Server has rejected the INSERT statement because in the first row the value 4 for the SafetyStockLevel column is lower than 10 and it can’t be accepted.
Msg 50000, Level 16, State 1, Procedure TR_Product_StockLevel, Line 17Safety Stock Level cannot be lower than 10!
If you have to deploy your Trigger as soon as possible, you could convince yourself that this Trigger works properly, after all you have already done two tests and all wrong rows were rejected. You decide to apply the Trigger in the production environment; but what happens if someone or an application tries to insert two products, in which there is one wrong value put in an order that differs from the one you used in the previous test?
Let’s see the following INSERT statement in which the first row is right and the second one is wrong.
USE [AdventureWorks];
GO-- Test three: Try to insert two rows
-- The first row one is right, but the second one is wrong
INSERT INTO Production.Product
(
Name
, ProductNumber
, MakeFlag
, FinishedGoodsFlag
, SafetyStockLevel
, ReorderPoint
, StandardCost
, ListPrice
, DaysToManufacture
, SellStartDate
, rowguid
, ModifiedDate
)
VALUES
(
N'Carbon Bar 4'
, N'CB-0004'
, 0
, 0
, 18 /* SafetyStockLevel */
, 750
, 0.0000
, 78.0000
, 0
, GETDATE()
, NEWID()
, GETDATE()
),
(
N'Carbon Bar 5'
, N'CB-0005'
, 0
, 0
, 6 /* SafetyStockLevel */
, 750
, 0.0000
, 78.0000
, 0
, GETDATE()
, NEWID()
, GETDATE()
);
GO
The last INSERT statement has been completed successfully, but inserted data do not respect the domain constraint implemented by the Trigger, as you can see in the following picture.
The safety stock level value for the product named “Carbon Bar 5” doesn’t respect the business constraint implemented by the Trigger Production.TR_Product_StockLevel; this Trigger hasn’t been thought to work on multiple rows. The mistake is in the following assignment line:
SELECT
@SafetyStockLevel = SafetyStockLevel
FROM
Inserted;The local variable named @SafetyStockLevel can contain only one value from the SELECT on the Inserted virtual table and this value will be the SafetyStockLevel value corresponding to the first row that is returned from the statement. If the first row (that one returned from the query) has a suitable value in the SafetyStockLevel column, this Trigger will consider right the others as well. In this case, not allowed values (lower than 10) from the second row on, will be stored anyway!
How can the Trigger’s Author fix this issue? He can fix it by checking SafetyStockLevel value on all rows in the Inserted virtual table, and if the Trigger finds just one value which is not allowed it will return an error. Below here, there is the version 2.0 of the Trigger Production.TR_Product_StockLevel, it fixes the issue changing the previous SELECT statement in an IF EXISTS SELECT statement.
USE [AdventureWorks];
GOALTER TRIGGER Production.TR_Product_StockLevel ON Production.Product
AFTER INSERT AS
BEGIN
/*
Avoid to insert products with value of safety stock level
lower than 10
*/
BEGIN TRY
-- Testing all rows in the Inserted virtual table
IF EXISTS
(
SELECT ProductID
FROM inserted
WHERE (SafetyStockLevel < 10)
)
THROW 50000, N'Safety Stock Level cannot be lower than 10!', 1;
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK;
THROW; — Re-Throw
END CATCH;
END;
GO
This new version is thought to work on multiple rows and it always works properly.
However the best implementation for this business logic is by using CHECK constraint that is the best way to implement customize domain integrity. The main reason to prefer CHECK constraints instead of the Triggers, when you have to implement customize domain integrity, is that all constraints (such as CHECK, UNIQUE and so on) will be checked before the execution of the statement that fires it. On the contrary, AFTER DML Triggers will fire after the statement has been executed. As you can imagine, for performance reason, in this scenario, the CHECK constraint solution is better than the Trigger solution.
All examples in this post are based on AdventureWorks database that you can download from Github repository at this link.
