Programming Microsoft SQL Server 2005 : An Overview of SQL CLR - CLR Triggers

T-SQL triggers are really just stored procedures that are called by SQL Server at specific times and query values in the “inserted” and “deleted” pseudo-tables. SQL CLR triggers are similar to SQL CLR stored procedures, and they can be created for all data manipulation language (DML) actions (updates, inserts, and deletes).

SQL Server 2005 introduces the concept of data definition language (DDL) triggers, which handle actions such as CREATE TABLE and ALTER PROCEDURE. Like DML triggers, DDL triggers can be implemented in T-SQL or SQL CLR code. We will cover SQL CLR DML and DDL triggers in this section.

SQL CLR DML triggers, like their T-SQL counterparts, have access to the “inserted” and “deleted” pseudo-tables and must be declared as handling one or more specific events for a specific table or, under certain circumstances, a specific view. Also, they can make use of the SqlTriggerContext object (through the SqlContext object’s TriggerContext property) to determine which particular event (update, insert, or delete) caused them to fire and which columns were updated.

Once you latch on to these concepts, writing SQL CLR DML triggers is really quite simple. Listing 1, which shows the code for function trgUpdateContact from trgTest.cs in the sample project, shows the SQL CLR code for DML trigger trgUpdateContact, which is designed to function as a FOR UPDATE trigger on the Person.Contact table in the AdventureWorks database.

Listing 1. trgUpdateContact from trgTest.cs

//[SqlTrigger(Target="Person.Contact", Event="for UPDATE")] public static void trgUpdateContact() { SqlTriggerContext TriggerContext = SqlContext.TriggerContext; String OldName = String.Empty; String NewName = String.Empty; String OldDate = String.Empty; String NewDate = String.Empty; SqlConnection conn = new SqlConnection("context connection=true"); SqlCommand cmOld = new SqlCommand("SELECT FirstName, ModifiedDate from DELETED", conn); SqlCommand cmNew = new SqlCommand("SELECT FirstName, ModifiedDate from INSERTED", conn); conn.Open(); SqlDataReader drOld = cmOld.ExecuteReader(); if (drOld.Read()) { OldName = (string)drOld[0]; OldDate = drOld[1].ToString(); } drOld.Close(); SqlDataReader drNew = cmNew.ExecuteReader(); if (drNew.Read()) { NewName = (string)drNew[0]; NewDate = drNew[1].ToString(); } drNew.Close(); conn.Close(); SqlContext.Pipe.Send("Old Value of FirstName:" + OldName); SqlContext.Pipe.Send("New Value of FirstName:" + NewName); SqlContext.Pipe.Send("Old Value of ModifiedDate:" + OldDate); SqlContext.Pipe.Send("New Value of ModifiedDate:" + NewDate); for (int i = 0; i <= TriggerContext.ColumnCount - 1; i++) { SqlContext.Pipe.Send("Column " + i.ToString() + ": " + TriggerContext .IsUpdatedColumn(i).ToString()); } }

This CLR DML trigger queries the “deleted” and “inserted” tables and echoes back the “before and after” values for the FirstName and ModifiedDate columns when a row is updated. It does so not by piping back SqlDataReader objects but by fetching values from them and echoing back the values as text using the SqlPipe object’s Send method. The trigger code also uses the TriggerContext.IsUpdatedColumn method to echo back a list of all columns in the Person.Contact table and whether each was updated.

To deploy the trigger automatically, you would normally configure a SqlTrigger attribute and apply it to the .NET function that implements the trigger. Because DML triggers are applied to a target object (a table or a view) and an event (for example, “for update” or “instead of insert”), the SqlTrigger attribute has parameters for each of these pieces of information and you must supply values for both. The SqlTrigger attribute deploys only a single copy of the trigger, but you can use T-SQL to deploy the same code as a separate trigger for a different event and/or table. Each separate deployment of the same code is assigned a unique trigger name.

Unfortunately, a bug in Visual Studio prevents the SqlTrigger attribute from being used for target objects not in the dbo schema. (For example, our table, Person.Contact, is in the Person schema rather than the dbo schema.) This is because the value for the Target parameter is surrounded by square brackets when Visual Studio generates its T-SQL code (generating, for example, [Person.Contact], which will cause an error). It is for this reason that the SqlTrigger attribute code is commented out in Listing 1. A workaround to this problem is available through the use of pre-deployment and post-deployment scripts, which we will discuss shortly.

Important

