Walkthrough: Debugging a SQL CLR Trigger

This topic applies to:

Edition	Visual Basic	C#	C++	Web Developer
Express
Standard
Pro and Team

Table legend:

	Applies
	Does not apply
	Command or commands hidden by default.

This example shows how to debug a SQL CLR trigger. It uses the Contact table in the AdventureWorks sample database, which is one of the databases installed with SQL Server 2005. The sample creates a new insert CLR trigger on the Contact table, and then steps into it.

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 CLR trigger:

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

2. Create a new trigger using the code from the first example section below, and name it iContact.cs. For more information, see How to: Develop with the SQL Server Project Type.

3. Add a script that tests the trigger by causing it to fire. In Solution Explorer, right-click the TestScripts directory, click Add Test Script, and insert the code from the second Example section below. Save the file with the name truContact.sql. Right-click the file name, and click Set as Default Debug Script.

4. Set breakpoints in iContact.cs, and then click Start from the Debug menu to compile, deploy, and unit test the project. When the instruction pointer, designated by a yellow arrow, appears on a breakpoint, you are debugging the trigger.

5. Try out different debugging features.

   1. Step past the statement instantiating the SqlTriggerContext.

   2. Open the Locals window. Notice that you can open the variable triggContext, which is a SqlTriggerContext, and you can examine its members. For more information, see How to: Use Debugger Variable Windows.

      Note

      The server may not reflect changes to values of variables in the debugger windows. For more information, see SQL Debugging Limitations.

   3. Choose Step Into from the Debug menu to step one line in the stored procedure. Notice that execution has instantiated the value of the variable sqlP of type SqlPipe.

   4. Open the Watch window. Drag the sqlP variable to any location on the Watch window. The variable is now added to the list of watched variables. For more information, see How to: Use Debugger Variable Windows.

      Note

      You can edit the values of variables in the Watch window as well.

   5. In the Text Editor, right-click the last line, and click Insert Breakpoint.

   6. On the Debug menu, click Continue, and the debugger will run the code up to the new breakpoint.

6. Click Continue again to finish debugging the trigger. A message appears in the Output window stating that the trigger was successfully deployed, and displaying the result of executing the commands in the truContact.sql file.

Example

This is the code required to create the trigger used in this example.

using System;
using System.Data;
using System.Data.Sql;
using Microsoft.SqlServer.Server;


public partial class Triggers
{
      // Enter existing table or view for the target and uncomment the attribute line.
      [Microsoft.SqlServer.Server.SqlTrigger (Name="Trigger1", Target="primes", Event="FOR UPDATE")]
      public static void Trigger1()
      {
            SqlTriggerContext triggContext = SqlContext.TriggerContext;
            SqlPipe sqlP = SqlContext.Pipe;
            sqlP.Send("primes inserted!");
      }
}

This is the test script that will cause the trigger to fire.

UPDATE Person.Contact SET MiddleName = 'A' WHERE ContactID = 8

See Also

Tasks

How to: Create and Run a CLR SQL Server Trigger

Other Resources

SQL CLR Database Debugging