THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Sergio Govoni

A place where share my experience on SQL Server and related technologies

SQL Server DML Triggers - Tips & Tricks - Part 2 ("Thought to work 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 DML Triggers - Tips & Tricks - Part 1 ("Intro" & "Triggers activation order").

 

Triggers must be thought to work on multiple rows

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 will work on multiple rows, sooner or later! 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 AdventureWorks2014 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 [AdventureWorks2014];
GO

CREATE 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 [AdventureWorks2014];
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 17

Safety 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 [AdventureWorks2014];
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 17

Safety 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 [AdventureWorks2014];
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.

Picture 1 – Safety stock level: Domain integrity violated for product named "Carbon Bar 5"


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 [AdventureWorks2014];
GO

ALTER 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 AdventureWorks2014 database that you can download from codeplex website at this link.

Published Thursday, March 5, 2015 10:29 PM by Sergio Govoni

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement