#0382 – SQL Server – SSMS – Debugging a T-SQL DML Trigger

I often receive questions related to debugging code using SSMS. Based on these questions, I have written a lot of blog posts in the past, however I was recently asked the question:

How can I debug a DML trigger in SSMS?

I realized that while I had encountered the same question and researched it in my initial days (when I worked on SQL Server 2000), I had never written about it. So, here goes.

In order to demonstrate how to debug a trigger, all we need to do is debug the statement that initiates the trigger.

For this demonstration, I will fire an update on the [Sales].[SalesOrderHeader] table of the AdventureWorks database.

USE AdventureWorks2012;
GO
UPDATE soh
SET soh.SubTotal += 2
FROM Sales.SalesOrderHeader AS soh
WHERE soh.SalesOrderNumber = 'SO43697';

When we debug this query (using F10), the SSMS debugger launches and we can step through the code using the same key combinations as we have in Visual Studio.

Demonstrating how to debug DML triggers using SSMS

Debugging T-SQL script using SSMS

Executing the update should fire the DML trigger [uSalesOrderHeader] which can be debugged like any other T-SQL code by stepping into the trigger (press F11 when executing the update).

Stepping into a DML trigger during a SSMS T-SQL Debugging session

That’s it. Debugging a trigger is no different than debugging a stored procedure or any other T-SQL script.

In case you would like to learn more about debugging in SSMS, please do refer my previous posts (links below). If there is anything specific that you would like to know more about, do drop in a line.