Artigo
09/28/2010

Gatilhos CLR

Por causa da integração do SQL Server com CLR (Common Language Runtime) do .NET Framework, você pode usar qualquer linguagem do .NET Framework para criar gatilhos CLR. Esta seção contém informações específicas para gatilhos implementados com a integração CLR. Para uma discussão completa sobre gatilhos, consulte Compreendendo os gatilhos DML e Compreendendo os gatilhos DDL.

O que são gatilhos?

Um gatilho é um tipo especial de procedimento armazenado executado automaticamente quando um evento de linguagem é executado. O SQL Server inclui dois tipos gerais de gatilhos: DML (data manipulation language) e DDL (data definition language). Os gatilhos do tipo DML podem ser usados quando as instruções INSERT, UPDATEou DELETE modificarem dados em uma tabela especificada ou exibição. Já os gatilhos DDL acionam procedimentos armazenados em resposta a uma variedade de instruções DDL que são instruções iniciadas, principalmente, com CREATE, ALTERe DROP. Os gatilhos DDL podem ser usados para tarefas administrativas, como operações de auditoria e regulamentação de banco de dados.

Recursos exclusivos de gatilhos CLR

Os gatilhos gravados em Transact-SQL têm a capacidade de determinar quais colunas da exibição ou tabela acionada foram atualizadas com as funções UPDATE(column) e COLUMNS_UPDATED().

Os gatilhos gravados em linguagem CLR diferem de outros objetos de integração CLR de várias maneiras. Os gatilhos CLR podem:

Fazer referência a dados nas tabelas INSERTED e DELETED
Determinar quais colunas foram modificadas como resultado de uma operação UPDATE
Acessar informações sobre objetos de banco de dados afetados pela execução de instruções DDL.

Essas capacidades são fornecidas inerentemente na linguagem de consulta, ou pela classe SqlTriggerContext. Para obter informações sobre as vantagens da integração CLR e a escolha entre o código gerenciado e Transact-SQL, consulte Visão geral da integração CLR.

Usando a classe SqlTriggerContext

A classe SqlTriggerContext não pode ser criada publicamente e só pode ser obtida por meio do acesso à propriedade SqlContext.TriggerContext dentro do corpo de um gatilho CLR. A classe SqlTriggerContext pode ser obtida do SqlContext ativo chamando a propriedade SqlContext.TriggerContext:

SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;

A classe SqlTriggerContext fornece informações de contexto sobre o gatilho. Essas informações contextuais incluem o tipo de ação que causou o acionamento do gatilho, quais colunas foram modificadas em uma operação UPDATE e, no caso de um gatilho DDL, uma estrutura XML EventData que descreve a operação de acionamento. Para obter mais informações, consulte EVENTDATA (Transact-SQL).

Determinando a ação do gatilho

Depois que você obtiver uma classe SqlTriggerContext, poderá usá-la para determinar o tipo de ação que acionou o gatilho. Essas informações estão disponíveis por meio da propriedade TriggerAction da classe SqlTriggerContext.

No caso dos gatilhos DML, a propriedade TriggerAction pode ter um dos seguintes valores:

TriggerAction.Update (0x1)
TriggerAction.Insert (0x2)
TriggerAction.Delete(0x3)
Para os gatilhos DDL, a lista de possíveis valores TriggerAction é consideravelmente mais longa. Consulte "TriggerAction Enumeration" no .NET Framework SDK para obter mais informações.

Usando as tabelas Inserted e Deleted

Duas tabelas especiais são usadas nas instruções de gatilho DML: a tabela inserted e a tabela deleted. O SQL Server cria e gerencia automaticamente essas tabelas. Você pode usar essas tabelas temporárias para testar os efeitos de determinadas modificações de dados e definir condições para ações de gatilho DML; no entanto, você não pode alterar os dados diretamente nas tabelas.

Os gatilhos CLR podem acessar as tabelas inserted e deleted por meio do provedor CLR em processo. Isso é feito obtendo-se um objeto SqlCommand do objeto SqlContext. Por exemplo:

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"

Determinando colunas atualizadas

Você pode determinar o número de colunas que foram modificadas por uma operação UPDATE usando a propriedade ColumnCount do objeto SqlTriggerContext. Você pode usar o método IsUpdatedColumn que considera o ordinal da coluna como um parâmetro de entrada, a fim de determinar se a coluna foi atualizada. Um valor True indica que a coluna foi atualizada.

Por exemplo, este trecho de código (do gatilho EmailAudit mais adiante neste tópico) lista todas as colunas atualizadas:

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()

Acessando EventData para gatilhos DDL CLR

Os gatilhos DDL, como os gatilhos normais, disparam procedimentos armazenados em resposta a um evento. Mas, ao contrário dos gatilhos DML, não disparam em resposta a instruções UPDATE, INSERT ou DELETE em uma tabela ou exibição. Em vez disso, eles são acionados em resposta a uma variedade de instruções DDL que são instruções iniciadas, principalmente, com CREATE, ALTER e DROP. Os gatilhos DDL podem ser usados para tarefas administrativas, como operações de auditoria e monitoramento de operações de banco de dados e alterações de esquema.

Informações sobre um evento que aciona um gatilho DDL estão disponíveis na propriedade EventData da classe SqlTriggerContext. Essa propriedade contém um valor xml. O esquema xml inclui informações sobre:

