Środowisko CLR wyzwalaczy
Z powodu SQL Server Integracja z .NET Framework aparat plików wykonywalnych języka wspólnego (CLR), można użyć dowolnego .NET Framework Uruchamia język do tworzenia środowiska CLR. W tej sekcji omówiono informacje specyficzne dla wyzwalaczy z integracja środowiska CLR.Pełne Omówienie wyzwalaczy zobacz Understanding DML Triggers i Understanding DDL Triggers.
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: dane języka manipulacji (DML) i Wyzwalacze (DDL) języka definicja danych. Wyzwalacze DML mogą być używane, kiedy INSERT, UPDATE, lub DELETE instrukcje modyfikowania danych w określonej tabela lub widoku. Wyzwalacze DDL uruchomienie procedur przechowywanych w odpowiedzi na różne instrukcje DDL, które są przede wszystkim instrukcji, które zaczynają się od CREATE, ALTER, a DROP. Wyzwalacze DDL może służyć do zadań administracyjnych, takich jak inspekcji i regulowanie operacji w bazie danych.
Unikatowy możliwości środowiska CLR wyzwalaczy
Wyzwalacze w Transact-SQL ma możliwość określenia, które kolumny z tabela lub widoku wypalania zostały zaktualizowane przy użyciu UPDATE(column) i COLUMNS_UPDATED() funkcje.
Wyzwalacze w języku CLR różnią się od innych obiektów środowiska CLR integracja na kilka sposobów znaczący.Środowisko CLR Wyzwalacze mogą wykonywać następujące czynności:
Odwołanie do danych w INSERTED i DELETED tabele
Określenie kolumn, które zostały zmodyfikowane w wyniku UPDATE Operacja
Uzyskiwanie dostępu do informacji dotyczących obiektów bazy danych, które wpływają na wykonanie instrukcji DDL.
Funkcje te są dostarczane założenia w języku kwerend lub przez SqlTriggerContext Klasa. Aby uzyskać informacje na temat zalet integracja środowiska CLR i wybór między kod zarządzany i Transact-SQL, zobacz Omówienie integracja CLR.
Za pomocą klasy SqlTriggerContext
The SqlTriggerContext class cannot be publicly constructed and can only be obtained by accessing the SqlContext.TriggerContext właściwość within the body of a wyzwalacz CLR. The SqlTriggerContext class can be obtained from the active SqlContext by calling the SqlContext.TriggerContext właściwość:
SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;
The SqlTriggerContext class provides context information about the trigger.Te informacje kontekstowe zawiera typ akcja, która spowodowała wyzwalacza ognia, kolumny, które zostały zmodyfikowane podczas operacji UPDATE oraz, przypadek wyzwalacz DLL XML EventData strukturę, która opisuje wyzwalającego operacji. Aby uzyskać więcej informacji zobaczEVENTDATA (Transact-SQL).
Określanie akcja wyzwalacza
Po uzyskaniu SqlTriggerContext, który służy do ustalania typu akcja, która spowodowała uruchomienie wyzwalacza. Informacje te są dostępne za pośrednictwem TriggerAction Właściwość SqlTriggerContext Klasa.
Wyzwalaczy DML 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."TriggerAction wyliczania" w zestaw .NET Framework SDK, aby uzyskać więcej informacji można znaleźć.
Za pomocą tabel wstawione i usunięte
W instrukcji wyzwalacz DML używane są dwie tabele specjalne: the inserted table and the deleted table.SQL Server automatically creates and manages these tables.Te tymczasowe tabel można używać do testowania skutków pewnych modyfikacji danych i ustawić warunki akcje wyzwalacz DML, jednak można zmieniać dane w tabelach bezpośrednio.
Mogą uzyskać dostęp do środowiska CLR wyzwalacze dodaje and usunięte tabel za pośrednictwem dostawca w procesie środowiska CLR.Można to zrobić uzyskując 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 zaktualizowanych kolumny
Można określić liczbę kolumn, które zostały zmodyfikowane przez operację UPDATE za pomocą ColumnCount Właściwość SqlTriggerContext obiekt. Można użyć IsUpdatedColumn Metoda, która ma w kolumnie porządkowych jako parametr wejściowy, aby określić, czy kolumna została zaktualizowana. A True wartość wskazuje, że został zaktualizowany kolumna.
Na przykład tej wstawki kodu programu (od wyzwalacza EmailAudit w dalszej części tego tematu) zawiera wszystkie zaktualizowane kolumn:
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()
Uzyskiwanie dostępu do EventData CLR DDL wyzwalaczy
Wyzwalacze DDL, takie jak regularne wyzwalacze, uruchomienie procedur przechowywanych w odpowiedzi na zdarzenie.Jednak w odróżnieniu od DML wyzwalacze, nie one ognia w odpowiedzi na UPDATE, INSERT lub DELETE instrukcji na tabela lub widoku.Można natomiast uruchomienie ich w odpowiedzi na różne instrukcje DDL, które są przede wszystkim instrukcji, które zaczynają się od CREATE, ALTER i DROP.Wyzwalacze DDL może służyć do zadań administracyjnych, takich jak inspekcji i monitorowania operacji bazy danych i zmian schematu.
Informacje na temat zdarzenie uruchomieniu wyzwalacz DLL są dostępne w EventData Właściwość SqlTriggerContext Klasa. Właściwość ta zawiera xml wartość. Schemat xml zawiera informacje dotyczące:
Godzina wystąpienia zdarzenie.
Identyfikator systemu Process ID (SPID) połączenia, podczas którego wyzwalacz jest wykonywany.
Typ uruchomienia wyzwalacza zdarzenie.
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, na którym wystąpiło zdarzenie, a także Transact-SQL polecenie zdarzenia.
W poniższym przykładzie zwraca raw następujące wyzwalacz DLL EventData Właściwość.
Uwaga
Wysyłanie wyniki i komunikatów za pośrednictwem SqlPipe obiekt tym polu jest wyświetlana tylko w celach opisowy i ogólnie jest zalecane dla kodu produkcji programowania wyzwalacze środowiska CLR. Dodatkowe dane zwracane mogą być nieoczekiwane i prowadzić do błędów.
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 to EventData wartość właściwość po uruchamiany przez wyzwalacz DLL 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>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>
Oprócz informacji za pośrednictwem SqlTriggerContext Klasa kwerendy może nadal dotyczyć COLUMNS_UPDATED oraz dodaje/usunięty w tekście polecenie wykonywane w procesie.
Przykładowe CLR wyzwalacza
W tym przykładzie Rozważmy scenariusz, w którym powiadomić użytkownika, wybierz nazwę, wszystkie one mają, ale należy określić użytkowników, w szczególności wprowadzony adres e-mail jako identyfikator.Następujące wyzwalacz może wykryć te informacje i zalogować go do tabela inspekcji.
Uwaga
Wysyłanie wyniki i komunikatów za pośrednictwem SqlPipe obiekt tym polu jest wyświetlana tylko w celach opisowy i ogólnie jest zalecane dla kodu produkcji. Dodatkowe dane zwracane mogą być nieoczekiwane i prowadzić do błędów
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 definicjami następujące:
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
)
The Transact-SQL instrukcja that creates the trigger in SQL Server is as follows, and assumes wirtualny plik dziennika SQLCLRTest is already registered in the current SQL Server database.
CREATE TRIGGER EmailAudit
ON Users
FOR INSERT, UPDATE, DELETE
AS
EXTERNAL NAME SQLCLRTest.CLRTriggers.EmailAudit
Uwaga
Zaczyna się od SQL Server 2005, na SQL ServerBaza danych o poziom zgodności „ 80"nie można tworzyć zarządzane typów zdefiniowanych przez użytkownika, procedura przechowywana, funkcje, zagregowanych lub wyzwalaczy. Aby korzystać z tych CLR funkcje integracja SQL Server, należy użyć sp_dbcmptlevel (języka Transact-SQL)procedura przechowywana, aby ustawić poziom zgodności bazy danych "100".
Sprawdzanie poprawności i anulowanie nieprawidłowe transakcje
Często się za pomocą wyzwalaczy, sprawdzania poprawności i anulować nieprawidłowe transakcje INSERT, UPDATE lub DELETE, lub aby zapobiec zmianom w sieci schemat bazy danych.Można to osiągnąć przez włączanie reguł 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 wyzwalacz, w ramach Transaction.RollbackMetoda lub SqlCommand tekstem polecenia "cofnięcie transakcji" zgłasza wyjątek komunikat o błędzie niejednoznaczne i musi być otoczona blok try/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.
Ten wyjątek jest oczekiwany i blok try/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 również oczekuje się, a blok try/catch wokół Transact-SQL tak, aby kontynuować wykonywanie instrukcja, która wykonuje akcja, która uruchomieniu wyzwalacza jest konieczne. Pomimo dwa wyjątki generowane, transakcja jest cofana i wprowadzone zmiany nie są zobowiązane do tabela.Główna różnica pomiędzy CLR wyzwalaczy i Transact-SQL Wyzwalacze polega na tym, że Transact-SQL Wyzwalacze mogą w dalszym ciągu wykonywać więcej pracy po przywróceniu transakcji.
Przykład
Następujące wyzwalacza wykonuje proste poprawności instrukcji INSERT tabela.Jeśli wartość całkowitą wstawiony jest równe jeden, transakcja jest cofana i wartość nie jest wstawiane do tabela.Wszystkie inne wartości całkowitych są wstawiane do tabela.Należy zwrócić uwagę, blok try/catch wokół Transaction.Rollback Metoda. The Transact-SQL script creates a test tabela, wirtualny plik dziennika, and managed procedura przechowywana. Należy zauważyć, że dwa instrukcji INSERT są pakowane w blok try/catch tak, aby wpadł wyjątek, 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;