Walkthrough: Debugging a SQL Server CLR IntegrationTrigger

This topic applies to:

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

This example shows how to debug an SQL Server common language runtime (CLR) integration 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 SQL Server CLR integration trigger on the Contact table, and then steps into it.

If, when you try to debug a CLR integration object, the message "Canceled by user" appears, you must manually configure both the computer on which you are running Visual Studio as well as the computer that is running SQL Server. For more information, see How to: Configure Your Computers to Enable Transact-SQL and SQL Server CLR Integration Debugging.

To debug a SQL Server CLR integration trigger:

1. In a new CLR integration project, establish a connection to the AdventureWorks database. For more information, see How to: Connecting 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 might 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 SQL Server Trigger by using Common Language Run-time Integration