Although you might be tempted to work around the Visual Studio schema bug by supplying a Target value of Person].[Contact instead of Person.Contact, rest assured that this will not work. You may initiate a trace in SQL Server Profiler to observe the erroneous T-SQL generated by Visual Studio in either scenario.

Although Listing 1 does not demonstrate it, you can create a single piece of code that functions as both the update and insert trigger for a given table. You can then use the TriggerContext object’s TriggerAction property to determine exactly what event caused the trigger to fire, and you can execute slightly different code accordingly. Should you wish to deploy such a CLR trigger using the SqlTrigger attribute, you would set its Event parameter to “FOR UPDATE, INSERT”.

The T-SQL command to register a .NET function as a SQL CLR trigger for the update event only is as follows:

CREATE TRIGGER trgUpdateContact
ON Person.Contact
FOR UPDATE
AS EXTERNAL NAME Chapter03.Triggers.trgUpdateContact

Note

All necessary CREATE TRIGGER commands for the Class Library project version of the sample code are contained in the CreateObjects.sql script in the Management Studio project supplied with the sample code.

Beyond using such T-SQL code in Management Studio, there is a way to execute this T-SQL command from Visual Studio, and thus work around the SqlTrigger non-dbo schema bug. An essentially undocumented feature of Visual Studio SQL Server projects is that they allow you to create two special T-SQL scripts that will run immediately before and immediately after the deployment of your assembly. To use this feature, simply create two scripts, named PreDeployScript.sql and PostDeployScript.sql, in the root folder (not the Test Scripts folder) of your project. Although not case-sensitive, the names must match verbatim.

Tip

You can create the PreDeployScript.sql and PostDeployScript.sql scripts outside of Visual Studio and then add them to your project using Visual Studio’s Add Existing Item... feature. You can also add them directly by right-clicking the project node or Test Scripts folder node in the Solution Explorer, choosing the Add Test Script option from the shortcut menu, renaming the new scripts, and dragging them out of the Test Scripts folder into the root folder of your project.

To use this feature to work around the SqlTrigger non-dbo schema bug, insert the preceding CREATE TRIGGER code in your PostDeployScript.sql file and insert the following T-SQL code into your PreDeployScript.sql:

IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[Person].[trgUpdateC
ontact]'))
DROP TRIGGER Person.trgUpdateContact

					  

Regardless of deployment technique, you can use the following query in your Visual Studio test script or a Management Studio query window to test the trigger (this T-SQL code can be found in the TestTriggers.sql script file in the Management Studio project):

UPDATE Person.Contact
SET    FirstName = 'Gustavoo'
WHERE  ContactId = 1

When you run the preceding query, you will notice that the trigger is actually run twice. This is because the AdventureWorks Person.Contact table already has a T-SQL update trigger, called uContact. Because uContact itself performs an update on the ModifiedDate column of Person.Contact, it implicitly invokes a second execution of trgUpdateContact. By looking at the output of trgUpdateContact, you can confirm that the FirstName column is updated on the first execution (by the test query) and the ModifiedDate column is modified on the second execution (by trigger uContact). The two executions’ output might appear out of order, but the values of ModifiedDate will make the actual sequence clear.

If you place the TriggerContext object’s TriggerAction property in a comparison statement, IntelliSense will show you that there is a wide array of enumerated constants that the property can be equal to, and that a majority of these values correspond to DDL triggers. This demonstrates clearly that SQL CLR code can be used for DDL and DML triggers alike.

In the case of DDL triggers, a wide array of environmental information might be desirable to determine exactly what event caused the trigger to fire, what system process ID (SPID) invoked it, what time the event fired, and other information specific to the event type such as the T-SQL command that caused the event. The SqlTriggerContext object’s EventData property can be queried to fetch this information. The EventData property is of type SqlXml; therefore it, in turn, has a CreateReader method and a Value property that you can use to fetch the XML-formatted event data as an XmlReader object or a string, respectively.

The code in Listing 2, taken from function trgCreateTable in trgTest.cs in the sample project, shows the SQL CLR code for the DDL trigger trgCreateTable registered to fire for any CREATE TABLE command executed on the AdventureWorks database.

Listing 2. trgCreateTable from trgTest.cs

[SqlTrigger(Target = "DATABASE", Event = "FOR CREATE_TABLE")] public static void trgCreateTable() { SqlTriggerContext TriggerContext = SqlContext.TriggerContext; if (!(TriggerContext.EventData == null)) { SqlContext.Pipe.Send("Event Data: " + TriggerContext.EventData.Value.ToString()); } }

The code interrogates the Value property of SqlContext.TriggerContext.EventData, casts it to a string, and pipes that string back to the client. Note that the SqlTrigger attribute is not commented out in this case because a schema prefix is not used in the Target parameter value. Thus, you can use attribute-based deployment in the SQL Server project or the following command for the Class Library version:

CREATE TRIGGER trgCreateTable
ON DATABASE
FOR CREATE_TABLE
AS EXTERNAL NAME Chapter03.Triggers.trgCreateTable

Use the following T-SQL DDL command in your Visual Studio test script or a Management Studio query window to test the DDL trigger. (You can find this code in the TestTriggers.sql script file in the sample Management Studio project.)

CREATE TABLE Test (low INT, high INT)
DROP TABLE Test

Your result should appear similar to the following:

<EVENT_INSTANCE>
  <EventType>CREATE_TABLE</EventType>
  <PostTime>2006-04-29T16:37:50.690</PostTime>
  <SPID>54</SPID>
  <ServerName>CGH-T42AB</ServerName>
  <LoginName>CGH-T42AB\AndrewB</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>AdventureWorks</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>Test</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
     <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER=
"ON" ENCRYPTED="FALSE" />
   <CommandText>CREATE TABLE Test (low INT, high INT)</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

					  

Note

The actual output would consist of continuous, unformatted text. We added the line breaks and indentation to make the EventData XML easier to read.