CLR Triggers 

A trigger is a special type of stored procedure that automatically runs when a language event executes. Because of the Microsoft SQL Server integration with the .NET Framework common language runtime (CLR), you can use any .NET Framework language to create CLR triggers. This section covers information specific to triggers implemented with CLR integration. For a complete discussion of triggers, see the topics "Understanding DML Triggers" and "Understanding DDL Triggers" in SQL Server 2005 Books Online.

Unique Capabilities of CLR Triggers

Triggers written in Transact-SQL have the capability of determining which columns from the firing view or table have been updated, by using the UPDATE(column) and COLUMNS_UPDATED() functions.

Triggers written in a CLR language differ from other CLR integration objects in several significant ways. CLR triggers can:

• Reference data in the INSERTED and DELETED tables.

• Determine which columns have been modified as a result of an UPDATE operation.

• Access information about database objects affected by the execution of data definition language (DDL) statements.

These capabilities are provided inherently in the query language, or by the SqlTriggerContext class.

Using the SqlTriggerContext Class

The SqlTriggerContext class cannot be publicly constructed, and can only be obtained by accessing the TriggerContext property within the body of a CLR trigger. The SqlTriggerContext class can be obtained from the active SqlContext by calling the TriggerContext property:

SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;

The SqlTriggerContext class provides contextual information about the trigger. This includes the type of action that caused the trigger to fire, which columns were modified in an UPDATE operation, and, in the case of a DDL trigger, an XML EventData structure which describes the triggering operation. For more information, see the topic "EVENTDATA (Transact-SQL)" in SQL Server 2005 Books Online.

Determining the Trigger Action

Once you have obtained a SqlTriggerContext, you can use it to determine the type of action which caused the trigger to fire. This information is available through the TriggerAction property of the SqlTriggerContext class.

For DML triggers, the TriggerAction property can be one of the following values:

• TriggerAction.Update (0x1)

• TriggerAction.Insert (0x2)

• TriggerAction.Delete (0x3)

• For DDL triggers, the list of possible TriggerAction values is considerably longer.

Using the inserted and deleted tables

Two special tables are used in data manipulation language (DML) trigger statements: the inserted table and the deleted table. SQL Server 2005 automatically creates and manages these tables. You can use these temporary tables to test the effects of certain data modifications and to set conditions for DML trigger actions; however, you cannot alter the data in the tables directly.

CLR triggers can access the inserted and deleted tables through the CLR in-process provider. This is done by obtaining a SqlCommand object from the SqlContext object. For example:

sqlCommand sqlComm = SqlContext.GetCommand()
sqlComm.CommandText = "SELECT column_1 from inserted";

Determining Updated Columns

You can determine the number of columns that were modified by an UPDATE operation by using the ColumnCount property of the SqlTriggerContext object. You can use the IsUpdatedColumn method, which takes the column ordinal as an input parameter, to determine whether the column was updated. A True value in the array indicates that the column has been updated.

For example, this code snippet lists all of the columns updated:

C#

updatedColumnCount = SqlContext.TriggerContext.ColumnCount;
updatedColumns = new bool [updatedColumnCount];

for (int i=0; i<updatedColumnCount; i++)
   updatedColumns [i] = SqlContext.TriggerContext.IsUpdatedColumn(i);
   counter = 0;
   foreach (bool t in updatedColumns)
      SqlContext.Pipe.Send("Updated column " + reader.GetName(counter++) + "? " + t.ToString());

Visual Basic

updatedColumnCount = SqlContext.TriggerContext.ColumnCount
ReDim updatedColumns(updatedColumnCount)

Dim i As Integer
For i=0 To updatedColumnCount-1
   updatedColumns(i) = SqlContext.TriggerContext.IsUpdatedColumn(i)
   counter = 0

   Dim t As Boolean
   For Each t In updatedColumns
      SqlContext.Pipe.Send("Updated column " _
        & reader.GetName(counter) & "? " & t.ToString())
      counter += 1
    Next
Next

Accessing the EventData Property for CLR DDL Triggers

DDL triggers, like regular triggers, fire stored procedures in response to an event. But unlike DML triggers, they do not fire in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they fire in response to a variety of DDL statements, which are primarily statements that begin with CREATE, ALTER, and DROP. DDL triggers can be used for administrative tasks, such as auditing and monitoring of database operations and schema changes.

Information about an event that fires a DDL trigger is available in the EventData property of the SqlTriggerContext class. This property contains an XML value. The XML schema includes information about:

• The time of the event.

• The System Process ID (SPID) of the connection during which the trigger executed.

• The type of event that fired the trigger.

Then, depending on the event type, the schema includes additional information, such as the database in which the event occurred, the object against which the event occurred, and the Transact-SQL command of the event.

In the following example, the following DDL trigger returns the raw EventData property.

C#

using System;
using System.Data;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;
using System.Text.RegularExpressions;

public class CLRTriggers
{
   public static void DropTableTrigger()
   {
       SqlTriggerContext triggContext = SqlContext.TriggerContext;           

       switch(triggContext.TriggerAction)
       {
           case TriggerAction.DropTable:
           SqlContext.Pipe.Send("Table dropped! Here's the EventData:")
           SqlContext.Pipe.Send(triggContext.EventData.Value);
           break;
                
           default:
           SqlContext.Pipe.Send("Something happened! Here's the EventData:");
           SqlContext.Pipe.Send(triggContext.EventData.Value);
           break;
       }
   }
}

Visual Basic

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient

'The Partial modifier is only required on one class definition per project.
Partial Public Class CLRTriggers 
    
    Public Shared Sub DropTableTrigger()
        Dim triggContext As SqlTriggerContext
        triggContext = SqlContext.TriggerContext

        Select Case triggContext.TriggerAction
           Case TriggerAction.DropTable
              SqlContext.Pipe.Send("Table dropped! Here's the EventData:")
              SqlContext.Pipe.Send(triggContext.EventData.Value)

           Case Else
              SqlContext.Pipe.Send("Something else happened! Here's the EventData:")
              SqlContext.Pipe.Send(triggContext.EventData.Value)
        
        End Select
    End Sub
End Class

The following sample output is the EventData property value after a DDL trigger has been fired by a CREATE TABLE event:

<EVENT_INSTANCE><PostTime>2004-04-16T21:17:16.160</PostTime><SPID>58</SPID><EventType>CREATE_TABLE</EventType><ServerName>MACHINENAME</ServerName><LoginName>MYDOMAIN\myname</LoginName><UserName>MYDOMAIN\myname</UserName><DatabaseName>AdventureWorks</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>UserName</ObjectName><ObjectType>TABLE</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create table dbo.UserName&#x0D;&#x0A;(&#x0D;&#x0A; UserName varchar(50),&#x0D;&#x0A; RealName varchar(50)&#x0D;&#x0A;)&#x0D;&#x0A;</CommandText></TSQLCommand></EVENT_INSTANCE>

Sample CLR Trigger

In this example, consider the scenario in which you let the user choose any ID they want, but you want to know the users that specifically entered an e-mail address as an ID. The following trigger would detect that information and log it to an audit table.

using System;
using System.Data;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;
using System.Text.RegularExpressions;

public class CLRTriggers
{
    public static void EmailAudit()
    {
        SqlTriggerContext triggContext = SqlContext.TriggerContext;
        string userName, realName;
        SqlConnection connection = new SqlConnection ("context connection = true");
        connection.Open();
        SqlCommand command = connection.CreateCommand();
        SqlDataReader reader;
        bool[] updatedColumns;
        int updatedColumnCount;
        int counter;
        switch(triggContext.TriggerAction)
        {
            case TriggerAction.Insert:
                command.CommandText = "SELECT * from " + "inserted";
                reader = command.ExecuteReader();
                userName = (string)reader[0];
                realName = (string)reader[1];

                if (IsValidEMailAddress(userName))
                {
                    command.CommandText = "INSERT dbo.UserNameAudit " + 
                     "VALUES ('" + userName + "', '" + realName + "')";
                    SqlContext.Pipe.Send(command.CommandText);
                    SqlContext.Pipe.ExecuteAndSend(command);
                    SqlContext.Pipe.Send("You inserted: " + userName);
                }

                break;

            case TriggerAction.Update:
                command.CommandText = "SELECT * from " + "inserted";
                reader = command.ExecuteReader();
                userName = (string)reader[0];
                realName = (string)reader[1];
                SqlContext.Pipe.Send("You updated: " + userName + " - " + realName);

                updatedColumnCount = SqlContext.TriggerContext.ColumnCount;
                updatedColumns = new bool [updatedColumnCount];

                for (int i=0; i<updatedColumnCount; i++)
                updatedColumns [i] = SqlContext.TriggerContext.IsUpdatedColumn(i) ;

                counter = 0;
                foreach (bool t in updatedColumns)
                SqlContext.Pipe.Send("Updated column " + reader.GetName(counter++) + "? " + t.ToString());
                
                break;

            case TriggerAction.Delete:
                command.CommandText = "SELECT * from " + "deleted";
                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    SqlContext.Pipe.Send("You deleted the following rows:");
                    while (reader.Read())
                        SqlContext.Pipe.Send(reader.GetString(0) + ", " + reader.GetString(1));
                    //alternately, to just send a tabular resultset back:
                    //SqlContext.Pipe.Send(reader);
                } 
                else 
                {
                    SqlContext.Pipe.Send("No rows affected.");
                }

                break;
        }
    }
    public static bool IsValidEMailAddress(string emailAddress)
    {
        return Regex.IsMatch(emailAddress, "^([\\w-]+\\.)*?[\\w-]+@[\\w-]+\\.([\\w-]+\\.)*?[\\w]+$");
    }
}

