HOW TO：建立及執行 CLR SQL Server 觸發程序
更新：2007 年 11 月
將 [觸發程序] 項目加入至 SQL Server 專案，建立 SQL 觸發程序。成功部署後，以 Managed 程式碼所建立的觸發程序，其呼叫和執行方式會類似其他 T-SQL 觸發程序的方式。以 Managed 語言撰寫的觸發程序可以使用 SqlTriggerContext 類別，存取其他 T-SQL 觸發程序所使用的相同資訊。
.gif "注意事項") 注意事項： |
|---|
根據預設，Microsoft SQL Server 中的 Common Language Runtime (CLR) 整合功能已關閉，您必須啟用此功能，才能使用 SQL Server 專案項目。若要啟用 CLR 整合，請使用 sp_configure 預存程序的 clr enabled 選項。如需詳細資訊，請參閱啟用 CLR 整合。 |
| 注意事項： |
|---|
您的電腦可能會在下列說明中，以不同名稱或位置顯示某些 Visual Studio 使用者介面項目。您所擁有的 Visual Studio 版本以及使用的設定會決定這些項目。如需詳細資訊，請參閱 Visual Studio 設定。 |
建立 SQL Server 觸發程序
若要建立 SQL Server 觸發程序
開啟現有的 [SQL Server 專案]，或建立一個新專案。如需詳細資訊，請參閱 HOW TO：建立 SQL Server 專案。
從 [專案] 功能表中選取 [加入新項目]。
在加入新項目對話方塊中，選取 [觸發程序]。
為新觸發程序輸入 [名稱]。
加入執行觸發程序時執行的程式碼。請參閱遵循此程序的第一個範例。
注意事項：C++ 範例必須使用 /clr:safe 編譯器選項進行編譯。
若為 Visual Basic 和 Visual C#，請在 [方案總管] 中，開啟 [TestScripts] 資料夾，並按兩下 [Test.sql] 檔案。
若為 Visual C++，請在 [方案總管] 中，按兩下 [debug.sql] 檔案。
將程式碼加入至 Test.sql (在 Visual C++ 中為 debug.sql) 檔案，以執行觸發程序。請參閱遵循此程序的第二個範例。
按 F5，建置、部署和偵錯觸發程序。如需在不偵錯的情況下進行部署的詳細資訊，請參閱 HOW TO：將 SQL Server 專案項目部署至 SQL Server。
檢視輸出視窗中所顯示的結果，並選取 [顯示輸出來源: 資料庫輸出]。
範例
此範例將示範的情況為使用者選擇任何想要的使用者名稱，但是您想要知道哪些使用者輸入電子郵件地址做為使用者名稱。此觸發程序將會偵測到該項資訊，並將它記錄到稽核表中。
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
Imports Microsoft.SqlServer.Server
Partial Public Class Triggers
<SqlTrigger(Name:="UserNameAudit", Target:="Users", Event:="FOR INSERT")> _
Public Shared Sub UserNameAudit()
Dim triggContext As SqlTriggerContext = SqlContext.TriggerContext()
Dim userName As New SqlParameter("@username", SqlDbType.NVarChar)
If triggContext.TriggerAction = TriggerAction.Insert Then
Using conn As New SqlConnection("context connection=true")
conn.Open()
Dim sqlComm As New SqlCommand
Dim sqlP As SqlPipe = SqlContext.Pipe()
sqlComm.Connection = conn
sqlComm.CommandText = "SELECT UserName from INSERTED"
userName.Value = sqlComm.ExecuteScalar.ToString()
If IsEMailAddress(userName.ToString) Then
sqlComm.CommandText = "INSERT UsersAudit(UserName) VALUES(username)"
sqlP.Send(sqlComm.CommandText)
sqlP.ExecuteAndSend(sqlComm)
End If
End Using
End If
End Sub
Public Shared Function IsEMailAddress(ByVal s As String) As Boolean
Return Regex.IsMatch(s, "^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$")
End Function
End Class
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
public partial class Triggers
{
[SqlTrigger(Name="UserNameAudit", Target="Users", Event="FOR INSERT")]
public static void UserNameAudit()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlParameter userName = new SqlParameter("@username", System.Data.SqlDbType.NVarChar);
if (triggContext.TriggerAction == TriggerAction.Insert)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand sqlComm = new SqlCommand();
SqlPipe sqlP = SqlContext.Pipe;
sqlComm.Connection = conn;
sqlComm.CommandText = "SELECT UserName from INSERTED";
userName.Value = sqlComm.ExecuteScalar().ToString();
if (IsEMailAddress(userName.ToString()))
{
sqlComm.CommandText = "INSERT UsersAudit(UserName) VALUES(userName)";
sqlP.Send(sqlComm.CommandText);
sqlP.ExecuteAndSend(sqlComm);
}
}
}
}
public static bool IsEMailAddress(string s)
{
return Regex.IsMatch(s, "^([\\w-]+\\.)*?[\\w-]+@[\\w-]+\\.([\\w-]+\\.)*?[\\w]+$");
}
}
#include "stdafx.h"
#using <System.dll>
#using <System.Data.dll>
#using <System.Xml.dll>
using namespace System;
using namespace System::Data;
using namespace System::Data::Sql;
using namespace System::Data::SqlClient;
using namespace System::Data::SqlTypes;
using namespace System::Text::RegularExpressions;
using namespace Microsoft::SqlServer::Server;
// In order to debug your Trigger, add the following to your debug.sql file:
//
// -- Insert one user name that is not an e-mail address and one that is
// INSERT INTO Users(UserName, Pass) VALUES(N'someone', N'cnffjbeq')
// INSERT INTO Users(UserName, Pass) VALUES(N'someone@example.com', N'cnffjbeq')
//
// -- check the Users and UsersAudit tables to see the results of the trigger
// SELECT * FROM Users
// SELECT * FROM UsersAudit
//
public ref class AddNewTrigger
{
public:
[SqlTrigger(Name="UserNameAudit", Target="Users", Event="FOR INSERT")]
static void UserNameAudit()
{
SqlTriggerContext ^triggContext = SqlContext::TriggerContext;
SqlParameter ^userName = gcnew SqlParameter("@username", System::Data::SqlDbType::NVarChar);
if (triggContext->TriggerAction == TriggerAction::Insert)
{
SqlConnection ^conn = gcnew SqlConnection("context connection=true");
conn->Open();
SqlCommand ^sqlComm = gcnew SqlCommand();
SqlPipe ^sqlP = SqlContext::Pipe;
sqlComm->Connection = conn;
sqlComm->CommandText = "SELECT UserName from INSERTED";
userName->Value = sqlComm->ExecuteScalar()->ToString();
if (IsEMailAddress(userName->ToString()))
{
sqlComm->CommandText = "INSERT UsersAudit(UserName) VALUES(userName)";
sqlP->Send(sqlComm->CommandText);
sqlP->ExecuteAndSend(sqlComm);
}
conn->Close();
}
}
static bool IsEMailAddress(String ^s)
{
return Regex::IsMatch(s, "^([\\w-]+\\.)*?[\\w-]+@[\\w-]+\\.([\\w-]+\\.)*?[\\w]+$");
}
};
將程式碼加入至專案中 TestScripts 資料夾的 Test.sql (在 Visual C++ 中為 debug.sql) 檔案，以執行和測試觸發程序。例如，如果您已部署觸發程序，可以執行指令碼，在設定觸發程序的資料表中，插入引發觸發程序的新資料列，進行測試。下列偵錯程式碼會假設現在有兩個資料表，而且資料表具有下列定義：
CREATE TABLE Users
(
UserName NVARCHAR(200) NOT NULL,
Pass NVARCHAR(200) NOT NULL
)
CREATE TABLE UsersAudit
(
UserName NVARCHAR(200) NOT NULL
)
-- Insert one user name that is not an e-mail address and one that is
INSERT INTO Users(UserName, Pass) VALUES(N'someone', N'cnffjbeq')
INSERT INTO Users(UserName, Pass) VALUES(N'someone@example.com', N'cnffjbeq')
-- check the Users and UsersAudit tables to see the results of the trigger
select * from Users
select * from UsersAudit
請參閱
工作
HOW TO：建立及執行 CLR SQL Server 預存程序
HOW TO：建立及執行 CLR SQL Server 觸發程序
HOW TO：建立及執行 CLR SQL Server 彙總
HOW TO：建立及執行 CLR SQL Server 使用者定義函式
HOW TO：建立及執行 CLR SQL Server 使用者定義型別