CLR triggers

In addition to stored procedures and user-defined functions, the new .NET integration capabilities found in SQL Server 2005 also provide the ability to create CLR triggers. Find out how in this chapter excerpt.

In addition to stored procedures and user-defined functions, the new .NET integration capabilities found in SQL Server 2005 also provide the ability to create CLR triggers. To create a trigger using Visual Studio 2005, you start your project as you saw in the earlier examples. To create a trigger using Visual Studio 2005, select the New | Project option, give your project a name, and click OK to create the project.

For this project, I used the name ti_ShowInserted for my trigger. This trigger essentially retrieves the values of the row being inserted in a table and displays them. After naming the project and clicking OK, I filled out the New Database Reference dialog using the same values that were shown in the previous examples. Next, I used the Project | Add Trigger menu option that you can see in Figure 3-10 to create a starter project for the CLR trigger.

Figure 3-10
Figure 3-10: Adding a CLR trigger

As you saw in the earlier example of CLR database objects, you select the Trigger option from the list of templates and then provide the name of the trigger in the name prompt. Visual Studio 2005 will generate a starter project file that you can add your code to. The starter project includes the appropriate import directives as well as generating a class, in this case appropriately named Triggers, and a method named ti_ShowInserted with its appropriate method attribute. The following code listing shows the completed code for the CLR trigger named ti_ShowInserted:

The example CLR trigger displays the contents of the data that is used for an insert action that's performed on the Person.ContactTypes table in the Adventureworks database. The first thing to notice in this code listing is the Attribute for the ti_ShowInserted subroutine (the code enclosed within the < > markers). The Attribute is used to name the trigger and identify the table the trigger will be applied to as well as the event that will cause the trigger to fire.

When the Visual Studio 2005 trigger template initially generates this Attribute, it is prefaced by a comment symbol—essentially making the line a comment. This is because the trigger template doesn't know how or where you want the trigger to be used. In order for Visual Studio 2005 to deploy the trigger, you need to uncomment the Attribute line and then fill in the appropriate properties. The following table lists the properties used by the Visual Studio 2005 trigger template:


In this example, the resulting trigger will be named ti_ShowInserted. It will be applied to the table named Person.ContactType, which is in the AdventureWorks database, and the trigger will only be fired for an insert operation.

The primary code for the trigger is found within the ti_ShowInserted subroutine. This code example makes use of another new ADO.NET object: SqlTriggerContext. The SqlTriggerContext object provides information about the trigger action that's fired and the columns that are affected. The SqlTriggerContext object is always instantiated by the SqlContext object. Generally, the SqlContext object provides information about the caller's context. Specifically, in this case, the SqlContext object enables the code to access the virtual table that's created during the execution of the trigger. This virtual table stores the data that caused the trigger to fire.

Next, a SqlPipe object is created. The SqlPipe object enables the trigger to communicate with the external caller, in this case to pass the inserted data values to the caller. The TriggerAction property of the SqlContext object is used to determine if the trigger action was an insert operation. Using the TriggerAction property is quite straightforward. It supports the following values:


If the TriggerAction property equals TriggerAction.Insert, then an insert was performed and the contents of the virtual trigger table are retrieved and sent to the caller using the SqlPipe object's Execute method. In order to retrieve the contents of the virtual table, a SqlConnection object and a SqlCommand object are needed. These objects come from the System.Data.SqlClient namespace. You should note that when used with server-side programming, the Connection String used by the SqlConnection object must be set to the value of "context Connection=true". Then a SqlCommand object named oCmd is instantiated that uses the statement "Select * from inserted" to retrieve all of the rows and columns from the virtual table that contains the inserted values. Finally, the ExecuteAndSend method of SqlPipe object is used to execute the command and send the results back to the caller.

Deploying the trigger

Once the code has been created, you can either deploy it to the database using the Visual Studio 2005 Build | Deploy solution option or manually drop and re-create the assembly and any dependent objects you saw in UDF examples earlier in this chapter. ch03.

To manually deploy the code, you'd need to copy ti_ShowInserted.dll to the SQL Server system or to a share that's accessible to the SQL Server system and then execute the following T-SQL Server commands:

This example assumes that ti_ShowInsert.dll was copied into the c:temp directory on the SQL Server system. First, the Create Assembly statement is used to copy the DLL into the SQL Server database and then the Create Trigger statement is used with the As External Name clause to create a trigger named ti_ShowInserted and attach it to the Person.ContactTypes table. As in the earlier examples, the As External Name clause identifies the assembly using a three-part name: asssembly.class.method. Pay particular attention to the class portion of this name. For triggers you must bracket the class name and include the namespace just before the class name. In this example, the assembly is named ti_ShowInserted. The Namespace is ti_ShowInserted. The class is named Triggers, and the method is named ti_ShowInserted.

Using the trigger

After the CLR trigger has been deployed, it will be fired for every insert operation that's performed on the base table. For example, the following INSERT statement will add a row to the Person.ContactType table, which will cause the CLR trigger to fire:

The example trigger, ti_ShowInserted, performs a select statement on the inserted row value. Then it uses the SqlPipe object to send the results back to the caller. In this example the trigger will send the contents of the inserted row values back to the caller:

The previous tip is from "Developing CLR database objects: 10 tips in 10 minutes," excerpted from Chapter 3 of the book "Microsoft SQL Server 2005: A Developer's Guide" written by Michael Otey and Denielle Otey, courtesy of McGraw-Hill Publishing.

Developing CLR database objects

 Home: Introduction
 Tip 1: CLR architecture
 Tip 2: CLR assemblies in SQL Server 2005
 Tip 3: Creating CLR database objects
 Tip 4: CLR stored procedures
 Tip 5: User-Defined Functions
 Tip 6: CLR triggers
 Tip 7: User-Defined Types
 Tip 8: CLR aggregates
 Tip 9: Debugging CLR database objects
 Tip 10: .NET database object security

Dig Deeper on Microsoft SQL Server 2005