由於 SQL Server 與 .NET Framework Common Language Runtime （CLR） 整合，因此您可以使用任何 .NET Framework 語言來建立 CLR 觸發程式。 本節涵蓋使用 CLR 整合實作之觸發程式的特定資訊。 如需觸發程式的完整討論，請參閱 DDL 觸發程式。
什麼是觸發程式？
觸發程式是一種特殊的預存程式類型，可在語言事件執行時自動執行。 SQL Server 包含兩種一般類型的觸發程式：數據作語言 （DML） 和數據定義語言 （DDL） 觸發程式。 當、 UPDATE或 DELETE 語句修改指定數據表或檢視中的資料時INSERT，可以使用 DML 觸發程式。 DDL 觸發程式會引發預存程式，以響應各種 DDL 語句，主要是以 CREATE、 ALTER和 DROP開頭的語句。 DDL 觸發程式可用於系統管理工作，例如稽核和規範資料庫作業。
CLR 觸發程式的獨特功能
以 Transact-SQL 撰寫的觸發程式能夠使用 UPDATE(column) 和 COLUMNS_UPDATED() 函式來判斷引發檢視或數據表中的數據行已更新。
以 CLR 語言撰寫的觸發程式與其他 CLR 整合物件不同，有數個重要方式。 CLR 觸發程式可以：
參考 和
DELETED數據表中的數據INSERTED判斷哪些數據行已因為
UPDATE作業而修改存取受 DDL 語句執行影響之資料庫對象的資訊。
這些功能原本就以查詢語言或 類別提供 SqlTriggerContext 。 如需 CLR 整合的優點，以及在 Managed 程式代碼與 Transact-SQL 之間選擇的相關信息，請參閱 CLR 整合概觀。
使用 SqlTriggerContext 類別
類別 SqlTriggerContext 無法公開建構，而且只能藉由存取 SqlContext.TriggerContext CLR觸發程式主體內的屬性來取得。 您可以SqlTriggerContext藉由呼叫 SqlContext.TriggerContext 屬性，從使用SqlContext中取得 類別：
SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;
類別 SqlTriggerContext 提供觸發程式的相關內容資訊。 此內容資訊包含導致觸發程式引發的動作類型、UPDATE 作業中修改的數據行，以及在描述觸發作業的 DDL 觸發程式的 XML EventData 結構案例中。 如需詳細資訊，請參閱 EVENTDATA （Transact-SQL） 。
判斷觸發程序動作
SqlTriggerContext取得 之後，您可以使用它來判斷觸發程式引發的動作類型。 這項資訊可透過 TriggerAction 類別的 SqlTriggerContext 屬性取得。
針對 DML 觸發程式， TriggerAction 屬性可以是下列其中一個值：
TriggerAction.Update （0x1）
TriggerAction.Insert （0x2）
TriggerAction.Delete（0x3）
對於 DDL 觸發程式，可能的 TriggerAction 值清單會相當長。 如需詳細資訊，請參閱 .NET Framework SDK 中的“TriggerAction 列舉”。
使用插入和刪除的數據表
DML 觸發程式語句中會使用兩個特殊數據表： 插入 的數據表和 已刪除的 數據表。 SQL Server 會自動建立並管理這些資料表。 您可以使用這些臨時表來測試特定數據修改的效果，以及設定 DML 觸發程式動作的條件;不過，您無法直接改變數據表中的數據。
CLR 觸發程式可以透過CLR進程內提供者存取 插入 和 刪除的 資料表。 這是藉由從 SqlContext 物件取得 SqlCommand 物件來完成。 例如：
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()
存取 CLR DDL 觸發程式的 EventData
DDL 觸發程式，例如一般觸發程式，會引發預存程式以回應事件。 但與 DML 觸發程式不同，它們不會在數據表或檢視表上回應 UPDATE、INSERT 或 DELETE 語句時引發。 相反地，它們會引發以響應各種 DDL 語句，這些語句主要是以 CREATE、ALTER 和 DROP 開頭的語句。 DDL 觸發程式可用於系統管理工作，例如稽核和監視資料庫作業和架構變更。
引發 DDL 觸發程式之事件的相關信息可在 類別的 SqlTriggerContext 屬性中使用EventData。 這個屬性包含值 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
)
在 SQL Server 中建立觸發程式的 Transact-SQL 語句如下所示，並假設目前 SQL Server 資料庫中已註冊元件 SQLCLRTest 。
CREATE TRIGGER EmailAudit
ON Users
FOR INSERT, UPDATE, DELETE
AS
EXTERNAL NAME SQLCLRTest.CLRTriggers.EmailAudit
驗證和取消無效的交易
使用觸發程式來驗證和取消無效的 INSERT、UPDATE 或 DELETE 交易，或防止變更資料庫架構很常見。 這可以藉由將驗證邏輯併入觸發程式，然後在動作不符合驗證準則時回復目前的交易來完成。
在觸發程式內呼叫時， Transaction.Rollback 方法或具有命令文字 「TRANSACTION ROLLBACK」 的 SqlCommand 會擲回具有模棱兩可錯誤訊息的例外狀況，而且必須包裝在 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.
此例外狀況也是預期的，而且執行觸發程式所需動作的 Transact-SQL 語句周圍有 try/catch 區塊，以便繼續執行。 儘管擲回了兩個例外狀況，但交易會回復，而且變更不會認可至數據表。 CLR 觸發程式和 Transact-SQL 觸發程式之間的主要差異在於，Transact-SQL 觸發程式可以在交易回復後繼續執行更多工作。
範例
下列觸發程式會在數據表上執行 INSERT 語句的簡單驗證。 如果插入的整數值等於一，則會回復交易，而且該值不會插入數據表中。 所有其他整數值都會插入數據表中。 請注意 方法周圍的 Transaction.Rollback try/catch 區塊。 Transact-SQL 文稿會建立測試數據表、元件和 Managed 預存程式。 請注意，兩個 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 觸發器
嘗試。。。CATCH （Transact-SQL）
使用 Common Language Runtime （CLR） 整合建置資料庫物件
EVENTDATA （Transact-SQL）