Desencadenadores CLR
Debido a la integración de SQL Server con .NET Framework Common Language Runtime (CLR), es posible usar cualquier lenguaje .NET Framework para crear desencadenadores CLR. En esta sección se incluye información específica acerca de los desencadenadores implementados con la integración CLR. Para obtener más información acerca de los desencadenadores, vea Descripción de los desencadenadores DML y Descripción de los desencadenadores DDL.
Qué son los desencadenadores
Un desencadenador es un tipo especial de procedimiento almacenado que se ejecuta automáticamente cuando se produce un evento de lenguaje. SQL Server incluye dos tipos de desencadenadores: desencadenadores del lenguaje de manipulación de datos (DML) y desencadenadores del lenguaje de definición de datos (DDL). Los desencadenadores DML pueden usarse cuando las instrucciones INSERT, DELETE o UPDATE modifican los datos de una tabla o vista especificada. Los desencadenadores DDL activan procedimientos almacenados en respuesta a una serie de instrucciones DDL, principalmente instrucciones que comienzan por CREATE, ALTER y DROP. Los desencadenadores DDL pueden usarse en tareas administrativas, como la auditoría y regulación de operaciones de base de datos.
Capacidades únicas de los desencadenadores CLR
Los desencadenadores escritos en Transact-SQL poseen la capacidad de determinar las columnas de la vista o de la tabla de activación que se han actualizado mediante el uso de las funciones COLUMNS_UPDATED() y UPDATE(column).
Los desencadenadores escritos en lenguaje CLR se diferencian de otros objetos de integración CLR de varias formas significativas. Los desencadenadores CLR pueden:
Hacer referencia a datos de las tablas INSERTED y DELETED
Determinar las columnas que se han modificado como resultado de una operación UPDATE
Obtener acceso a información acerca de los objetos de base de datos afectados por la ejecución de instrucciones DDL.
Estas capacidades se proporcionan de forma inherente en el lenguaje de consulta o por medio de la clase SqlTriggerContext. Para obtener información acerca de las ventajas de la integración CLR y el modo de elegir entre código administrado y Transact-SQL, vea Información general de la integración CLR.
Usar la clase SqlTriggerContext
La clase SqlTriggerContext no puede construirse públicamente y solo puede obtenerse mediante el acceso a la propiedad SqlContext.TriggerContext dentro del cuerpo de un desencadenador CLR. La clase SqlTriggerContext puede obtenerse a partir del contexto SqlContext activo mediante una llamada a la propiedad SqlContext.TriggerContext:
SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;
La clase SqlTriggerContext proporciona información de contexto acerca del desencadenador. Esta información contextual incluye el tipo de acción que provocó la activación del desencadenador, las columnas que se modificaron en una operación UPDATE y, en el caso de un desencadenador DDL, una estructura XML EventData que describe la operación de desencadenamiento. Para obtener más información, vea EVENTDATA (Transact-SQL).
Determinar la acción del desencadenador
Una vez que ha obtenido un SqlTriggerContext, puede usarlo para determinar el tipo de acción que provocó que se activara el desencadenador. Esta información se encuentra disponible a través de la propiedad TriggerAction de la clase SqlTriggerContext.
En el caso de los desencadenadores DML, la propiedad TriggerAction puede tener uno de los siguientes valores:
TriggerAction.Update (0x1)
TriggerAction.Insert (0x2)
TriggerAction.Delete (0x3)
En el caso de los desencadenadores DDL, la lista de valores posibles de TriggerAction es bastante más larga. Para obtener más información, vea el tema relativo a la enumeración TriggerAction en el SDK de .NET Framework.
Usar las tablas insertadas y eliminadas
En las instrucciones de desencadenadores DML se usan dos tablas especiales: la tabla inserted y la tabla deleted. SQL Server crea y administra estas tablas de forma automática. Puede usar estas tablas temporales para probar los efectos de determinadas modificaciones en los datos y para establecer condiciones para las acciones de los desencadenadores DML; sin embargo, no es posible modificar los datos de las tablas directamente.
Los desencadenadores CLR pueden obtener acceso a las tablas inserted y deleted a través del proveedor CLR en proceso. Para ello, se obtiene un objeto SqlCommand del objeto SqlContext. Por ejemplo:
C#
SqlConnection connection = new SqlConnection ("context connection = true");
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * from " + "inserted";
Visual Basic
Dim connection As New SqlConnection("context connection=true")
Dim command As SqlCommand
connection.Open()
command = connection.CreateCommand()
command.CommandText = "SELECT * FROM " + "inserted"
Determinar las columnas actualizadas
Puede determinar el número de columnas que se han modificado en una operación UPDATE mediante el uso de la propiedad ColumnCount del objeto SqlTriggerContext. Puede usar el método IsUpdatedColumn, que toma el ordinal de la columna como parámetro de entrada, para determinar si la columna se ha actualizado. Un valor True indica que la columna se ha actualizado.
Por ejemplo, este fragmento de código (del desencadenador EmailAudit que se muestra más adelante en este tema) muestra todas las columnas actualizadas:
C#
reader = command.ExecuteReader();
reader.Read();
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
{
pipe.Send("Updated column "
+ reader.GetName(columnNumber) + "? "
+ triggContext.IsUpdatedColumn(columnNumber).ToString());
}
reader.Close();
Visual Basic
reader = command.ExecuteReader()
reader.Read()
Dim columnNumber As Integer
For columnNumber=0 To triggContext.ColumnCount-1
pipe.Send("Updated column " & reader.GetName(columnNumber) & _
"? " & triggContext.IsUpdatedColumn(columnNumber).ToString() )
Next
reader.Close()
Obtener acceso a EventData para desencadenadores DDL de CLR
Los desencadenadores DDL, al igual que los desencadenadores normales, activan procedimientos almacenados en respuesta a un evento. Pero, a diferencia de los desencadenadores DML, no se activan en respuesta a instrucciones UPDATE, INSERT o DELETE en una tabla o vista. En lugar de ello, se activan en respuesta a una serie de instrucciones DDL, principalmente instrucciones que comienzan por CREATE, ALTER y DROP. Los desencadenadores DDL pueden usarse en tareas administrativas, como la auditoría y supervisión de operaciones de base de datos y cambios de esquema.
Encontrará información acerca de un evento que activa un desencadenador DDL en la propiedad EventData de la clase SqlTriggerContext. Esta propiedad contiene un valor xml. El esquema XML incluye información acerca de:
La hora del evento.
El Id. de proceso del sistema (SPID) de la conexión durante la que se ha ejecutado el desencadenador.
El tipo de evento que ha activado el desencadenador.
A continuación, en función del tipo de evento, el esquema incluirá información adicional, como la base de datos en la que se ha producido el evento, el objeto en el que se ha producido el evento y el comando Transact-SQL del evento.
En el ejemplo que se muestra a continuación, el siguiente desencadenador DDL devuelve la propiedad EventData sin formato.
Nota
El envío de resultados y mensajes a través del objeto SqlPipe se muestra aquí únicamente como ejemplo y, en general, no se aconseja para el código de producción cuando se programan desencadenadores CLR. Otros datos devueltos pueden ser inesperados y conducir a errores en la aplicación.
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
El siguiente resultado de ejemplo es el valor de la propiedad EventData tras la activación de un desencadenador DDL por parte de un evento CREATE TABLE
<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>AdventureWorks2008R2</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>
Además de la información accesible a través de la clase SqlTriggerContext, las consultas pueden seguir haciendo referencia a COLUMNS_UPDATED y inserted/deleted dentro del texto de un comando que se ejecuta en proceso.
Desencadenador CLR de ejemplo
Para este ejemplo, considere un escenario donde se permita que el usuario elija el identificador que desee, pero usted desea saber qué usuarios en concreto han especificado una dirección de correo electrónico como identificador. El siguiente desencadenador detectará esa información y la registrará en una tabla de auditoría.
Nota
El envío de resultados y mensajes a través del objeto SqlPipe se muestra aquí únicamente como ejemplo y, en general, no se aconseja para el código de producción. Otros datos devueltos pueden ser inesperados y conducir a errores en la aplicación.
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
{
[SqlTrigger(Name = @"EmailAudit", Target = "[dbo].[Users]", Event = "FOR INSERT, UPDATE, DELETE")]
public static void EmailAudit()
{
string userName;
string realName;
SqlCommand command;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM INSERTED;",
connection);
reader = command.ExecuteReader();
reader.Read();
userName = (string)reader[0];
realName = (string)reader[1];
reader.Close();
if (IsValidEMailAddress(userName))
{
command = new SqlCommand(
@"INSERT [dbo].[UserNameAudit] VALUES ('"
+ userName + @"', '" + realName + @"');",
connection);
pipe.Send(command.CommandText);
command.ExecuteNonQuery();
pipe.Send("You inserted: " + userName);
}
}
break;
case TriggerAction.Update:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM INSERTED;",
connection);
reader = command.ExecuteReader();
reader.Read();
userName = (string)reader[0];
realName = (string)reader[1];
pipe.Send(@"You updated: '" + userName + @"' - '"
+ realName + @"'");
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
{
pipe.Send("Updated column "
+ reader.GetName(columnNumber) + "? "
+ triggContext.IsUpdatedColumn(columnNumber).ToString());
}
reader.Close();
}
break;
case TriggerAction.Delete:
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM DELETED;",
connection);
reader = command.ExecuteReader();
if (reader.HasRows)
{
pipe.Send(@"You deleted the following rows:");
while (reader.Read())
{
pipe.Send(@"'" + reader.GetString(0)
+ @"', '" + reader.GetString(1) + @"'");
}
reader.Close();
//alternately, to just send a tabular resultset back:
//pipe.ExecuteAndSend(command);
}
else
{
pipe.Send("No rows affected.");
}
}
break;
}
}
public static bool IsValidEMailAddress(string email)
{
return Regex.IsMatch(email, @"^([\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
<SqlTrigger(Name:="EmailAudit", Target:="[dbo].[Users]", Event:="FOR INSERT, UPDATE, DELETE")> _
Public Shared Sub EmailAudit()
Dim userName As String
Dim realName As String
Dim command As SqlCommand
Dim triggContext As SqlTriggerContext
Dim pipe As SqlPipe
Dim reader As SqlDataReader
triggContext = SqlContext.TriggerContext
pipe = SqlContext.Pipe
Select Case triggContext.TriggerAction
Case TriggerAction.Insert
Using connection As New SqlConnection("context connection=true")
connection.Open()
command = new SqlCommand("SELECT * FROM INSERTED;", connection)
reader = command.ExecuteReader()
reader.Read()
userName = CType(reader(0), String)
realName = CType(reader(1), String)
reader.Close()
If IsValidEmailAddress(userName) Then
command = New SqlCommand("INSERT [dbo].[UserNameAudit] VALUES ('" & _
userName & "', '" & realName & "');", connection)
pipe.Send(command.CommandText)
command.ExecuteNonQuery()
pipe.Send("You inserted: " & userName)
End If
End Using
Case TriggerAction.Update
Using connection As New SqlConnection("context connection=true")
connection.Open()
command = new SqlCommand("SELECT * FROM INSERTED;", connection)
reader = command.ExecuteReader()
reader.Read()
userName = CType(reader(0), String)
realName = CType(reader(1), String)
pipe.Send("You updated: " & userName & " - " & realName)
Dim columnNumber As Integer
For columnNumber=0 To triggContext.ColumnCount-1
pipe.Send("Updated column " & reader.GetName(columnNumber) & _
"? " & triggContext.IsUpdatedColumn(columnNumber).ToString() )
Next
reader.Close()
End Using
Case TriggerAction.Delete
Using connection As New SqlConnection("context connection=true")
connection.Open()
command = new SqlCommand("SELECT * FROM DELETED;", connection)
reader = command.ExecuteReader()
If reader.HasRows Then
pipe.Send("You deleted the following rows:")
While reader.Read()
pipe.Send( reader.GetString(0) & ", " & reader.GetString(1) )
End While
reader.Close()
' Alternately, just send a tabular resultset back:
' pipe.ExecuteAndSend(command)
Else
pipe.Send("No rows affected.")
End If
End Using
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
Suponiendo que existen dos tablas con las siguientes definiciones:
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
)
La instrucción Transact-SQL creada por el desencadenador en SQL Server presenta la siguiente forma y se asume que el ensamblado SQLCLRTest ya está registrado en la base de datos SQL Server actual.
CREATE TRIGGER EmailAudit
ON Users
FOR INSERT, UPDATE, DELETE
AS
EXTERNAL NAME SQLCLRTest.CLRTriggers.EmailAudit
Nota
A partir de SQL Server 2005, en una base de datos SQL Server con un nivel de compatibilidad de "80", no pueden crearse tipos administrados definidos por el usuario, procedimientos almacenados, funciones, agregados o desencadenadores. Para aprovechar las características de integración CLR de SQL Server, debe usar el procedimiento almacenado sp_dbcmptlevel (Transact-SQL) a fin de establecer el nivel de compatibilidad de la base de datos en "100".
Validar y cancelar las transacciones no válidas
Es normal usar desencadenadores para validar y cancelar transacciones INSERT, UPDATE o DELETE no válidas o para impedir que se realicen cambios en el esquema de base de datos. Esto puede realizarse incorporando una lógica de validación en el desencadenador y, a continuación, revirtiendo la transacción actual si la acción no cumple los criterios de validación.
Cuando se llama al método Transaction.Rollback o a un objeto SqlCommand con el texto de comando "TRANSACTION ROLLBACK" dentro de un desencadenador, se inicia una excepción con un mensaje de error ambiguo y debe incluirse dentro de un bloque try/catch. El mensaje de error que aparece es similar al siguiente:
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.
Se espera esta excepción y el bloque try/catch resulta necesario para que continúe la ejecución del código. Cuando finaliza la ejecución del código del desencadenador, se inicia otra excepción:
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.
También se espera esta excepción y es necesario incluir un bloque try/catch alrededor de la instrucción Transact-SQL que realiza la acción que activa el desencadenador para que la ejecución pueda continuar. A pesar de las dos excepciones iniciadas, la transacción se revierte y no se confirman los cambios en la tabla. Una diferencia importante entre los desencadenadores CLR y los desencadenadores Transact-SQL es que los desencadenadores Transact-SQL pueden seguir realizando más trabajo una vez que se ha revertido la transacción.
Ejemplo
El desencadenador siguiente realiza una validación simple de instrucciones INSERT en una tabla. Si el valor entero insertado es igual a uno, la transacción se revierte y el valor no se inserta en la tabla. El resto de los valores enteros se insertan en la tabla. Tenga en cuenta el bloque try/catch alrededor del método Transaction.Rollback. El script Transact-SQL crea una tabla de prueba, un ensamblado y un procedimiento almacenado administrado. Tenga en cuenta que las dos instrucciones INSERT se incluyen en un bloque try/catch, de modo que la excepción iniciada se detecta al finalizar la ejecución del desencadenador.
C#
using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Transactions;
public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
// [Microsoft.SqlServer.Server.SqlTrigger (Name="trig_InsertValidator", Target="Table1", Event="FOR INSERT")]
public static void trig_InsertValidator()
{
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
SqlCommand command;
SqlDataReader reader;
int value;
// Open the connection.
connection.Open();
// Get the inserted value.
command = new SqlCommand(@"SELECT * FROM INSERTED", connection);
reader = command.ExecuteReader();
reader.Read();
value = (int)reader[0];
reader.Close();
// Rollback the transaction if a value of 1 was inserted.
if (1 == value)
{
try
{
// Get the current transaction and roll it back.
Transaction trans = Transaction.Current;
trans.Rollback();
}
catch (SqlException ex)
{
// Catch the expected exception.
}
}
else
{
// Perform other actions here.
}
// Close the connection.
connection.Close();
}
}
}
Visual Basic
Imports System
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Transactions
Partial Public Class Triggers
' Enter existing table or view for the target and uncomment the attribute line
' <Microsoft.SqlServer.Server.SqlTrigger(Name:="trig_InsertValidator", Target:="Table1", Event:="FOR INSERT")> _
Public Shared Sub trig_InsertValidator ()
Using connection As New SqlConnection("context connection=true")
Dim command As SqlCommand
Dim reader As SqlDataReader
Dim value As Integer
' Open the connection.
connection.Open()
' Get the inserted value.
command = New SqlCommand("SELECT * FROM INSERTED", connection)
reader = command.ExecuteReader()
reader.Read()
value = CType(reader(0), Integer)
reader.Close()
' Rollback the transaction if a value of 1 was inserted.
If value = 1 Then
Try
' Get the current transaction and roll it back.
Dim trans As Transaction
trans = Transaction.Current
trans.Rollback()
Catch ex As SqlException
' Catch the exception.
End Try
Else
' Perform other actions here.
End If
' Close the connection.
connection.Close()
End Using
End Sub
End Class
Transact-SQL
-- Create the test table, assembly, and trigger.
create table Table1(c1 int);
go
CREATE ASSEMBLY ValidationTriggers from 'E:\programming\ ValidationTriggers.dll';
go
CREATE TRIGGER trig_InsertValidator
ON Table1
FOR INSERT
AS EXTERNAL NAME ValidationTriggers.Triggers.trig_InsertValidator;
go
-- Use a Try/Catch block to catch the expected exception
BEGIN TRY
insert into Table1 values(42)
insert into Table1 values(1)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNum, ERROR_MESSAGE() AS ErrorMessage
END CATCH;
-- Clean up.
DROP TRIGGER trig_InsertValidator;
DROP ASSEMBLY ValidationTriggers;
drop table Table1;
Vea también