Como: Criar e executar um disparar CLR SQL servidor

Artigo
08/25/2010

Crie um disparador SQL adicionando um item Trigger a um projeto SQL Server.Após a implantação bem-sucedida, disparadores criados no código gerenciado são chamados e executados como qualquer outro disparador T-SQL.Disparadores escritos em uma linguagem gerenciada podem usar a classe SqlTriggerContext para obter acesso às mesmas informações que estão disponíveis para disparadores T-SQL.

Observação:
Por padrão, o recurso de integração Common linguagem tempo de execução (CLR) está desativado no Microsoft SQL servidor e deve ser ativado para usar itens de projeto do SQL servidor.Para ativar integração CLR, use o CLR enabled opção de do sp_configure procedimento armazenado.Para obter mais informações, consulte Habilitar integração CLR.

Observação:
Seu computador pode mostrar diferentes nomes ou localizações para alguns dos elementos de interface do usuário do Visual Studio nas instruções a seguir. A edição do Visual Studio que você possui e as configurações que você usa determinam esses elementos. Para obter mais informações, consulte Configurações do Visual Studio.

Criando o disparador SQL Server

Para criar um disparador SQL Server

Abra um existente Projeto do SQL servidor, ou criar um novo.Para obter mais informações, consulte Como: Criar um projeto de SQL servidor.
A partir do menu Project, selecione Add New Item.
Selecione Trigger no Adicionar Novo Item Caixa de diálogo caixa.
Digite um Name para o novo disparador.
Adicione código para executar quando o disparador for executado.Consulte o primeiro exemplo que segue este procedimento.

Observação:

Exemplos de C++ devem ser compilados com o / CLR : segurança opção do compilador.
Para Visual Basic e Visual C#, em Solution Explorer, abra a pasta TestScripts e clique duas vezes no arquivo Test.sql.

Para Visual C++, em Solution Explorer, clique duas vezes no arquivo debug.sql.
Adicione código ao arquivo Test.sql ( Debug.sql em Visual C++) para executar o disparador.Consulte o segundo exemplo que segue este procedimento.
Pressione F5 para criar, implantar, e depurar o disparador.Para obter informações sobre implantação sem depuração, consulte Como: Implantar o SQL servidor projeto Items em um SQL servidor.
Exibir os resultados que são mostrados no Janela de saída e selecionar Show output from: Banco de dados de saída .

Observação:
Exemplos de C++ devem ser compilados com o / CLR : segurança opção do compilador.

Exemplo

Este exemplo demonstra o cenário onde os usuários escolhem qualquer nome de usuário que desejarem, mas você deseja saber quais usuários inseriram um endereço de email como nome de usuário.Este disparador detecta essas informações e as registra em uma tabela de auditoria.

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

Adicione código para executar e testar seu disparador no arquivo Test.sql (debug.sql em Visual C++) na pasta TestScripts no seu projeto.Por exemplo, se você implantou um disparador, você pode testá-lo ao executar um script que insere uma nova linha na tabela na qual o disparador está definido, causando o disparo do disparador.O seguinte código depuração supõe que existem duas tabelas com as seguintes definições:

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

Consulte também

Como: Criar e executar um disparar CLR SQL servidor

Criando o disparador SQL Server

Para criar um disparador SQL Server

Exemplo

Consulte também

Tarefas

Conceitos

Referência

Outros recursos

Recursos adicionais