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.
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.
- T-SQL Debugger – Underappreciated Features of Microsoft SQL Server [Blog Link]
- Enabling T-SQL Debugger in SQL Server Management Studio (SSMS) [Blog Link]
- SSMS – Tutorial – Part 03 – The T-SQL Debugger (L200) [Blog Link]
- T-SQL Debugging – Connection Errors & Firewall settings [Blog Link]
- T-SQL Debugging – SSMS errors – MSDBG2.DLL [Blog Link]
- Setting, Labeling, Using & Searching Conditional Breakpoints [Blog Link]
- Export/Import Breakpoints [Blog Link]
- Using the “When Hit, Do Something” breakpoint configuration [Blog Link]
- Using Data-Tips [Blog Link]
- Editing, Exporting and Importing Data Tips [Blog Link]
Until we meet next time,Be courteous. Drive responsibly.