Visual Basic

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions

'The Partial modifier is only required on one class definition per project.
Partial Public Class CLRTriggers 
    
    Public Shared Sub DropTableTrigger()
        Dim triggContext As SqlTriggerContext
        Dim connection As New SqlConnection("context connection=true")
        Dim command As SqlCommand
        Dim reader As SqlDataReader
        Dim userName As String
        Dim realName As String
        Dim updatedColumns As Boolean()
        Dim updatedColumnCount As Integer
        Dim counter As Integer

        triggContext = SqlContext.TriggerContext
        connection.Open()
        command = connection.CreateCommand()

        Select Case triggContext.TriggerAction
           Case TriggerAction.Insert
              command.CommandText = "SELECT * FROM " + "inserted"
              
              reader = command.ExecuteReader()

              userName = CType(reader(0), String)
              realName = CType(reader(1), String)

              If IsValidEmailAddress(userName) Then
                 command.CommandText = "INSERT dbo.UserNameAudit " & _ 
                     "VALUES ('" & userName & "', '" & realName & "')"
                 
                 SqlContext.Pipe.Send(command.CommandText)
                 SqlContext.Pipe.ExecuteAndSend(command)
                 SqlContext.Pipe.Send("You inserted: " & userName)

              End If              
              
           Case TriggerAction.Update
              command.CommandText = "SELECT * FROM " & "inserted"
              
              reader = command.ExecuteReader()

              userName = CType(reader(0), String)
              realName = CType(reader(1), String)
              SqlContext.Pipe.Send("You updated: " & userName & " - " & realName)

              updatedColumnCount = SqlContext.TriggerContext.ColumnCount
              ReDim updatedColumns(updatedColumnCount)

              Dim i As Integer

              For i=0 To updatedColumnCount-1

                 updatedColumns(i) = SqlContext.TriggerContext.IsUpdatedColumn(i)
                 counter = 0

                 Dim t As Boolean
                 For Each t In updatedColumns
                    SqlContext.Pipe.Send("Updated column " & reader.GetName(counter) & "? " & t.ToString() )
                    counter += 1
                 Next

              Next

           Case TriggerAction.Delete
              command.CommandText = "SELECT * FROM " & "deleted"
              reader = command.ExecuteReader()
   
              If reader.HasRows Then
                 SqlContext.Pipe.Send("You deleted the following rows:")

                 While reader.Read()
                    SqlContext.Pipe.Send( reader.GetString(0) & ", " & reader.GetString(1))
                    ' Alternately, just send a tabular resultset back:
                    ' SqlContext.Pipe.Send(reader)
                 End While
              Else
                SqlContext.Pipe.Send("No rows affected.")
              End If              
        
        End Select
    End Sub

    Public Shared Function IsValidEMailAddress(emailAddress As String) As Boolean

       return Regex.IsMatch(emailAddress, "^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$")

    End Function    
End Class

Assuming two tables exist with the following definitions:

CREATE TABLE Users
(
    UserName nvarchar(200) NOT NULL,
    RealName nvarchar(200) NOT NULL
);
GO CREATE TABLE UserNameAudit
(
    UserName nvarchar(200) NOT NULL,
    RealName nvarchar(200) NOT NULL
)

The Transact-SQL statement that creates the trigger in SQL Server is as follows, and assumes assembly SQLCLRTest is already registered in the current SQL Server database.

CREATE TRIGGER EmailAudit
ON Users
FOR INSERT
AS
EXTERNAL NAME SQLCLRTest.testclrtrigger.EmailAudit