Walkthrough: Debugging a Transact-SQL Trigger

  • Article

This topic applies to:

Edition

Visual Basic

C#

C++

Web Developer

Express

 Topic does not apply Topic does not apply Topic does not apply Topic does not apply

Standard

 Topic does not apply Topic does not apply Topic does not apply Topic does not apply

Pro and Team

 Topic applies Topic applies Topic applies Topic applies

Table legend:

Topic applies

Applies
Topic does not apply

Does not apply
Topic applies but command hidden by default

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.

Note

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

  1. In a new SQL Server project, establish a connection to the AdventureWorks sample database. For more information, see How to: Connect to a Database.

  2. 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.

  3. 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.

  4. Set breakpoints for the trigger.

    1. 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.

    2. 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.

  5. 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.

  6. Set the following parameter values:

    1. @currencyCode = AAA

    2. @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.

  7. Try out different debugging features.

    1. 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.

    2. Step through the trigger until you exit back to the stored procedure, and continue to the end.

  8. 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.

Example

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

See Also

Concepts

Debugging SQL

Reference

Server Explorer/Database Explorer