CLR Tetikleyicileri
Nedeniyle SQL Server ile tümleştirme .NET Framework ortak dil çalışma zamanı (CLR), kullanabilirsiniz .NET Framework CLR oluşturmak için dil tetikler. Bu bölüm tetikleyicileri CLR tümleştirmeyle uygulanan özgü bilgileri kapsar.Tetikleyicileri tam bir tartışma için bkz: DML Tetikleyicileri anlama ve DDL Tetikleyicileri anlama.
Tetikleyicileri nelerdir?
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: veri işleme dili (DML) ve veri tanımlama dili (DDL) tetikler. DML Tetikleyiciler olabilir ne zaman kullanılır INSERT, UPDATE, veya DELETE ifadeleri, belirtilen bir tablo veya Görünüm'deki verileri değiştirmek. Saklı yordamlar için öncelikle ile başlayan ifadeleri DDL ifadeleri çeşitli yanıt DDL Tetikleyicileri harekete CREATE, ALTER, ve DROP. DDL Tetikleyicileri, Denetim ve veritabanı işlemleri düzenleme gibi yönetim görevleri için kullanılabilir.
CLR Tetikleyicileri, benzersiz özellikleri
' De yazılmış Tetikleyicileri Transact-SQL firing görünüm veya tablo sütunlarından ile güncelleştirilmiş olup olmadığını belirleme yeteneğine sahip UPDATE(column) ve COLUMNS_UPDATED() işlevler.
CLR bir dilde yazılan Tetikleyicileri CLR tümleştirme nesnelerden diğer birkaç önemli farklar.CLR Tetikleyicileri yapabilirsiniz:
Verilerde başvuru INSERTED ve DELETED Tablolar
Sonucunda hangi sütunların değiştirilmiş belirlemek bir UPDATE Operation
DDL) deyimleri yürütülmesini tarafından etkilenen veritabanı nesneleri ile ilgili bilgilere erişmek.
Bu yetenekleri esas sorgu dili veya tarafından sağlanan SqlTriggerContext Sınıf. CLR tümleştirme ve yönetilen kod arasında seçim yapma yararları hakkında daha fazla bilgi için ve Transact-SQL, bkz: CLR tümleştirme'e genel bakış.
SqlTriggerContext sınıf'ı kullanma
The SqlTriggerContext class cannot be publicly constructed and can only be obtained by accessing the SqlContext.TriggerContext özellik within the body of a CLR tetikleyicisi. The SqlTriggerContext class can be obtained from the active SqlContext by calling the SqlContext.TriggerContext özellik:
SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;
The SqlTriggerContext class provides context information about the trigger.Bu bağlamsal bilgiler hangi sütunların UPDATE işleminde ve XML gibi DDL tetikleyicisi örneğinde değiştirilmiş tetikleyici baþlatmak, , neden olan eylem türünü içerir. EventData Tetikleyici işlem tanımlayan yapısı. Daha fazla bilgi için bkz:EVENTDATA (Transact-SQL).
Tetikleyici olayını yürütmesini belirleme
Siz aldıktan sonra bir SqlTriggerContext, tetiğin ateşlenmesine neden olan bir eylem türünü belirlemek için kullanabilirsiniz. Bu bilgilere erişilebilir TriggerAction özellik SqlTriggerContext Sınıf.
DML tetikleyici, TriggerAction özellik aşağıdaki değerlerden biri olabilir:
TriggerAction.Update (0x1)
TriggerAction.Insert (0x2)
TriggerAction.Delete(0x3)
DDL Tetikleyicileri, olası TriggerAction değerler listesi çok uzun.Lütfen daha fazla bilgi için .NET Framework SDK'sındaki "TriggerAction enumeration" konusuna bakın.
Eklenen ve silinen tablolar'ı kullanma
Iki özel tablo DML tetikleyicisi tablolarda kullanılır: the inserted table and the deleted table.SQL Server automatically creates and manages these tables.Bu geçici tablolar bazı veri değişiklikleri etkisini sınayın ve DML tetikleyicisi eylemler için koşullar'ı ayarlamak için kullanabilirsiniz; ancak, tablolarındaki verilere doðrudan deðiþtirmeye edemiyor.
CLR Tetikleyicileri erişebilirler eklenen and silindi tablolar CLR işlem içi sağlayıcı üzerinden.Bu alma tarafından yapılan bir SqlCommand Nesne SqlContext nesnesinden. Örneğin:
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"
Güncelleştirilmiş sütun belirleme
Kullanarak bir UPDATE işlemi tarafından değiştirilmiş bir sütun sayısını belirleyebilir ColumnCount özellik SqlTriggerContext nesne. Kullanabileceğiniz IsUpdatedColumn sütunu sütun güncelleştirildiği olup olmadığını belirlemek için bir giriş parametresi sıralı yöntem. C True değer sütun güncelleştirildiğini gösterir.
Örneğin, bu kod parçacığını (gelen, bu konunun ilerleyen bölümlerindeki EmailAudit tetikleyicisi) tüm güncelleştirilmiş sütunları listeler:
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()
CLR DDL Tetikleyicileri EventData erişme
DDL Tetikleyicileri, normal bir tetikleyici gibi bir olaya yanıt olarak saklı yordamlar ateşlenmesine.Ancak, DML Tetikleyicileri farklı olarak, bunlar UPDATE, INSERT veya bir tablo veya Görünüm deyimlerini DELETE yanıtındaki ateşlenmesine değil.Bunun yerine, yanıt olarak, öncelikle CREATE, ALTER ve DROP deyimi DDL ifadeleri çeşitli ateşlenmesine.DDL Tetikleyicileri, Denetim ve veritabanı işlemleri ve şema değişiklikleri izleme gibi yönetim görevleri için kullanılabilir.
DDL bir tetiği harekete bir olay hakkında bilgi EventData özellik SqlTriggerContext Sınıf. Bu özellik içeren bir xml Değer. Xml şeması, hakkında bilgi içerir:
Olay saat.
Sistem işlem KIMLIĞI (SPID) bağlantı boyunca tetikleyici yürütüldü.
Tetikleyici harekete olay türü.
Sonra Olay türüne bağlı olarak, ek bilgi için veritabanı içinde olayın gerçekleşme, karşı olayın oluştuğu, nesne gibi şema içerir ve Transact-SQL olay komut.
Aşağıdaki örnekte, aşağıdaki DDL tetikleyicisi ham verir EventData Özellik.
Not
Gönderme ve sonuçlar arasında iletileri SqlPipe nesne yalnızca görsel amacıyla burada gösterilen ve üretim kodu için CLR Tetikleyicileri programlama, genellikle önerilmez. Ek veriler döndürdü beklenmeyen ve müşteri adayı için uygulama hataları olabilir.
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şağıdaki örnek çıktısı EventData özellik değeri bir DDL tetikleyicisi ile harekete sonra bir CREATE TABLE Olay:
<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>
Yoluyla erişilebilir bilgilere ek olarak SqlTriggerContext sınıf sorguları yine de başvuruda bulunabilir COLUMNS_UPDATED ve eklenen/silinmiş bir komut metni içinde iþlem içi yürütülürler.
Örnek CLR tetikleyicisi
Bu örnekte, kullanıcıya herhangi bir KIMLIK istedikleri sağlar, ancak özellikle e-posta adresi, bir KIMLIK girilen kullanıcılar istediğiniz senaryonun göz önünde bulundurun.Aşağıdaki tetikleyicisi ve bu bilgileri algılama, bir denetim için oturum tablo.
Not
Gönderme ve sonuçlar arasında iletileri SqlPipe nesne yalnızca görsel amacıyla burada gösterilen ve üretim kodunu genellikle önerilmez. Ek veriler döndürdü, beklenmeyen ve müşteri adayı için uygulama hataları olabilir
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
Assuming iki tablo ile aşağıdaki tanımları var:
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 deyim that creates the trigger in SQL Server is as follows, and assumes derleme 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
Not
Ile başlayan SQL Server 2005, üzerinde bir SQL Serveruyumluluk düzey "oluşturamıyor, 80" veritabanı, kullanıcı tanımlı türler, saklı yordamları, işlevleri, toplamları veya Tetikleyicileri yönetiliyor. Bu CLR bütünleştirme özelliklerinin avantajlarından yararlanmak için: SQL Server, kullanmanız gereken sp_dbcmptlevel (Transact-SQL)saklı bir yordam için "100" veritabanı uyumluluk düzeyini ayarlamak için.
Doğrulama ve geçersiz işlemleri iptal etme
Tetikleyicileri doğrulamak ve INSERT, UPDATE veya DELETE geçersiz hareketleri iptal edin veya veritabanı şemanız değişiklikleri önlemek için ortak kullanmaktır.Bu işlem doğrulama mantığını Tetikleyiciniz ekleme ve eylem doğrulama ölçütlerini karşılamıyor, geçerli işlem geri gerçekleştirilebilir.
Bir tetikleyici içinde çağrıldığında Transaction.Rollbackyöntem veya komut metni "TRANSACTıON ROLLBACK" ile bir SqlCommand belirsiz bir hata iletisiyle bir özel durum atar ve bir try/catch blok içinde alınmalıdır. Gördüğünüz hata iletisi aşağıdakine benzer:
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.
Bu özel durum beklenen ve kod yürütülmesine devam deneyin/catch blok gereklidir.Tetikleyici kod yürütme tamamlandığında, başka bir duruma neden oldu
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.
Bu durum da beklenen ve geçici bir try/catch blok Transact-SQL Tetik ateşlendiğinde eylem gerçekleştiren bir deyim yürütme devam edebilmeniz için gereklidir. Rağmen atılan iki özel durum, işlem geri alınır ve değişiklikleri için kaydedilmiş tablo.CLR Tetikleyicileri arasındaki ana fark ve Transact-SQL Tetikleyicileri olan Transact-SQL Tetikleyicileri hareket döndürülüyor sonra daha fazla iş yapmaya devam edebilirsiniz.
Örnek
Aşağıdaki tetikleyicisi INSERT deyimleri, basit bir doğrulama gerçekleştirir bir tablo.Eklenen bir tamsayı değeri için ise, işlem geri alınır ve bu değeri eklenir. tablo.Diğer tüm tamsayı değerler, tabloya eklenir.Try/catch blok içine Not Transaction.Rollback yöntem. The Transact-SQL script creates a test tablo, derleme, and managed saklı yordam. Böylece, tetikleyici Yürütme tamamlandığında atılan özel durum yakalandı iki INSERT deyimleri bir try/catch blok içinde kaydırılan unutmayın.
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;