Wyzwalacze CLR
Z powodu SQL Server integracja z .NET Framework plików wykonywalnych języka wspólnego (CLR), można użyć dowolnego .NET Framework język do tworzenia wyzwalaczy CLR.W tej sekcji omówiono informacje specyficzne dla wyzwalaczy z integracja CLR.Pełne omówienie wyzwalaczy, zobacz Opis DML wyzwalaczy i Opis Wyzwalacze DDL.
Co to są wyzwalacze?
A trigger is a special type of stored procedure that automatically runs when a language event executes.SQL Server includes two general types of triggers: język edycji danych(DML) i Wyzwalacze (DDL) języka definicja danych.DMLWyzwalacze mogą być stosowane, gdy INSERT, UPDATE, lub DELETE instrukcji modyfikowanie danych w określonej tabela lub widoku.Wyzwalacze DDL ognia procedur przechowywanych w odpowiedzi na szereg instrukcji DDL, które są głównie instrukcji, które zaczynają się od CREATE, ALTER, i DROP.Wyzwalacze DDL służą zadania administracyjne, takie jak inspekcji oraz regulowanie operacji bazy danych.
Wyjątkowe możliwości CLR wyzwalaczy
Wyzwalacze napisane w Transact-SQL mieli możliwość określania, które kolumny z tabela lub widoku wypalania zostały zaktualizowane przy użyciu UPDATE(column) i COLUMNS_UPDATED() funkcji.
Wyzwalacze w języku CLR różnią się od innych obiektów integracja CLR na kilka sposobów znaczące.Wyzwalacze CLR może:
Odwołania danych w INSERTED i DELETED tabel
Określić kolumny, które zostały zmodyfikowane w wyniku UPDATE operacji
Informacje dostępu dotyczy wykonanie instrukcji DDL obiektów bazy danych.
Funkcje te są dostarczane założenia w języku kwerend lub przez SqlTriggerContext klasyInformacje dotyczące zalet integracja CLR i wybór między kod zarządzany i Transact-SQL, zobacz Omówienie integracji CLR.
Za pomocą klasy SqlTriggerContext
SqlTriggerContext Klasy nie może być publicznie skonstruowane i mogą być uzyskane jedynie poprzez dostęp do SqlContext.TriggerContext właściwość w treści wyzwalacz CLR.SqlTriggerContext Klasy można uzyskać z aktywnego SqlContext przez wywołanie SqlContext.TriggerContext właściwość:
SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;
SqlTriggerContext Klasy zawiera informacje kontekstowe o wyzwalacza.Informacje kontekstowe zawiera typ akcja, który spowodował wyzwalacza ognia, kolumny, które zostały zmodyfikowane w operacji aktualizacji, a przypadek wyzwalacz DLL XML EventData strukturę, która opisuje wyzwalającego operacji.Aby uzyskać więcej informacji, zobacz EVENTDATA (Transact-SQL).
Określanie akcja wyzwalacza
Po uzyskaniu SqlTriggerContext, użyć, aby określić typ akcja, która spowodowała wyzwalacza pożaru.Ta informacja jest dostępna za pośrednictwem TriggerAction Właściwość SqlTriggerContext klasy
Dla DML wyzwalaczy, TriggerAction właściwość może mieć jedną z następujących wartości:
TriggerAction.Update (0x1)
TriggerAction.Insert (0x2)
TriggerAction.Delete(0x3)
Wyzwalacze DDL listy możliwych wartości TriggerAction jest znacznie dłużej.Zobacz "Wyliczania TriggerAction" w.NET Framework SDK więcej informacji.
Za pomocą tabel wstawionych i usuniętych
Dwie tabele specjalne są używane w wyzwalacz DML instrukcji: the inserted table and the deleted table.SQL Server automatically creates and manages these tables.Za pomocą tych tabel tymczasowych na badanie skutków pewnych modyfikacji danych i zestaw warunki LŚD wyzwalać akcje; nie można jednak bezpośrednio zmieniać dane w tabelach.
Wyzwalacze CLR dostęp dodaje i usunięte tabel za pośrednictwem dostawca w trakcie CLR.Polega to na uzyskanie SqlCommand obiekt z obiektu SqlContext.Na przykład:
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"
Określanie kolumn zaktualizowane
Można określić liczbę kolumn, które zostały zmodyfikowane przez operacji aktualizacji za pomocą ColumnCount Właściwość SqlTriggerContext obiektu.Można użyć IsUpdatedColumn metoda, która zajmuje kolumny porządkowych jako parametr wejściowy, aby określić, czy kolumna została zaktualizowana.A True wskazuje wartość kolumna została zaktualizowana.
Na przykład następujący fragment kodu (z wyzwalacza EmailAudit w dalszej części tego tematu) wyświetla wszystkie kolumny zaktualizowane:
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()
Dostęp do EventData wyzwalaczy CLR DDL
Wyzwalacze DDL, podobnie jak regularne wyzwalaczy ognia procedur przechowywanych w odpowiedzi na zdarzenie.Ale przeciwieństwie do DML wyzwalaczy, one nie uruchamiaj w odpowiedzi na sprawozdaniu aktualizacji, WSTAWIANIA lub usuwania w tabela lub widoku.Natomiast ich ognia w odpowiedzi na szereg instrukcji DDL, które są głównie instrukcji, które zaczynają się od CREATE, ALTER i UPUSZCZANIA.Wyzwalacze DDL służą zadania administracyjne, takie jak Inspekcja i monitorowanie operacji bazy danych i zmian schematu.
Informacje o zdarzenie że uruchamiany wyzwalacz DLL jest dostępna w EventData Właściwość SqlTriggerContext klasyTa właściwość zawiera xml wartości.Schemat xml zawiera informacje dotyczące:
czas zdarzenie.
System proces ID (SPID) połączenia, podczas którego wykonywane wyzwalacza.
Typ zdarzenie, które są opalane wyzwalacza.
Następnie, w zależności od typu zdarzenia schemat zawiera dodatkowe informacje, takie jak bazy danych w którym wystąpiło zdarzenie obiektu, przeciwko którym wystąpiło zdarzenie, a Transact-SQL polecenia zdarzenia.
W poniższym przykładzie następuje wyzwalacz DLL zwraca surowych EventData właściwość.
Ostrzeżenie
Wysyłanie wyniki i komunikatów za pośrednictwem SqlPipe obiektu jest tu tylko w celach ilustracyjnych i ogólnie odradza kodu produkcji programowania wyzwalaczy CLR.Dodatkowe dane zwracane mogą być błędy nieoczekiwane i doprowadziłoby do aplikacji.
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
Następujące przykładowe dane wyjściowe jest EventData wartości właściwość po wyzwalacz DLL opalanych CREATE TABLE zdarzenie:
<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>AdventureWorks2008R2</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>
Oprócz informacji za pośrednictwem SqlTriggerContext klasy kwerendy można wciąż odwoływać się do COLUMNS_UPDATED i dodane albo usunięte w tekście polecenie, wykonane w-proces.
Wyzwalacz CLR próbki
W tym przykładzie Rozważmy scenariusz, w którym pozwalają użytkownikowi wybrać dowolny identyfikator chcą, ale chcesz wiedzieć użytkowników, które w szczególności wprowadzić adres e-mail jako identyfikatora.Czy wyzwalacz następujące wykrywać informacji i zaloguj się do tabela inspekcji.
Ostrzeżenie
Wysyłanie wyniki i komunikatów za pośrednictwem SqlPipe obiektu jest tu tylko w celach ilustracyjnych i generalnie jest zalecane dla produkcji kodu.Dodatkowe dane zwracane mogą być błędy nieoczekiwane i doprowadziłoby do aplikacji
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
Istnieją dwie tabele zakładając z następującymi definicjami:
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 instrukcja, która tworzy wyzwalacza w SQL Server jest następujący i zakłada wirtualny plik dziennika SQLCLRTest jest już zarejestrowany w bieżącym SQL Server bazy danych.
CREATE TRIGGER EmailAudit
ON Users
FOR INSERT, UPDATE, DELETE
AS
EXTERNAL NAME SQLCLRTest.CLRTriggers.EmailAudit
Ostrzeżenie
Począwszy od SQL Server 2005, na SQL Server bazy danych z poziom zgodności "80" nie można utworzyć typy zarządzane przez użytkownika, procedura składowana, funkcje, agregatów ani wyzwalaczy. Aby skorzystać z tych funkcji integracja CLR z SQL Server, należy użyć sp_dbcmptlevel (języka Transact-SQL) przechowywane procedury zestaw poziom zgodności bazy danych "100".
Sprawdzanie poprawności i anulowanie transakcji nieprawidłowy
Sprawdź poprawność i anulować nieprawidłowe transakcje INSERT, UPDATE lub DELETE lub uniemożliwić zmiany schemat bazy danych przy użyciu wyzwalaczy jest wspólne.Można to osiągnąć przez dołączanie logiki sprawdzania poprawności do wyzwalacz, a następnie wycofywanie bieżącej transakcji, jeśli akcja nie spełnia kryteriów sprawdzania poprawności.
Po wywołaniu w wyzwalacz, Transaction.Rollback metoda lub SqlCommand tekst polecenia "transakcji ROLLBACK" zgłasza wyjątek komunikat o błędzie niejednoznaczne i muszą być pakowane w blok try i catch.Zostanie wyświetlony komunikat o błędzie jest podobny do następującego:
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.
Wyjątek ten jest oczekiwany i blok try i catch jest konieczne wykonywanie kodu kontynuować.Po zakończeniu wykonywania kod wyzwalacza innego jest wyjątek
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.
Wyjątek ten jest również spodziewać i blok try i catch wokół Transact-SQL instrukcja, która wykonuje akcja, która fires wyzwalacz jest tak, aby kontynuować wykonywanie.Pomimo dwa wyjątki generowane, transakcja jest cofana i zmiany nie są zobowiązane do tabela.Główna różnica pomiędzy CLR wyzwalaczy i Transact-SQL wyzwalaczy jest Transact-SQL wyzwalaczy nadal można wykonać więcej pracy po transakcji jest wycofywany Wstecz.
Przykład
Następujące wyzwalacza wykonuje proste poprawności instrukcji INSERT tabela.Jeśli wartość całkowitą wstawiony jest równa jednej, transakcja jest cofana i wartość nie jest wstawiany do tabela.Inne wartości całkowitych są wstawiane do tabela.Uwaga blok try i catch wokół Transaction.Rollback metoda. Transact-SQL Skrypt tworzy tabela test wirtualny plik dziennikai zarządzane procedura składowana.Uwaga dwóch instrukcji INSERT są pakowane w blok try i połowów, tak aby Przechwycono wyjątek podczas wyzwalacza kończy wykonywanie.
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;
Zobacz także