Share via

Walkthrough: Debugging a Transact-SQL Trigger

  • Article

This topic applies to:

Visual Studio Ultimate

Visual Studio Premium

Visual Studio Professional

Visual Studio Express
ms165046.DoesApplybmp(en-us,VS.100).gif ms165046.DoesApplybmp(en-us,VS.100).gif ms165046.DoesApplybmp(en-us,VS.100).gif ms165046.DoesNotApplybmp(en-us,VS.100).gif

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 AdventureWorks2008 database, which has a Purschasing.Vendor table with a DELETE trigger. The example includes a stored procedure that deletes 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 AdventureWorks2008 sample database. For more information, see How to: Connect to a Database with Server Explorer.

  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: Debug with a SQL Server Database Project or Server Project.

  3. Set breakpoints in DeleteVendor. 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 Purchasing.Vendor table, and then double-clicking the icon for the trigger which is named dVendor.

    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:

    @entityID = 1492

    The yellow arrow of an instruction pointer appears on the line SET NOCOUNT ON, 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 DELETE 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.

Example

This is the code for the stored procedure that causes the trigger to fire.

ALTER PROCEDURE dbo.DeleteVendor
    (
        @entityID       int
    )
AS
    SET NOCOUNT ON
    DELETE Purchasing.Vendor
    WHERE BusinessEntityID = @entityID 
    RETURN

See Also

Concepts

Debugging Transact-SQL

Other Resources

Server Explorer/Database Explorer