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.

Crear el desencadenador de SQL Server

Para crear un desencadenador de SQL Server

1. 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.

2. En el menú Proyecto, seleccione Agregar nuevo elemento.

3. En el cuadro de diálogo Agregar nuevo elemento, seleccione Desencadenador.

4. Escriba un Nombre para el nuevo desencadenador.

5. Agregue código al ejecutar el desencadenador. Vea el primer ejemplo incluido después de este procedimiento.

6. 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.

7. Agregue código al archivo Test.sql para ejecutar el desencadenador. Vea el segundo ejemplo incluido después de este procedimiento.

8. 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.

   Importante

   SQL 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.

9. 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 un proyecto para objetos de base de datos que usan la integración de Common Language Runtime de SQL Server

Cómo: Crear y ejecutar un procedimiento almacenado de SQL Server mediante la integración de Common Language Runtime

Cómo: Crear y ejecutar un agregado de SQL Server mediante la integración de Common Language Runtime

Cómo: Crear y ejecutar una función definida por el usuario de SQL Server mediante la integración de Common Language Runtime

Cómo: Crear y ejecutar un tipo definido por el usuario 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

Otros recursos

SQL CLR Database Debugging