SQL Server 2012 : Exploring SQL CLR - CLR Triggers

T-SQL triggers are really just stored procedures that are called by SQL Server at specific times and that can query values in the DELETED and INSERTEDSQL CLR triggers are similar to SQL CLR stored procedures, and they can be created for all data manipulation language (DML) actions that modify data (that is, updates, inserts, and deletes). pseudo-tables (which expose “before and after” snapshots of data changed by the statement that fired the trigger).

SQL Server 2005 introduced the concept of data definition language (DDL) triggers, which can intercept and 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 both SQL CLR DML and DDL triggers in this section.

SQL CLR DML triggers, like their T-SQL counterparts, have access to the DELETED and INSERTED 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 understand these concepts, writing SQL CLR DML triggers is really quite simple. Example 1, which shows the code for the trgUpdatePerson function from trgTest.cs in the SQLCLRDemo sample project, shows the SQL CLR code for the trgUpdatePerson DML trigger, which is designed to function as a FOR UPDATE trigger on the Person.Person table in the AdventureWorks2012 database.

Example 1. trgUpdatePerson from trgTest.cs.

[SqlTrigger(Target="Person.Person", Event="for UPDATE")]
public static void trgUpdatePerson()
{
    SqlTriggerContext context = SqlContext.TriggerContext;
    string oldName = string.Empty;
    string newName = string.Empty;
    SqlConnection conn = new SqlConnection("context connection=true");
    SqlCommand cmOld = new SqlCommand(
                        "SELECT FirstName FROM DELETED", conn);
    SqlCommand cmNew = new SqlCommand(
                        "SELECT FirstName FROM INSERTED", conn);
    conn.Open();
    SqlDataReader drOld = cmOld.ExecuteReader();
    if (drOld.Read())
    {
        oldName = (string)drOld[0];
    }
    drOld.Close();
    SqlDataReader drNew = cmNew.ExecuteReader();
    if (drNew.Read())
    {

        newName = (string)drNew[0];
    }
    drNew.Close();
    conn.Close();
    SqlContext.Pipe.Send("Old Value of FirstName:" + oldName);
    SqlContext.Pipe.Send("New Value of FirstName:" + newName);
    for (int i = 0; i <= context.ColumnCount - 1; i++)
    {
        SqlContext.Pipe.Send("Column " + i.ToString() + ": " +
           context.IsUpdatedColumn(i).ToString());
    }
}

This CLR DML trigger queries the DELETED and INSERTED pseudo-tables and echoes back the “before and after” values (respectively) for the FirstNameSqlDataReader objects but by fetching values from the pseudo-tables and echoing back the values as text using the SqlPipe object’s Send method. The trigger code also uses the TriggerContext.IsUpdatedColumn method to return a list of all columns in the Person.Person table and whether each was updated. column when a row is updated. It does so not by piping back

To deploy the trigger automatically, you apply a SqlTrigger attribute to the .NET function that implements the trigger. Because DML triggers are tied 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 table. Each separate deployment of the same code is assigned a unique trigger name.

Although Example 1 does not demonstrate it, you can create a single piece of code that functions as both the update and the 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 want 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 trgUpdatePerson
 ON Person.Person
 FOR UPDATE
 AS EXTERNAL NAME SQLCLRDemo.Triggers.trgUpdatePerson

You can use the following query to test the trigger. (This T-SQL code can be found in the TestTriggers.sql script file in the SSMS project.)

UPDATE Person.Person
 SET    FirstName = 'Gustavo'
 WHERE  BusinessEntityId = 1

If you place the TriggerContext object’s TriggerAction property in a comparison statement, Visual Studio’s 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 text, respectively.

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

Example 2. trgCreateTable from trgTest.cs.

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

The code interrogates the Value property of SqlContext.TriggerContext.EventData, converts it to a string, and pipes that string back to the client. To deploy this trigger, you can use attribute-based deployment in the SQL Server Database Project or the following command for the Class Library version:

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

Use the following T-SQL DDL command to test the DDL trigger. (You can find this code in the TestTriggers.sql script file in the sample SSMS project.)

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

Your result should contain the message “Event Data:” followed by text similar to the following:

<EVENT_INSTANCE>
  <EventType>CREATE_TABLE</EventType>
  <PostTime>2012-04-27T16:16:05.150</PostTime>
  <SPID>66</SPID>
  <ServerName>KIWI</ServerName>
  <LoginName>CONTOSO\Administrator</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>AdventureWorks2012</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. Line breaks and indentation have been added here to make the EventData XML easier to read.