SQL Server Tips & Tricks: How to debug a Trigger

This is the third blog post in the series of articles dedicated to the SQL Server Triggers development. The first and the second part can be found here:

• Part 1: Triggers activation order
• Part 2: Triggers must be thought to work on multiple rows

The modern and most important Programming Languages have debugging tools integrated into the development tool. Debugger usually has a graphic interface that allows you to inspect the variables values at run-time to analyze source code and program flow row-by-row and finally to manage breakpoints.

Each developer loves debugging tools because they are very useful when a program fails in a calculation or when it returns an error. Now, think about a Trigger that performs a very complex operation silently. Suppose that this Trigger works into a problem; probably, this question comes to your mind: “Can I debug a Trigger” and if it is possible, “How can I do it?”. Debugging a Trigger is possible with Microsoft Visual Studio development tool (except Express edition).

Consider the first version of the Trigger Production.TR_Product_StockLevel in the my previous blog post of this series SQL Server Triggers Tips & Tricks: Working on multiple rows:

USE [AdventureWorks];
GO
CREATE TRIGGER Production.TR_Product_StockLevel ON Production.Product
AFTER INSERT AS
BEGIN
  /*
    Avoid to insert products with 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;

As you probably have already seen, the first version of that Trigger doesn’t work properly with multiple rows because it hadn’t been thought to work on multiple rows. The customer in which we deployed that Trigger complains that some products have the safety threshold lower than 10. Now we have to debug this Trigger and below you will learn how to do it.

The first step to debug a Trigger is to create a stored procedure that encapsulates the statement that is able to fire the Trigger that you want to debug. Right, we have to create a stored procedure that performs an INSERT statement to the Production.Product table of the AdventureWorks database.

The following piece of T-SQL code creates the stored procedure Production.usp_ins_products in the AdventureWorks database.

USE [AdventureWorks];
GO
CREATE PROCEDURE Production.USP_INS_PRODUCTS
AS BEGIN
  /*
    INSERT statement to fire Trigger TR_Product_StockLevel
  */
  
  INSERT INTO Production.Product
  (
    Name
    ,ProductNumber
    ,MakeFlag
    ,FinishedGoodsFlag
    ,SafetyStockLevel
    ,ReorderPoint
    ,StandardCost
    ,ListPrice
    ,DaysToManufacture
    ,SellStartDate
    ,rowguid
    ,ModifiedDate
  )
  VALUES
  (
    N'BigBike8'
    ,N'BB-5388'
    ,0
    ,0
    ,15 /* SafetyStockLevel */
    ,750
    ,0.0000
    ,78.0000
    ,0
    ,GETDATE()
    ,NEWID()
    ,GETDATE()
  ),
  (
    N'BigBike9'
    ,N'BB-5389'
    ,0
    ,0
    ,1  /* SafetyStockLevel */
    ,750
    ,0.0000
    ,62.0000
    ,0
    ,GETDATE()
    ,NEWID()
    ,GETDATE()
  );
END;

The second step consists in the execution of the stored procedure created in the previous step through Microsoft Visual Studio.

• Open Microsoft Visual Studio and surf into SQL Server Object Explorer
• Open your AdventureWorks database tree
• Expand Programmability folder and try to find out the stored procedure Production.usp_ins_products into “Stored Procedures” folder
• Press right click on the stored procedure Production.usp_ins_products, a context pop-up menu will appear
• Select the item “Debug Procedure…”

A new SQL Query page will be open and it will be ready to debug the stored procedure as you can see in the following picture.

Picture 1 — Debugging the USP_INS_PRODUCTS stored procedure through Microsoft Visual Studio

The execution pointer is set to the first executable instruction of the T-SQL script automatically generated by the Visual Studio Debugger Tool. Using step into debugger function (F11) you can execute thestored procedure Production.usp_ins_products step-by-step up to the INSERT statement that will fire the Trigger you want to debug. If you press step into button (F11) when the execution pointer is on the INSERT statement, the execution pointer will jump into the Trigger, on the first executable statement, as shown in the following picture.

Picture 2 — A break-point into a Trigger

Debugger execution pointer is now on the first executable statement of the Trigger, now you can execute the Trigger’s code and observe variables content step-by-step. In addition, you can see the exact execution flow and the number of rows affected by each statement. If multiple Triggers fire on the same statement, the “Call Stack” panel will show the execution chain and you will be able to discover how the Trigger’s code works.

All examples in this article are based on AdventureWorks database that you can download from GitHub at this link.

Enjoy the debugger! :)