Cómo: Crear y ejecutar un desencadenador de SQL Server mediante la integración de Common Language Runtime
Para crear un desencadenador de SQL, agregue un elemento Desencadenador a un proyecto de base de datos de Common Language Runtime de SQL Server (CLR de SQL). Después de una implementación correcta, a los desencadenadores creados en código administrado se les llama y ejecuta como cualquier otro desencadenador de Transact-SQL. Los desencadenadores que se escriben en un lenguaje administrado pueden usar la clase SqlTriggerContext para obtener acceso a la misma información que está disponible para los desencadenadores de Transact-SQL.
Nota
Es posible que su equipo muestre nombres o ubicaciones diferentes para algunos de los elementos de la interfaz de usuario de Visual Studio incluidos en las instrucciones siguientes. La edición de Visual Studio que se tenga y la configuración que se utilice determinan estos elementos. Para obtener más información, vea Valores de configuración de Visual Studio.
Crear el desencadenador de SQL Server
Para crear un desencadenador de SQL Server
Abra un proyecto de base de datos CLR de SQL existente o cree uno nuevo. Para obtener más información, vea Cómo: Crear un proyecto para objetos de base de datos que usan la integración de Common Language Runtime de SQL Server.
En el menú Proyecto, seleccione Agregar nuevo elemento.
En el cuadro de diálogo Agregar nuevo elemento, seleccione Desencadenador.
Escriba un Nombre para el nuevo desencadenador.
Agregue código al ejecutar el desencadenador. Vea el primer ejemplo incluido después de este procedimiento.
En el Explorador de soluciones, abra la carpeta SecuenciasDePrueba y haga doble clic en el archivo Test.sql.
Nota
Puede especificar otros scripts como script de depuración predeterminado. Para obtener más información, vea Cómo: Editar el script Test.sql para ejecutar objetos que usan la integración de Common Language Runtime de SQL Server.
Agregue código al archivo Test.sql para ejecutar el desencadenador. Vea el segundo ejemplo incluido después de este procedimiento.
Presione F5 para generar, implementar y depurar el desencadenador. Para obtener información sobre cómo implementar sin depurar, vea Cómo: Implementar elementos de proyecto de base de datos CLR de SQL en un servidor SQL Server.
.gif "Nota importante")
ImportanteSQL Server 2005 y SQL Server 2008 solo admiten proyectos de SQL Server compilados con las versiones 2.0, 3.0 ó 3.5 de .NET Framework. Si intenta implementar un proyecto de SQL Server en SQL Server 2005 o SQL Server 2008, aparece un error: Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database (donde AssemblyName es el nombre del ensamblado que se implementa). Para obtener más información, vea Cómo: Crear un proyecto para objetos de base de datos que usan la integración de Common Language Runtime de SQL Server.
View the results that are shown in the Resultados (Ventana) and select Show output from: Database Output.
Ejemplo
En este ejemplo se muestra el escenario en el que los usuarios eligen el nombre de usuario que desean, pero quizá prefiera saber los usuarios que especificaron una dirección de correo electrónico como nombre de usuario. Este desencadenador detecta esa información y la registra en una tabla de auditoría.
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
Imports Microsoft.SqlServer.Server
Partial Public Class Triggers
<SqlTrigger(Name:="UserNameAudit", Target:="Users", Event:="FOR INSERT")>
Public Shared Sub UserNameAudit()
Dim triggContext As SqlTriggerContext = SqlContext.TriggerContext()
Dim userName As New SqlParameter("@username", SqlDbType.NVarChar)
If triggContext.TriggerAction = TriggerAction.Insert Then
Using conn As New SqlConnection("context connection=true")
conn.Open()
Dim sqlComm As New SqlCommand
Dim sqlP As SqlPipe = SqlContext.Pipe()
sqlComm.Connection = conn
sqlComm.CommandText = "SELECT UserName from INSERTED"
userName.Value = sqlComm.ExecuteScalar.ToString()
If IsEMailAddress(userName.ToString) Then
sqlComm.CommandText = "INSERT UsersAudit(UserName) VALUES(username)"
sqlP.Send(sqlComm.CommandText)
sqlP.ExecuteAndSend(sqlComm)
End If
End Using
End If
End Sub
Public Shared Function IsEMailAddress(ByVal s As String) As Boolean
Return Regex.IsMatch(s, "^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$")
End Function
End Class
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
public partial class Triggers
{
[SqlTrigger(Name="UserNameAudit", Target="Users", Event="FOR INSERT")]
public static void UserNameAudit()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlParameter userName = new SqlParameter("@username", System.Data.SqlDbType.NVarChar);
if (triggContext.TriggerAction == TriggerAction.Insert)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand sqlComm = new SqlCommand();
SqlPipe sqlP = SqlContext.Pipe;
sqlComm.Connection = conn;
sqlComm.CommandText = "SELECT UserName from INSERTED";
userName.Value = sqlComm.ExecuteScalar().ToString();
if (IsEMailAddress(userName.ToString()))
{
sqlComm.CommandText = "INSERT UsersAudit(UserName) VALUES(userName)";
sqlP.Send(sqlComm.CommandText);
sqlP.ExecuteAndSend(sqlComm);
}
}
}
}
public static bool IsEMailAddress(string s)
{
return Regex.IsMatch(s, "^([\\w-]+\\.)*?[\\w-]+@[\\w-]+\\.([\\w-]+\\.)*?[\\w]+$");
}
}
Agregue código para ejecutar y probar el desencadenador al archivo Test.sql de la carpeta SecuenciasDePrueba de su proyecto. Por ejemplo, si implementó un desencadenador, puede probarlo mediante la ejecución de un script que inserta una nueva fila en la tabla en la que se encuentra establecido el desencadenador, provocando que éste se active. El código de depuración siguiente supone que existen dos tablas con las definiciones siguientes:
CREATE TABLE Users
(
UserName NVARCHAR(200) NOT NULL,
Pass NVARCHAR(200) NOT NULL
)
CREATE TABLE UsersAudit
(
UserName NVARCHAR(200) NOT NULL
)
-- Insert one user name that is not an e-mail address and one that is
INSERT INTO Users(UserName, Pass) VALUES(N'someone', N'cnffjbeq')
INSERT INTO Users(UserName, Pass) VALUES(N'someone@example.com', N'cnffjbeq')
-- check the Users and UsersAudit tables to see the results of the trigger
select * from Users
select * from UsersAudit
Vea también
Tareas
Cómo: Crear y ejecutar un agregado de SQL Server mediante la integración de Common Language Runtime
Tutorial: Crear un procedimiento almacenado en código administrado
Cómo: Depurar un procedimiento almacenado de SQL CLR
Referencia
Atributos para proyectos de base de datos CLR de SQL Server y objetos de base de datos
Conceptos
Introducción a la integración de CLR y SQL Server (ADO.NET)
Ventajas de utilizar código administrado para crear objetos de base de datos
Crear objetos de SQL Server en código administrado