Поделиться через

Триггеры CLR

Из-за интеграции SQL Server с средой CLR .NET Framework можно использовать любой язык .NET Framework для создания триггеров СРЕДЫ CLR. В этом разделе рассматриваются сведения, относящиеся к триггерам, реализованным с интеграцией СРЕДЫ CLR. Полное обсуждение триггеров см. в разделе "Триггеры DDL".

Что такое триггеры?

Триггер — это специальный тип хранимой процедуры, которая автоматически выполняется при выполнении языкового события. SQL Server включает два общих типа триггеров: язык обработки данных (DML) и триггеры языка определения данных (DDL). Триггеры DML можно использовать, когда INSERTUPDATEоператоры или DELETE операторы изменяют данные в указанной таблице или представлении. DDL активирует хранимые процедуры в ответ на различные инструкции DDL, которые в первую очередь являются операторами, начинающимися с CREATE, ALTERи DROP. Триггеры DDL можно использовать для административных задач, таких как аудит и регулирование операций базы данных.

Уникальные возможности триггеров СРЕДЫ CLR

Триггеры, написанные в Transact-SQL, имеют возможность определить, какие столбцы из представления стрельбы или таблицы были обновлены с помощью UPDATE(column) функций и COLUMNS_UPDATED() функций.

Триггеры, написанные на языке CLR, отличаются от других объектов интеграции CLR несколькими значительными способами. Триггеры СРЕДЫ CLR могут:

  • Справочные данные в таблицах INSERTED и DELETED таблицах

  • Определение столбцов, измененных в результате UPDATE операции

  • Доступ к сведениям о объектах базы данных, затронутых выполнением инструкций DDL.

Эти возможности предоставляются по сути в языке запросов или SqlTriggerContext в классе. Сведения о преимуществах интеграции СРЕДЫ CLR и выборе между управляемым кодом и Transact-SQL см. в разделе "Обзор интеграции СРЕДЫ CLR".

Использование класса SqlTriggerContext

Класс SqlTriggerContext не может быть создан публично и может быть получен только путем доступа к SqlContext.TriggerContext свойству в теле триггера CLR. Класс SqlTriggerContext можно получить из активного SqlContext , вызвав SqlContext.TriggerContext свойство:

SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;

Класс SqlTriggerContext предоставляет сведения о контексте триггера. Эти контекстные сведения включают тип действия, вызвавшего срабатывание триггера, которые были изменены в операции UPDATE, и, в случае триггера DDL, xml-структура, EventData описывающая операцию активации. Дополнительные сведения см. в разделе EVENTDATA (Transact-SQL).

Определение действия триггера

После получения SqlTriggerContextего можно использовать для определения типа действия, вызвавшего срабатывание триггера. Эта информация доступна через TriggerAction свойство SqlTriggerContext класса.

Для триггеров TriggerAction DML свойство может быть одним из следующих значений:

  • TriggerAction.Update (0x1)

  • TriggerAction.Insert (0x2)

  • TriggerAction.Delete(0x3)

  • Для триггеров DDL список возможных значений TriggerAction значительно длиннее. Дополнительные сведения см. в разделе "Перечисление TriggerAction" в пакете SDK для .NET Framework.

Использование вставленных и удаленных таблиц

В инструкциях триггера DML используются две специальные таблицы: вставленная таблица и удаленная таблица. SQL Server автоматически создает и управляет этими таблицами. Эти временные таблицы можно использовать для проверки влияния определенных изменений данных и задания условий для действий триггера DML; однако нельзя напрямую изменить данные в таблицах.

Триггеры СРЕДЫ CLR могут получить доступ к вставленным и удаленным таблицам через поставщика среды CLR в процессе. Это делается путем получения SqlCommand объекта из объекта SqlContext. Рассмотрим пример.

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"

Определение обновленных столбцов

Можно определить количество столбцов, измененных операцией UPDATE, с помощью ColumnCount свойства SqlTriggerContext объекта. Этот метод можно использовать IsUpdatedColumn , который принимает порядковый номер столбца в качестве входного параметра, чтобы определить, был ли обновлен столбец. Значение True указывает, что столбец обновлен.

Например, этот фрагмент кода (из триггера EmailAudit далее в этом разделе) перечисляет все обновленные столбцы:

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

Доступ к EventData для триггеров DDL среды CLR