A hora do evento.
O SPID (System Process ID) da conexão durante a qual o gatilho é executado.
O tipo de evento que acionou o gatilho.

Assim, dependendo do tipo de evento, o esquema poderá conter ainda outras informações, como o banco de dados em que o evento ocorreu, o objeto em relação ao qual ele ocorreu e o comando Transact-SQL do evento.

No exemplo a seguir, o gatilho DDL retorna a propriedade bruta EventData.

Observação
O envio de resultados e mensagens pelo objeto SqlPipe é mostrado aqui apenas para fins ilustrativos e, em geral, não é uma prática recomendada para o código de produção durante a programação de gatilhos CLR. Talvez dados inesperados sejam retornados, o que pode levar a erros de aplicativo.

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

A seguinte saída de exemplo é o valor de propriedade EventData depois que um gatilho DDL foi acionado por um 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>AdventureWorks</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>

Além das informações acessíveis através da classe SqlTriggerContext, as consultas ainda podem fazer referência a COLUMNS_UPDATED e inserted/deleted dentro do texto de um comando executado em processo.

Exemplo de gatilho CLR

Neste exemplo, considere o cenário no qual você permite que o usuário escolha o ID que desejar, mas você deseja saber quais usuários inseriram especificamente um endereço de email como ID. O gatilho a seguir detectaria essas informações e as registraria em uma tabela de auditoria.

Observação
O envio de resultados e mensagens pelo objeto SqlPipe é mostrado aqui apenas para fins ilustrativos, não sendo, em geral, uma prática recomendada para o código de produção. Talvez dados inesperados sejam retornados, o que pode levar a erros de aplicativo

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

Suponha que existam duas tabelas com as seguintes definições:

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
)

A instrução Transact-SQL que cria o gatilho no SQL Server é apresentada como a seguir e pressupõe que o assembly SQLCLRTest já esteja registrado no banco de dados atual do SQL Server.

CREATE TRIGGER EmailAudit
ON Users
FOR INSERT, UPDATE, DELETE
AS
EXTERNAL NAME SQLCLRTest.CLRTriggers.EmailAudit

Observação
A partir do SQL Server 2005, em um banco de dados do SQL Server com um nível de compatibilidade "80", não é possível criar gatilhos, agregações, funções, procedimentos armazenados ou tipos de dados definidos pelo usuário gerenciados. Para usufruir esses recursos de integração do CLR do SQL Server, você deve usar o procedimento armazenado sp_dbcmptlevel (Transact-SQL) a fim de definir o nível de compatibilidade do banco de dados como "100".

A partir do SQL Server 2005, em um banco de dados do SQL Server com um nível de compatibilidade "80", não é possível criar gatilhos, agregações, funções, procedimentos armazenados ou tipos de dados definidos pelo usuário gerenciados. Para usufruir esses recursos de integração do CLR do SQL Server, você deve usar o procedimento armazenado sp_dbcmptlevel (Transact-SQL) a fim de definir o nível de compatibilidade do banco de dados como "100".

Validando e cancelando transações inválidas

O uso de gatilhos para validar e cancelar transações INSERT, UPDATE ou DELETE inválidas ou impedir alterações em seu esquema de banco de dados é comum. Para isso, incorpore a lógica de validação em seu gatilho e, em seguida, reverta a transação atual se a ação não corresponder aos critérios de validação.

Quando chamado em um gatilho, o método Transaction.Rollback ou um SqlCommand com o texto de comando "TRANSACTION ROLLBACK" lança uma exceção com uma mensagem de erro ambígua e deve ser encapsulado em um bloco try/catch. A mensagem de erro exibida é semelhante à seguinte:

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.

Essa exceção é esperada e o bloco try/catch é necessário para que a execução do código continue. Quando o código de gatilho termina a execução, outra exceção é gerada

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.

Essa exceção também é esperada, sendo necessário um bloco try/catch ao redor da instrução Transact-SQL que executa a ação que aciona o gatilho para que a execução possa continuar. Apesar das duas exceções lançadas, a transação é revertida e as alterações não são confirmadas na tabela. A principal diferença entre gatilhos CLR e gatilhos Transact-SQL é que os gatilhos Transact-SQL podem continuar a operar depois que a transação é revertida.

Exemplo

O gatilho a seguir executa uma validação simples de instruções INSERT em uma tabela. Se o valor inteiro inserido for igual a um, a transação será revertida e o valor não será inserido na tabela. Todos os outros valores inteiros são inseridos na tabela. Observe o bloco try/catch ao redor do método Transaction.Rollback. O script Transact-SQL cria uma tabela de teste, um assembly e um procedimento armazenado gerenciado. Observe que as duas instruções INSERT são encapsuladas em um bloco try/catch de forma que a exceção lançada quando a execução do gatilho termina é capturada.

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;

Consulte também

Compartilhar via

Gatilhos CLR

O que são gatilhos?

Recursos exclusivos de gatilhos CLR

Usando a classe SqlTriggerContext

Determinando a ação do gatilho

Usando as tabelas Inserted e Deleted

Determinando colunas atualizadas

Acessando EventData para gatilhos DDL CLR

Exemplo de gatilho CLR

Validando e cancelando transações inválidas

Exemplo

Consulte também

Referência

Conceitos

Outros recursos

Recursos adicionais