CLR Triggers

Because of the Microsoft SQL Server integration with the Microsoft .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 Understanding DML Triggers and Understanding DDL Triggers.

A trigger is a special type of stored procedure that automatically runs when a language event executes. SQL Server includes two general types of triggers: data manipulation language (DML) and data definition language (DDL) triggers. DML triggers can be used when INSERT, UPDATE, or DELETE statements modify data in a specified table or view. DDL triggers fire stored procedures 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 regulating database operations.

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 DDL statements.

These capabilities are provided inherently in the query language, or by the SqlTriggerContext class. For information about the advantages of CLR integration and choosing between managed code and Transact-SQL, see Overview of CLR Integration.

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

SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;

The SqlTriggerContext class provides context information about the trigger. This contextual information 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 EVENTDATA (Transact-SQL).

Determining the Trigger Action

Once you have obtained a SqlTriggerContext, you can use it to determine the type of action that 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. Please see "TriggerAction Enumeration" in the .NET Framework SDK for more information.

Using the Inserted and Deleted Tables

Two special tables are used in 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:

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 indicates that the column has been updated.

For example, this code snippet (from the EmailAudit trigger later in this topic) lists all of the columns updated:

Accessing EventData 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.

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

CREATE TRIGGER EmailAudit
ON Users
FOR INSERT, UPDATE, DELETE
AS
EXTERNAL NAME SQLCLRTest.CLRTriggers.EmailAudit

Note:

On a SQL Server 2005 database with a compatibility level of "80," you cannot create managed user-defined types, stored procedures, functions, aggregates, or triggers. To take advantage of these CLR integration features of SQL Server 2005, you must use the sp_dbcmptlevel (Transact-SQL) stored procedure to set the database compatibility level to "90.".

Using triggers to validate and cancel invalid INSERT, UPDATE, or DELETE transactions or to prevent changes to your database schema is common. This can be accomplished by incorporating validation logic into your trigger and then rolling back the current transaction if the action does not meet the validation criteria.

When called within a trigger, the Transaction.Rollback method or a SqlCommand with the command text "TRANSACTION ROLLBACK" throws an exception with an ambiguous error message and must be wrapped in a try/catch block. The error message you see is similar to the following:

Msg 6549, Level 16, State 1, Procedure trig_InsertValidator, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'trig_InsertValidator':
System.Data.SqlClient.SqlException: Transaction is not allowed to roll back inside a user defined routine, trigger or aggregate because the transaction is not started in that CLR level. Change application logic to enforce strict transaction nesting… User transaction, if any, will be rolled back.

This exception is expected and the try/catch block is necessary for code execution to continue. When the trigger code finishes execution, another exception is raised

Msg 3991, Level 16, State 1, Procedure trig_InsertValidator, Line 1
The context transaction which was active before entering user defined routine, trigger or aggregate "trig_InsertValidator" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting.
The statement has been terminated.

This exception is also expected, and a try/catch block around the Transact-SQL statement that performs the action that fires the trigger is necessary so that execution can continue. Despite the two exceptions thrown, the transaction is rolled back and the changes are not committed to the table. A major difference between CLR triggers and Transact-SQL triggers is that Transact-SQL triggers can continue to perform more work after the transaction is rolled back.

Example

The following trigger performs simple validation of INSERT statements on a table. If the inserted integer value is equal to one, the transaction is rolled back and the value is not inserted into the table. All other integer values are inserted into the table. Note the try/catch block around the Transaction.Rollback method. The Transact-SQL script creates a test table, assembly, and managed stored procedure. Note that the two INSERT statements are wrapped in a try/catch block so that the exception thrown when the trigger finishes execution is caught.