CLR Triggers

In this section, I will focus on the features, development, deployment, usage, and limitations of triggers developed in managed code.

Supported Features

SQL Server 2005 supports the creation of DML and DDL triggers in managed code. Both types of DML triggers are also supported—After triggers and Instead-of triggers.

Creation of DML Triggers

I will demonstrate the creation of a DML trigger in Visual Studio 2005:

1. Open Visual Studio 2005 and select File | New Project.

2. Select Database | SQL Server Project and set its name (for example, to CSrpTrigger).

3. Select or create a new database reference.

4. On the Project menu, select Add Trigger and set the name of the file. Visual Studio will create the file with the trigger template:

 using System; using System.Data; using System.Data.SqlClient; 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="Triggerl",     //                                         Target="Tablel",     //                                         Event="FOR UPDATE")]     public static void Triggerl()     {         // Replace with your own code         SqlContext.Pipe.Send("Trigger FIRED");     } } 

As you can see, triggers are also created as public static methods. They should not return a value (it is set to void) to the caller. To force the compiler to add everything needed for this method to become a trigger, you need to enable the Microsoft. SqlServer. Server.SqlTrigger attribute before the method declaration. It contains several properties that are important for the creation of the assembly and the CLR trigger. The Name property sets the name of the trigger. The Target property sets the table the trigger will be associated with. Event defines the type of trigger and the action that triggers it. Possible trigger types are Instead-of and After (or you can use For as a synonym for After). Possible actions are Insert, Update, and Delete. For example, if you want to create an After trigger that will be invoked by Insert and Update statements, you should define the following attribute:

 [SqlTrigger (Name="dbo.ctr_Contact_iu",              Target="Contact",              Event="AFTER UPDATE, INSERT")] 

You can deploy the trigger in the usual manner by choosing Build | Deploy on the Visual Studio menu. To test it, you can insert a new record in the Contact table using Management Studio.

Note 

I deliberately didn't put the schema name in front of the name of the table. It is also a problem to assign a trigger to a table that is in a nondefault schema. There is a known bug in initial versions of Visual Studio 2005 and SQL Server 2005 that prevents this. Sometimes you will get a Visual Studio error stating that it cannot find the specified table to deploy the trigger on. As a workaround, you can try closing then reopening the project before testing.

To access trigger-related features, you need to get the context of the current trigger. You will be able to get it using SqlContext:

 SqlTriggerContext triggerContext = SqlContext.GetTriggerContext(); 

It returns some of the available information to the caller:

 string action = triggerContext.TriggerAction.ToString(); string sObj = triggerContext.ToString(); SqlContext.Pipe.Send("Trigger " + sObj                   + " FIRED on " + action); 

You can also identify which columns have been changed:

 string s = ""; int iCount = triggerContext.ColumnCount; for (int i = 0; i < iCount; i++) {     if (triggerContext.IsUpdatedColumn(i) == true)         s = s + i.ToString() + ", "; } SqlContext.Pipe.Send("Trigger updated columns: " + s); 

You can execute additional Transact-SQL commands through an existing connection (using the same method as in stored procedures):

 SqlConnection connection = new SqlConnection("context connection = true"); connection.Open(); SqlCommand command = connection.CreateCommand(); 

In this way, you can access any database object, but more importantly, you can access the content of Inserted and Deleted virtual tables:

 if (triggerContext.TriggerAction == Sql.TriggerAction.Insert) {    command.CommandText = "SELECT * FROM INSERTED";    SqlDataRecord record = command.ExecuteRow();    string email = (string)record[5];    if (Regex.IsMatch(email,           @"([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$") == false)           SqlContext.Pipe.Send("Not a valid email!"); } 

The only problem with the preceding method is that it does not work as expected in the case where more than one record is updated or inserted in a single statement (if you remember, this was also a typical error during the design of Transact-SQL triggers).

The solution is to load e-mails from all inserted or updated records and check them one by one. The following trigger uses the SqlDataReader class to get all records from the Inserted virtual table. The SqlDataReader.GetValue method will be used to read data from the specified column.

 using System; using System.Data; using System.Data.SqlClient; using Microsoft.SqlServer.Server; using System.Text.RegularExpressions; public partial class Triggers {     [SqlTrigger(Name = "dbo.ctr_Contact_iu_Email",                 Target = "Contact",                 Event = "AFTER UPDATE, INSERT")]     public static void ctr_Contact_iu_Email()      {         //get trigger context to access trigger related features         SqlTriggerContext triggerContext = SqlContext.TriggerContext;         //test validity of email         using (SqlConnection con                = new SqlConnection("context connection = true"))         {             con.Open();             using (SqlCommand cmd = con.CreateCommand())             {                 if (triggerContext.TriggerAction == TriggerAction.Insert)                 {                     cmd.CommandText = "SELECT * FROM INSERTED";                     using (SqlDataReader rdr = cmd.ExecuteReader())                     {                         while (rdr.Read())                         {                             string email = rdr.GetValue(5).ToString();                             if (Regex.IsMatch(email, @"^([\w-]+\.)*?[\w-] +@[\w-]+\.([\w-]+\.)*?[\w]+$") == false)                             {                                 SqlContext.Pipe.Send("Not a valid email!");                                 //Transaction.Current.Rollback();                             }                          }                      }                  }             }         }     } } 

Using an additional connection (along with a SqlCommand and a SqlDataReader object), you can return a list of columns that were updated:

 //list of updated column names using (SqlConnection con = new SqlConnection("context connection = true"))  {     con.Open(};     using (SqlCommand cmd = con.CreateCommand())     {         cmd.CommandText = "SELECT * FROM INSERTED";         using (SqlDataReader rdr = cmd.ExecuteReader())         {             rdr.Read();             if (triggerContext.TriggerAction == TriggerAction.Update)             {                 string sCol = "Updated columns: ";                 for (int icol = 0; icol < triggerContext.ColumnCount; icol++)                     if (triggerContext.IsUpdatedColumn(icol) == true)                          sCol = sCol + rdr.GetName(icol) + ", ";                 SqlContext.Pipe.Send(sCol);             }         }    } } 

In all of the trigger examples so far, I have used Pipe.Send() just to demonstrate how to create triggers. In the real world, it is not generally recommended that you return text messages from a trigger to a caller. More likely, the operation would be rolled back and/or written to the database and/or an audit file.

It is very simple to write content to file in .NET. For example, you can use the StreamWriter class to create a file and log information in it. The Boolean parameter in the constructor specifies that data will be appended to the file if the file already exists.

 StreamWriter file = new StreamWriter("c:\\Audit.txt", true); file.WriteLine("Delete trigger is fired!"); file.Close() 

The following Delete Instead-of trigger will write log information in a file:

 using System; using System.Data; using System.Data.SqlClient; using Microsoft.SqlServer.Server; using System.I0; public partial class Triggers  {    [SqlTrigger(Name = "citr_OrderItem_D",                Target = "Orderltem",                Event = "INSTEAD OF DELETE")]    public static void TriggerDelete()    {        string lines;        //create a new file or append an existing        using (StreamWriter file =            new StreamWriter("c:\\Audit.txt", true))        {            using (SqlConnection con =                 new SqlConnection("context connection = true"))            {                con.Open();                using (SqlCommand cmd = con.CreateCommand())                {                    cmd.CommandText = "select system_user as [user], "                        + " GetDate() as [time], 'OrderItem' as [table]"                        + " from deleted for xml raw";                    using (SqlDataReader rdr = cmd.ExecuteReader())                    {                        // there could be multiple records                        while (rdr.ReadO)                        {                            lines = rdr.GetValue(O)-ToString();                            //SqlContext.Pipe.Send(lines);                            file.WriteLine(lines);                        }                    }                }            }            file.Close();        }    } } 

Tip 

The advantage of writing to file is that the data will stay in the file even when the operation is rolled hack. However, you should use this technique only for operations that are very rare, so that you do not degrade the performance of the system and do not encounter issues with concurrency.

Creation of DDL Triggers

SQL Server also supports managed DDL triggers. I will first show you how to create them with Visual Studio and then without it.

Visual Studio 2005 does not have a special template for DDL triggers. In fact, BOL is definitely not detailed enough on the subject of SqlTrigger attribute requirements for DDL triggers (but that is why you need this book). The Target identifier of the SqlTrigger attribute of a DDL trigger defines the scope of the trigger, which can be Database or All Server. The trigger will be fired after one or more server or database events that are listed in the Event identifier. Naturally, you must also set the Name identifier so that Visual Studio knows how to name the trigger object in the database.

The following Visual Basic .NET trigger records event information about changes to functions and procedures in the current database to a log file on a local hard drive. It will react to DDL statements that change stored procedures and functions (which include Create, Drop, and Alter statements). It will be implemented in the current database as ctrd_DdlProcedureEvents_b:

 Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.IO Partial Public Class Triggers     <SqlTrigger(Name:="ctrd_DdlProcedureEvents_vb",         Target:="DATABASE",         Event:="AFTER DDL_PROCEDURE_EVENTS, DDL_FUNCTION_EVENTS")>     Public Shared Sub trigger_DDL_PROCEDURE_EVENTS()         'get info about all procedure modifications in the database         'get trigger context         Dim triggerContext As SqlTriggerContext = SqlContext.TriggerContext         'get event info         Dim sXml As String = triggerContext.EventData.Value         'log info         Using file As New StreamWriter("c:\\sp_change.log", True)              file.WriteLine(sXml)             file.Close ()         End Using     End Sub End Class 

The method uses the System.IO.StreamWriter class to create or append the file and write text to a file.

Note 

This is a somewhat academic example. Naturally, you should use a trigger to write to file only for events that you expect to occur seldom. A better place to log changes such as this would he to a database.

To customize the behavior of the trigger, you need to get hold of trigger context in the same manner as with a DML trigger. However, the DDL trigger contains a different set of properties. The property that is available only in DDL triggers, and which you will use most often, is SqlTriggerContext.EventData.Value. It contains an XML document with information about the event that fired the trigger. For example:

 <EVENT_INSTANCE>       <EventType>CREATE_PROCEDURE</EventType>       <PostTime>2005-12-17T16:07:00</PostTime>       <SPID>54</SPID>       <ServerName>LG\RC</ServerName>       <LoginName>LG\dsunderic</LoginName>       <UserName>dbo</UserName>       <DatabaseName>Asset5</DatabaseName>       < S chemaName > dbo </S chemaName >       <0bj ectName>ap_test</Obj ectName>       <ObjectType>PROCEDURE</ObjectType>       <TSQLCommand>             <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"       ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />            < CommandText >                   create proc dbo.ap_Invetory_List                   as                   select * from dbo.Inventory             </CommandText >          </TSQLCommand>       </EVENT INSTANCE> 

The following C# managed trigger will react to table DDL events (which include Create Table, Drop Table, and Alter Table statements). It will be implemented in the current database as ctrd_CreateTableDdl:

 using System; using System.Data; using System.Data.SqlClient; using Microsoft.SqlServer.Server; using System.Xml; using System.Diagnostics; using System.10; public partial class Triggers { [SqlTrigger(    Name = "ctrd_TableDdl",    Target = "DATABASE",    Event = "AFTER DDL_TABLE_EVENTS")] public static void ddl_table() {     //get trigger context to access trigger related features     SqlTriggerContext triggerContext = SqlContext.TriggerContext;     //read xml with info about event that fired trigger     string sXml = triggerContext.EventData.Value;     string login = "";     string sql = "";     using (StringReader srXml = new StringReader(sXml))     {         using (XmlReader rXml = new XmlTextReader(srXml))         {             //loop through nodes to the end of XML             while (!(rXml.EOF))             {                 rXml.MoveToContent();                 if (rXml.IsStartElement()                       && rXml.Name.Equals("LoginName"))                     login = rXml.ReadElementString("LoginName");                 else if (rXml.IsStartElement()                       && rXml.Name.Equals("CommandText"))                     sql = rXml.ReadElementString("CommandText");                 //move to the next node                 rXml.Read();             }                  //if the source does not exist create it                 if ( !EventLog.SourceExists("Assets"))                       EventLog.CreateEventSource("Assets", "Asset5 database");                 //write event info in event log                 EventLog EventLogl = new                       EventLog("Asset5 database", "LG", "Assets");                 string log = "Login: "+login+" executed: ["+sql+"]";                 EventLogl.WriteEntry(log);              }     } } } 

The trigger obtains the XML document and then parses it using the SystemXml. XmlTextReader class. It is a class that is designed for performing XML reading and writing without loading a complete XML document into memory. The trigger will obtain a login and the statement that fired it.

      using (StringReader srXml = new StringReader(sXml))      {          using (XmlReader rXml = new XmlTextReader(srXml))          {              //loop through nodes to the end of XML              while (!(rXml.EOF))              {                  rXml.MoveToContent() ;                  if (rXml.IsStartElement()                        && rXml.Name.Equals("LoginName"))                      login = rXml.ReadElementString("LoginName");                  else if (rXml.IsStartElement()                        && rXml.Name.Equals("CommandText"))                      sql = rXml.ReadElementString("CommandText");                  //move to the next node                  rXml.Read();              } 

The collected information is eventually written in Event Log. Recording is performed using the SystemDiagnostics.EventLog class:

           //if the source does not exist create it           if ( !EventLog.SourceExists("Assets"))                 EventLog.CreateEventSource("Asset5", "Asset5 database");               //write event info in event log              EventLog EventLogl = new                    EventLogt"Asset5 database", "LG", "Assets");              string log = "Login: "+login+" executed: ["+sql+"]";              EventLogl.WriteEntry(log); 

If you want to deploy DDL triggers manually, you can use a script such as the following:

 CREATE ASSEMBLY VbTriggers FROM C:\Projects\VbTriggers\VbTriggers\bin\VbTriggers.dll WITH PERMISSION_SET = EXTERNAL_ACCESS GO CREATE TRIGGER ctrd_TableDdl ON DATABASE FOR CREATE_ASSEMBLY AS EXTERNAL NAME VbTriggers.Triggers.ddl_table GO 

Note 

You will have to manually deploy server-scoped managed DDL triggers. Unfortunately, Visual Studio 2005 does not support their manual deployment.

Microsoft has written an interesting and very useful DDL trigger that registers all user-defined database objects of every assembly that is registered in the database. It is a very useful script and will be the last script for managed database objects that you need to write. You can also explore its internals to see how Microsoft engineers are doing them. You can find the script at http://www.blogs.msdn.com/sqlclr/articles/495428.aspx.