Триггеры CLR
Благодаря тому что SQL Server интегрирован со средой .NET Framework CLR, для создания триггеров CLR можно использовать любой язык .NET Framework. В настоящем разделе рассматриваются сведения, касающиеся реализации триггеров в условиях интеграции со средой CLR. Полное описание триггеров см. в разделе триггеры DDL.
Что такое триггеры?
Триггером называют хранимую процедуру особого типа, которая автоматически выполняется при возникновении языкового события. SQL Serverвключает в себя два основных типа триггеров: языка обработки данных (DML) и триггеры языка DDL для определения данных. Триггеры DML могут быть использоваться тогда, когда вставки, обновление, или удалить инструкции изменения данных в указанной таблице или представлении. Триггеры DDL вызывают срабатывание хранимых процедур в ответ на разнообразные инструкции DDL, которые являются в основном инструкции, начинающиеся с создать, ALTER, и DROP. Триггеры DDL могут быть использованы в административных задачах, таких как аудит и регулирование операций базы данных.
Уникальные возможности триггеров CLR
Триггеры, написанные Transact-SQL имеется возможность определить, какие столбцы в обработке представлении или таблице были обновлены с помощью UPDATE(column) и COLUMNS_UPDATED() функции.
Триггеры, код которых написан на одном из языков среды CLR, отличаются от других объектов интеграции со средой CLR в нескольких существенных чертах. Триггеры CLR позволяют:
Ссылаться на данные в INSERTED и DELETED таблиц
Определить, какие столбцы были изменены в результате использования обновление операции
получать доступ к сведениям об объектах базы данных, затронутых в результате выполнения инструкций языка DDL.
Эти возможности предоставляются непосредственно в языке запросов или с SqlTriggerContext класса. Дополнительные сведения о преимуществах интеграции со средой CLR и выборе между управляемым кодом и Transact-SQL, в разделе Обзор интеграции со средой CLR.
Использование класса SqlTriggerContext
SqlTriggerContext класс не может быть создан как общедоступный и может быть получен только путем доступа к SqlContext.TriggerContext свойство в теле триггера CLR. SqlTriggerContext класса можно получить из активного SqlContext путем вызова SqlContext.TriggerContext свойство:
SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;
Класс SqlTriggerContext предоставляет сведения контекста о триггере. Эти сведения включают тип действия, вызвавшего срабатывание триггера, какие столбцы были изменены в операции обновления, а в случае триггера DDL XML EventData структуру, которая описывает операцию триггера. Дополнительные сведения см. в разделе EVENTDATA (Transact-SQL).
Определение триггерного действия
После получения SqlTriggerContext, его можно использовать для определения типа действия, вызвавшего срабатывание триггера. Эта информация доступна через TriggerAction свойство SqlTriggerContext класса.
Для триггеров DML TriggerAction свойство может принимать одно из следующих значений:
TriggerAction.Update (0x1)
TriggerAction.Insert (0x2)
TriggerAction.Delete (0x3)
Что касается триггеров DDL, то список возможных значений TriggerAction намного больше. Дополнительные сведения см. в разделе «Перечисления TriggerAction» пакета SDK для платформы .NET Framework.
Использование таблиц inserted и deleted
В инструкциях триггеров 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"
Определение обновленных столбцов
Можно определить количество столбцов, которые были изменены в процессе обновления с помощью число столбцов свойство 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 для триггеров CLR DDL
Триггеры 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;
См. также:
Создание ТРИГГЕРА (Transact-SQL)
Триггеры DML
Триггеры DDL
КОНСТРУКЦИЯ TRY... CATCH (Transact-SQL)
Создание объектов базы данных удобную среды выполнения (CLR)
Функция EVENTDATA (Transact-SQL)