Walkthrough: Debugging a Transact-SQL Trigger
This topic applies to:
Pro and Team
Does not apply
Command or commands hidden by default.
To debug a trigger, you set a breakpoint in a stored procedure that will cause the trigger to fire, set a breakpoint in the trigger, and then proceed as described in Walkthrough: Debug a Transact-SQL Stored Procedure.
This example uses the AdventureWorks database, which has a Sales.Currency table with an UPDATE trigger. The example includes a stored procedure that updates a row in the table, thus causing the trigger to fire. Set breakpoints in the trigger, and by executing the stored procedure with different parameters, you can follow different execution paths in the trigger.
The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.
To debug a SQL trigger
In a new SQL Server project, establish a connection to the AdventureWorks sample database. For more information, see How to: Connect to a Database.
Create a new stored procedure using the code from the first example section below, and name it UpdateCurrency_T_SQL. For more information, see How to: Develop with the SQL Server Project Type.
Set breakpoints in UpdateCurrency_T_SQL. This is optional, because Direct Database Debugging causes the first line of the procedure to act as a breakpoint.
Set breakpoints for the trigger.
Open the trigger source code by right-clicking the Tables node, then right-clicking the node for the Sales.Currency table, and then double-clicking the icon for the trigger which is named uCurrency.
Left-click in the gray margin next to the SET NOCOUNT ON statement to set a break point in the trigger. This step is not optional: if you do not set a breakpoint in the trigger, you will skip over its code when you try to step into it.
Step into the stored procedure. For more information, see How to: Step into an Object Using Server Explorer.
The Run Stored Procedure dialog box appears, asking for parameter values.
Set the following parameter values:
@currencyCode = AAA
@name = an arbitrary value such as My Test Name.
The yellow arrow of an instruction pointer appears on the line SET @mynvarchar = @@VERSION, the first executable line of code in the stored procedure.
Try out different debugging features.
Step through the code using the F11 key or the Step Into button.
At the UPDATE statement, when you press F11 again, you will step into the trigger.
Step through the trigger until you exit back to the stored procedure, and continue to the end.
In Server Explorer you can verify that the data was inserted by right-clicking on the Sales.Currency node under Tables, and clicking Show Table Data.
This is the code for the stored procedure that causes the trigger to fire.
ALTER PROCEDURE dbo.UpdateCurrency_T_SQL ( @currencyCode nvarchar(3), @Name nvarchar(50) ) AS SET NOCOUNT ON UPDATE Sales.Currency SET Name = @Name WHERE CurrencyCode = @currencyCode RETURN