MCTS 70-433 CLR Trigger

This is a bit advanced for the 70-433 exam but I've included it as interesting background.

The following bulletin board thread gives good example of basic CLR triggers. It illustrates why you might need to use them, how to set one up and just how tricky this can be:

http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/f3b0c009-7669-4090-9867-4adf15f1e66e

CLR Trigger

• Wednesday, July 13, 2005 9:30 AM
  Vinoth
   
  Hi, 
•       I want to call webservice in the Trigger.  How to call the webservice from CLR Triggers?.If anybody knows teh detail let me know.   
• And i worked out  the following Example code for CLR Trigger. 
• using System.Data; 
• using System.Collections.Generic; 
• using System.Text; 
• using System.Data.Sql; 
• using System.Data.SqlTypes; 
• using System.Data.SqlServer; 
• namespace CLR 
• { 
•     public class Class1 
•     { 
•         public static void InsertTrigger() 
•         { 
•             SqlTriggerContext sqlTrigger = SqlContext.GetTriggerContext(); 
•             SqlPipe sqlPipe = SqlContext.GetPipe(); 
•             SqlCommand cmd = SqlContext.GetCommand(); 
•             if (sqlTrigger.TriggerAction == TriggerAction.Insert) 
•             { 
•                 cmd.CommandText = "Select * from Inserted"; 
•                 SqlDataRecord dr = cmd.ExecuteRow(); 
•                 string Subject_uri = dr[0].ToString(); 
•                 string predicate = dr[1].ToString(); 
•                 string Obj = dr[2].ToString(); 
•                 sqlPipe.Execute(cmd); 
•                 sqlPipe.Send("You Selected Subject_uri ::"+Subject_uri+" Predicate ::"+predicate+" Object ::"+Obj); 
•             } 
•         } 
•     } 
• } 
• After that i created Assembly  and  Trigger and clr enabled 
• create assembly CLR from 'C:\Inetpub\wwwroot\Vinoth\CLR\bin\Debug\CLR.dll' 
• CREATE TRIGGER InsertTrigger 
• ON Triplets 
• For Insert 
• As 
• External Name 
• CLR.[CLR.Class1].InsertTrigger 
• sp_configure 'show advanced options', 1 
• RECONFIGURE 
• sp_configure 'clr enabled', 1 
• RECONFIGURE 
• insert into Triplets values('test','_2','Testing1') 
• When i insert the values it is inserting into table .But why ican't get sqlpipe.send Message. 
• How do i know whether my CLR trigger is working? 
• Thanks, 
• Vinoth
  
  

Answers

• Monday, August 01, 2005 1:55 AM
  Pablo Castro - MSFT
   
  To answer the specific question: pipe.Send(string) is the equivalent of "PRINT" in T-SQL. In order to "see" the string you sent from the server in the client you need to be listening to info-messages.
  
  If you're using System.Data.SqlClient in the client, the way to do this is to subscribe to the InfoMessage event in the SqlConnection object. If you're trying this from Query Analyzer or Management Studio, then you'll see it in the "messages" tab after executing the query.
  
  I have to extra comments:
  1. Note that when you move to newer CTPs or Beta versions (or the RTM version) you'll have to update your code as we no longer have a separate System.Data.SqlServer namespace for inproc data-access. That capability has been integrated into the provider in System.Data.SqlClient.
  
  2. Doing network I/O -particularly over the internet- inside a trigger can seriously impact the performance of your database. I can't say "don't do it" because I don't have understanding of your particular scenario, so let me say this: I recommend that you do this only if you really need to and understand the implications of doing so.
  
  Hope this helps.