Триггеры DDL, такие как обычные триггеры, запускают хранимые процедуры в ответ на событие. Но в отличие от триггеров DML, они не запускаются в ответ на инструкции UPDATE, INSERT или DELETE в таблице или представлении. Вместо этого они запускаются в ответ на различные инструкции DDL, которые в первую очередь являются операторами, начинающимися с CREATE, ALTER и DROP. Триггеры DDL можно использовать для административных задач, таких как аудит и мониторинг операций базы данных и изменения схемы.

Сведения о событии, которое запускает триггер DDL, доступно в EventData свойстве SqlTriggerContext класса. Это свойство содержит xml значение. Схема XML содержит сведения о следующих параметрах:

  • время события.

  • Идентификатор системного процесса (SPID) подключения, во время которого выполняется триггер.

  • тип события, которое привело к срабатыванию триггера.

Затем в зависимости от типа события схема содержит дополнительные сведения, такие как база данных, в которой произошло событие, объект, к которому произошло событие, и команда Transact-SQL события.

В следующем примере следующий триггер DDL возвращает необработанное EventData свойство.

Замечание

Отправка результатов и сообщений через SqlPipe объект отображается здесь только для иллюстрирующих целей и обычно не рекомендуется для рабочего кода при программировании триггеров CLR. Дополнительные возвращаемые данные могут быть непредвиденными и привести к ошибкам приложения.

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

Следующий пример выходных данных — это EventData значение свойства после триггера DDL, запущенного событием 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>

Помимо информации, доступной через SqlTriggerContext класс, запросы по-прежнему могут ссылаться на COLUMNS_UPDATED и вставлять и удаляться в тексте команды, выполняемой внутри процесса.

Пример триггера CLR

В этом примере рассмотрим сценарий, в котором пользователь может выбрать нужный идентификатор, но вы хотите знать, какие пользователи ввели адрес электронной почты в качестве идентификатора. Следующий триггер обнаруживает эти сведения и регистрирует его в таблице аудита.

Замечание

Отправка результатов и сообщений через SqlPipe объект отображается здесь только для иллюстрирующих целей и обычно не рекомендуется для рабочего кода. Дополнительные возвращаемые данные могут быть непредвиденными и привести к ошибкам приложения

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

Предположим, что две таблицы существуют со следующими определениями:

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  
)

Инструкция Transact-SQL, создающая триггер в SQL Server, выглядит следующим образом. Предполагается, что сборка SQLCLRTest уже зарегистрирована в текущей базе данных SQL Server.

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

Проверка и отмена недопустимых транзакций

Использование триггеров для проверки и отмены недопустимых транзакций INSERT, UPDATE или DELETE или предотвращения изменений в схеме базы данных является общим. Это можно сделать, включив логику проверки в триггер, а затем откат текущей транзакции, если действие не соответствует критериям проверки.

При вызове триггера Transaction.Rollback метод или sqlCommand с текстом команды "TRANSACTION ROLLBACK" создает исключение с неоднозначным сообщением об ошибке и должно быть заключено в блок try/catch. Сообщение об ошибке, которое вы видите, похоже на следующее:

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.

Это ожидаемое исключение, поэтому для продолжения выполнения кода необходим блок try-catch. После завершения выполнения кода триггера возникает другое исключение.

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.

Это исключение также ожидается, и блок try/catch вокруг оператора Transact-SQL, выполняющего действие, которое запускает триггер, необходимо, чтобы выполнение можно было продолжить. Несмотря на два исключения, транзакция откатывается и изменения не фиксируются в таблице. Основное различие между триггерами СРЕДЫ CLR и триггерами Transact-SQL заключается в том, что Transact-SQL триггеры могут продолжать выполнять большую работу после отката транзакции.

Пример

Следующий триггер выполняет простую проверку инструкций INSERT в таблице. Если вставленное целое число равно одному, транзакция откативается и значение не вставляется в таблицу. Все остальные целые значения вставляются в таблицу. Обратите внимание на блок try/catch вокруг Transaction.Rollback метода. Скрипт Transact-SQL создает тестовую таблицу, сборку и управляемую хранимую процедуру. Обратите внимание, что две инструкции INSERT упаковываются в блок try/catch, чтобы исключение, возникающее при завершении выполнения триггера.

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;

См. также

CREATE TRIGGER (Transact-SQL)
Триггеры DML
Триггеры DDL
ПОПЫТКА... CATCH (Transact-SQL)
Создание объектов базы данных с интеграцией CLR
EVENTDATA (Transact-SQL)

  • Last updated on