Compartilhar via

Gatilhos CLR

Devido à integração do SQL Server com o CLR (Common Language Runtime) do .NET Framework, você pode usar qualquer linguagem do .NET Framework para criar gatilhos CLR. Esta seção aborda informações específicas dos gatilhos implementados com a integração clr. Para obter uma discussão completa sobre gatilhos, consulte Gatilhos DDL.

O que são gatilhos?

Um gatilho é um tipo especial de procedimento armazenado que é executado automaticamente quando um evento de idioma é executado. O SQL Server inclui dois tipos gerais de gatilhos: DML (linguagem de manipulação de dados) e gatilhos DDL (linguagem de definição de dados). Gatilhos DML podem ser usados quando INSERT, UPDATEou DELETE instruções modificam dados em uma tabela ou exibição especificada. O DDL dispara procedimentos armazenados de incêndio em resposta a uma variedade de instruções DDL, que são principalmente instruções que começam com CREATE, ALTERe DROP. Gatilhos DDL podem ser usados para tarefas administrativas, como auditoria e regulação de operações de banco de dados.

Recursos exclusivos de gatilhos CLR

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

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

  • Dados de referência nas tabelas e DELETED nas INSERTED tabelas

  • Determinar quais colunas foram modificadas como resultado de uma UPDATE operação

  • Acesse informações sobre objetos de banco de dados afetados pela execução de instruções DDL.

Esses recursos são fornecidos inerentemente na linguagem de consulta ou pela SqlTriggerContext classe. Para obter informações sobre as vantagens da integração clr e escolher entre código gerenciado e Transact-SQL, consulte Visão geral da integração clr.

Usando a classe SqlTriggerContext

A SqlTriggerContext classe não pode ser construída publicamente e só pode ser obtida acessando a SqlContext.TriggerContext propriedade dentro do corpo de um gatilho CLR. A SqlTriggerContext classe pode ser obtida do ativo SqlContext chamando a SqlContext.TriggerContext propriedade:

SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;

A SqlTriggerContext classe fornece informações de contexto sobre o gatilho. Essas informações contextuais incluem o tipo de ação que causou o disparo 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 gatilho. Para obter mais informações, consulte EVENTDATA (Transact-SQL).

Determinando a ação de gatilho

Depois de obter um SqlTriggerContext, você pode usá-lo para determinar o tipo de ação que causou o disparo do gatilho. Essas informações estão disponíveis por meio da TriggerAction propriedade da SqlTriggerContext classe.

Para gatilhos DML, a TriggerAction propriedade pode ser um dos seguintes valores:

  • TriggerAction.Update (0x1)

  • TriggerAction.Insert (0x2)

  • TriggerAction.Delete(0x3)

  • Para gatilhos DDL, a lista de possíveis valores TriggerAction é consideravelmente maior. Consulte "Enumeração TriggerAction" no SDK do .NET Framework para obter mais informações.

Usando as tabelas inseridas e excluídas

Duas tabelas especiais são usadas em instruções de gatilho DML: a tabela inserida e a tabela excluída . O SQL Server cria e gerencia essas tabelas automaticamente. 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 nas tabelas diretamente.

Os gatilhos CLR podem acessar as tabelas inseridas e excluídas por meio do provedor clr em processo. Isso é feito obtendo um SqlCommand objeto do objeto SqlContext. Por exemplo:

C#

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 ColumnCount propriedade do SqlTriggerContext objeto. Você pode usar o IsUpdatedColumn método, que usa a coluna ordinal como um parâmetro de entrada, para determinar se a coluna foi atualizada. Um True valor indica que a coluna foi atualizada.

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

C#

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

Gatilhos DDL, como gatilhos regulares, disparam procedimentos armazenados em resposta a um evento. Mas, ao contrário dos gatilhos DML, eles não são acionados 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 principalmente instruções que começam com CREATE, ALTER e DROP. Gatilhos DDL podem ser usados para tarefas administrativas, como auditoria e monitoramento de operações de banco de dados e alterações de esquema.

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

  • A hora do evento.

  • A ID do Processo do Sistema (SPID) da conexão durante a qual o gatilho foi executado.

  • O tipo de evento que disparou o gatilho.

Em seguida, dependendo do tipo de evento, o esquema inclui informações adicionais, como o banco de dados no qual o evento ocorreu, o objeto no qual o evento ocorreu e o comando Transact-SQL do evento.

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

Observação

O envio de resultados e mensagens por meio do SqlPipe objeto é mostrado aqui apenas para fins ilustrativos e geralmente é desencorajado para o código de produção ao programar gatilhos CLR. Dados adicionais retornados podem ser inesperados e levar a erros de aplicativo.

C#

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 saída de exemplo a seguir é o valor da EventData propriedade após um gatilho DDL disparado por um CREATE TABLE evento:

<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 por meio da classe, as SqlTriggerContext consultas ainda podem se referir COLUMNS_UPDATED e inseridas/excluídas no texto de um comando executado em processo.

Gatilho CLR de exemplo

Neste exemplo, considere o cenário em que você permite que o usuário escolha qualquer ID desejada, mas você deseja conhecer os usuários que inseriram especificamente um endereço de email como uma 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 por meio do SqlPipe objeto é mostrado aqui apenas para fins ilustrativos e geralmente é desencorajado para código de produção. Dados adicionais retornados podem ser inesperados e 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

Supondo 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 é a seguinte e pressupõe que o ASSEMBLY SQLCLRTest já esteja registrado no banco de dados do SQL Server atual.

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

Validando e cancelando transações inválidas

Usar gatilhos para validar e cancelar transações INSERT, UPDATE ou DELETE inválidas ou impedir alterações no esquema de banco de dados é comum. Isso pode ser feito incorporando a lógica de validação ao gatilho e revertendo a transação atual se a ação não atender aos critérios de validação.

Quando chamado dentro de um gatilho, o Transaction.Rollback método ou um SqlCommand com o texto de comando "TRANSACTION ROLLBACK" gera 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 e um bloco try/catch em torno da instrução Transact-SQL que executa a ação que dispara o gatilho é necessário para que a execução possa continuar. Apesar das duas exceções geradas, a transação é revertida e as alterações não são confirmadas na tabela. Uma grande diferença entre gatilhos CLR e gatilhos Transact-SQL é que Transact-SQL gatilhos podem continuar a executar mais trabalho depois que a transação é revertida.

Exemplo

O gatilho a seguir executa a 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 Transaction.Rollback método. 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 para que a exceção gerada quando o gatilho concluir a execução seja capturada.

C#

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

CREATE TRIGGER (Transact-SQL)
Gatilhos DML
Gatilhos DDL
TENTAR... CATCH (Transact-SQL)
Compilando objetos de banco de dados com integração clr (Common Language Runtime)
EVENTDATA (Transact-SQL)

  • Last updated on