This is the third blog post in the series dedicated to the Tips and Tricks of DML Triggers development.
The first and the second parts can be found here:
The 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 into 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 SQL Server DML Triggers - Tips & Tricks - Part 2 ("Thought to work on multiple rows"):
CREATE TRIGGER Production.TR_Product_StockLevel ON Production.Product
AFTER INSERT AS
Avoid to insert products with value of safety stock level lower than 10
@SafetyStockLevel = SafetyStockLevel
IF (@SafetyStockLevel < 10)
THROW 50000, N'Safety Stock Level cannot be lower than 10!', 1;
IF (@@TRANCOUNT > 0)
THROW; -- Re-Throw
As you probably have already seen, the first version of that Trigger doesn't work well with multiple rows because it hadn't been thought to work with multiple rows. The customer in which we deployed that Trigger complains that some products have the safety threshold saved in the SafetyStockLevel column lower than 10. Now we have to debug that DML AFTER INSERT Trigger, below here 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 AdventureWorks2014 database. The following piece of T-SQL code creates the Production.USP_INS_PRODUCTS stored procedure in the AdventureWorks2014 database.
CREATE PROCEDURE Production.USP_INS_PRODUCTS
INSERT statement to fire Trigger TR_Product_StockLevel
INSERT INTO Production.Product
Name, ProductNumber, MakeFlag, FinishedGoodsFlag, SafetyStockLevel,
ReorderPoint, StandardCost, ListPrice, DaysToManufacture,
SellStartDate, rowguid, ModifiedDate
N'BigBike8', N'BB-5388', 0, 0, 10 /* 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()
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 the AdventureWorks2014 database tree, expand Programmability folder and try to find out the Production.USP_INS_PRODUCTS stored procedure into "Stored Procedures" folder. Next, press right click on the stored procedure Production.USP_INS_PRODUCTS, a context pop-up menu will appear and when you 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 the Production.USP_INS_PRODUCTS stored procedure 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 breakpoint within 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.
Enjoy the debugger! :)
All examples in this article are based on AdventureWorks2014 database that you can download from codeplex website at this link.