CLR User-Defined Triggers - SQL Server 2008

Triggers are a very useful construct for T-SQL programmers. A routine can be defined that will automatically fire upon attempted data manipulation, thereby putting the onus for the required logic on the database itself, rather than every stored procedure that needs to manipulate it. An example of this would be a trigger used for auditing. By using a trigger, the logic for copying some of the modified data into another table is centralized. Without the trigger, every stored procedure that did anything with the data would need to have its own copy of this logic, and a developer might forget to include it in one stored procedure, thereby destroying continuity of the audit logs.

CLR triggers behave the same way as T-SQL triggers, bringing the same power to the table: centralization and encapsulation of logic. However, CLR triggers can be written in a .NET language and possibly take advantage of resources not easily accessible from T-SQL, such as regular expressions for data validation. CLR triggers can be used to define both DML (such as UPDATE, INSERT, and DELETE) and DDL triggers (such as CREATE TABLE). (See Chapter 13 for a discussion of DDL triggers.)

It’s important to remember when working with triggers that speed is of the essence. A trigger fires in the context of the transaction that manipulated the data. Any locks required for that data manipulation are held for the duration of the trigger’s lifetime. This means that slow triggers can create blocking problems, which can lead to severe performance and scalability issues. This concern is doubly important when working with the CLR. Triggers are not the place to contact web services, send e-mail, work with the file system, or do other synchronous tasks. Developers who need this functionality should investigate using technologies such as Service Broker. Remember that if you’re using CLR triggers, keep them simple!

Adding a CLR User-Defined Trigger to a SQL Server Project

To add a CLR trigger to an existing SQL Server project in Visual Studio 2008, right-click the project name in Solution Explorer and select Add ➤Trigger, as shown in Figure.

Adding a CLR trigger to a SQL Server project

Adding a CLR trigger to a SQL Server project

Programming CLR Triggers

Once the trigger has been added to the project, Visual Studio will add template code. The following code is the result of adding a trigger called ValidateYear:

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="ValidateYear", ➥
Target="Table1", Event="FOR UPDATE")]
public static void ValidateYear()
{
// Put your code here
}
}

This template is quite simplistic; programming a CLR trigger is very similar to programming a CLR stored procedure. The main differences between the two are the influence of the SqlTrigger attribute and the lack of a return value for triggers—the method that defines the trigger must return void. Aside from those differences, most programming paradigms hold true in both types of routines. CLR triggers, like CLR stored procedures, can make use of SqlPipe to return as many rowsets or messages to the client as the developer requires.

SqlTrigger Attribute

The SqlTrigger attribute’s primary function is to help Visual Studio or other third-party deployment tools determine for which tables and events the trigger is written. The following parameters are available for the attribute:

  • Name: This parameter indicates the name that should be used to define the trigger in the CREATE TRIGGER statement executed when the trigger is deployed. If possible, it’s generally a good idea to keep this in sync with the name of the method that defines the trigger.
  • Target: This parameter can indicate a table name in the case of DML triggers, or a database name, or the ALL SERVER keyword in the case of DDL triggers. This indicates the object that, when manipulated, will cause the trigger to fire.
  • Event: This parameter indicates what event(s) to fire on and, in the case of DML triggers, whether the trigger should fire AFTER or INSTEAD OF the event in question. Another option is FOR, which is equivalent to AFTER and is included for symmetry with the T-SQL trigger options. Note that multiple events can appear in the list, delimited by commas.

TriggerContext

The SqlContext object exposes information about the state of the trigger via the TriggerContext.This object contains properties to assist with determining why the trigger fired. The most important of these are the TriggerAction property, which maps to an enumerator by the same name that contains every possible action that can cause a trigger to fire, and the EventData property, which contains XML data useful in DDL triggers.

For example, the following code fragment would be used to execute code conditionally based on whether the trigger had fired due to an update:

if (SqlContext.TriggerContext.TriggerAction == TriggerAction.Update)
{
// do something
}

Validating a Year Using a CLR Trigger

It should be stressed once again that CLR triggers must be kept simple and quick, just like T-SQL triggers. There are few situations in which a pure CLR trigger is appropriate, given that CLR functions can be called from T-SQL triggers. As such, the example here is shown only for the sake of illustrating how to program a CLR trigger, since it represents something that should be done in a T-SQL trigger in a production environment.

