HOW TO:建立及執行 CLR SQL Server 觸發程序
更新:2007 年 11 月
將 [觸發程序] 項目加入至 SQL Server 專案,建立 SQL 觸發程序。成功部署後,以 Managed 程式碼所建立的觸發程序,其呼叫和執行方式會類似其他 T-SQL 觸發程序的方式。以 Managed 語言撰寫的觸發程序可以使用 SqlTriggerContext 類別,存取其他 T-SQL 觸發程序所使用的相同資訊。
![]() |
---|
根據預設,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 使用者定義型別