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.
Note
Sending results and messages through the SqlPipe object is shown here for illustrative purposes only, and is generally discouraged for production code when programming CLR triggers. Additional data returned may be unexpected and lead to application errors.
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
(
 UserName varchar(50),
 RealName varchar(50)
)
</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.
Note
Sending results and messages through the SqlPipe object is shown here for illustrative purposes only, and is generally discouraged for production code. Additional data returned may be unexpected and lead to application errors
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