An example in which a trigger (either CLR or T-SQL) can be helpful is enforcement of business rules that don’t fit neatly into CHECK constraints. For instance, a DBA might want to define a rule that any new rows inserted into the Human Resources.Department table must be inserted with a Modified Date falling in 2005. A constraint checking the ModifiedDate column would preclude any existing rows from having a date falling in that year; a trigger can be set up to operate only on newly inserted rows and is therefore a better way to enforce the rule.

The rules for this trigger will be simple: if any rows are inserted with a ModifiedDate not falling in 2005, the transaction should be rolled back, and an error should be raised. Otherwise, nothing should happen, and the transaction should be allowed to commit.

Getting the number of rows with years other than 2005 will be accomplished the same way it could be in a T-SQL trigger: the rows will be selected from the INSERTED virtual table. Both INSERTED and DELETED are available from within CLR triggers, using the context connection, as follows:

SqlConnection conn =
new SqlConnection("context connection=true");
//Define the query
string sql =
"SELECT COUNT(*) " +
"FROM INSERTED " +
"WHERE YEAR(ModifiedDate) <> 2005";
SqlCommand comm =
new SqlCommand(sql, conn);
//Open the connection
conn.Open();
//Get the number of bad rows
int numBadRows = (int)comm.ExecuteScalar();

If the number of “bad” rows is greater than zero, an error should be raised. Remember from the previous chapter that raising a clean error from the CLR can be tricky: it requires sending a RAISERROR, but wrapping the send in a try/catch block to eliminate a second error bubbling up. Finally, the transaction will be rolled back using the Transaction object. The code to do this follows:

if (numBadRows > 0)
{
//Get the SqlPipe
SqlPipe pipe = SqlContext.Pipe;
//Roll back and raise an error
comm.CommandText =
"RAISERROR('Modified Date must fall in 2005', 11, 1)";
//Send the error
try
{
pipe.ExecuteAndSend(comm);
}
catch
{
//do nothing
}
System.Transactions.Transaction.Current.Rollback();
}

Note that to use the System.Transactions namespace, a reference to the assembly must be added. To add the reference, right-click References in Solution Explorer, click Add Reference, and select System.Transactions in the Component Name column.

The complete code for the ValidateYear trigger follows:

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="ValidateYear",
Target="HumanResources.Department",
Event="FOR INSERT")]
public static void ValidateYear()
{
SqlConnection conn =
new SqlConnection("context connection=true");
//Define the query
string sql =
"SELECT COUNT(*) " +
"FROM INSERTED " +
"WHERE YEAR(ModifiedDate) <> 2005";
SqlCommand comm =
new SqlCommand(sql, conn);
//Open the connection
conn.Open();
//Get the number of bad rows
int numBadRows = (int)comm.ExecuteScalar();
if (numBadRows > 0)
{
//Get the SqlPipe
SqlPipe pipe = SqlContext.Pipe;
//Roll back and raise an error
comm.CommandText =
"RAISERROR('Modified Date must fall in 2005', 11, 1)";
//Send the error
try
{
pipe.ExecuteAndSend(comm);
}
catch
{
//do nothing
}
System.Transactions.Transaction.Current.Rollback();
}
//Close the connection
conn.Close();
}
}

Managing User-Defined Triggers

If an assembly has been loaded into the database using CREATE ASSEMBLY, triggers can be created ordropped without using the Visual Studio deployment task.

To create a trigger that is exposed in an assembly, use CREATE TRIGGER and specify the name of the assembly, the name of the class in which the trigger resides, and the name of the method that defines the trigger. The following code creates the ValidateYear trigger, from an assembly called UserDefinedTriggers, containing a class called Triggers:

CREATE TRIGGER ValidateYear
ON HumanResources.Department
FOR INSERT
AS
EXTERNAL NAME UserDefinedTriggers.Triggers.ValidateYear

To drop a trigger, use DROP TRIGGER. The following code drops the ValidateYear trigger:

DROP TRIGGER ValidateYear

The sys.triggers catalog view contains information about both T-SQL and CLR triggers. To get information about CLR triggers, filter the type column for the value TA.

SELECT *
FROM sys.triggers
WHERE type = 'TA'

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics