Cómo: Crear y ejecutar un desencadenador CLR de SQL Server

Artículo
10/07/2008

Actualización: noviembre 2007

Cree un desencadenador de SQL agregando un elemento Desencadenador a un proyecto de SQL Server. 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 T-SQL. Los desencadenadores escritos un lenguaje administrado pueden utilizar la clase SqlTriggerContext para obtener acceso a la misma información que está disponible para los desencadenadores de T-SQL.

Nota:
De forma predeterminada, la característica de integración de Common Language Runtime (CLR) está desactivada en Microsoft SQL Server y se debe habilitar con el fin de utilizar los elementos de los proyectos de SQL Server. Para habilitar la integración CLR, utilice la opción clr enabled del procedimiento almacenado sp_configure. Para obtener más información, vea Habilitación de la integración CLR.

De forma predeterminada, la característica de integración de Common Language Runtime (CLR) está desactivada en Microsoft SQL Server y se debe habilitar con el fin de utilizar los elementos de los proyectos de SQL Server. Para habilitar la integración CLR, utilice la opción clr enabled del procedimiento almacenado sp_configure. Para obtener más información, vea Habilitación de la integración CLR.

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 SQL Server existente o cree uno nuevo. Para obtener más información, vea Cómo: Crear un proyecto de SQL Server.
En el menú Proyecto, seleccione Agregar nuevo elemento.
Seleccione Desencadenador en el Agregar nuevo elemento (Cuadro de diálogo).
Escriba un Nombre para el nuevo desencadenador.
Agregue código al ejecutar el desencadenador. Vea el primer ejemplo incluido después de este procedimiento.

Nota:

Los ejemplos en C++ se deben compilar con la opción /clr:safe del compilador.
Para Visual Basic y Visual C#, en el Explorador de soluciones, abra la carpeta SecuenciasDePrueba y haga doble clic en el archivo Test.sql.

Para Visual C++, en el Explorador de soluciones, haga doble clic en el archivo debug.sql.
Agregue el código al archivo Test.sql (debug.sql en Visual C++) 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 SQL Server en un servidor SQL Server.
Vea los resultados que se muestran en la Resultados (Ventana) y seleccione Mostrar resultados desde: Resultado de base de datos.

Nota:
Los ejemplos en C++ se deben compilar con la opción /clr:safe del compilador.

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]+$");
    }
}

#include "stdafx.h"

#using <System.dll>
#using <System.Data.dll>
#using <System.Xml.dll>

using namespace System;
using namespace System::Data;
using namespace System::Data::Sql;
using namespace System::Data::SqlClient;
using namespace System::Data::SqlTypes;
using namespace System::Text::RegularExpressions;
using namespace Microsoft::SqlServer::Server;

// In order to debug your Trigger, add the following to your debug.sql file:
//
// -- 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
//

public ref class AddNewTrigger
{
public:
    [SqlTrigger(Name="UserNameAudit", Target="Users", Event="FOR INSERT")]
    static void UserNameAudit()
    {
        SqlTriggerContext ^triggContext = SqlContext::TriggerContext;
        SqlParameter ^userName = gcnew SqlParameter("@username", System::Data::SqlDbType::NVarChar);

        if (triggContext->TriggerAction == TriggerAction::Insert)
        {
            SqlConnection ^conn = gcnew SqlConnection("context connection=true");
            conn->Open();
            SqlCommand ^sqlComm = gcnew 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);
            }

            conn->Close();
        }
    }

    static bool IsEMailAddress(String ^s)
    {
        return Regex::IsMatch(s, "^([\\w-]+\\.)*?[\\w-]+@[\\w-]+\\.([\\w-]+\\.)*?[\\w]+$");
    }
};

Agregue código para ejecutar y probar su desencadenador al archivo Test.sql (debug.sql en Visual C++) en 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

Cómo: Crear y ejecutar un desencadenador CLR de SQL Server

Crear el desencadenador de SQL Server

Para crear un desencadenador de SQL Server

Ejemplo

Vea también

Tareas

Conceptos

Referencia

Otros recursos

Recursos adicionales