HOW TO：使用 Common Language Run-time 整合建立和執行 SQL Server 觸發程序

發行項
08/12/2011

將 [觸發程序] 項目加入至 SQL Server Common Language Runtime (SQL CLR) 資料庫專案，建立 SQL 觸發程序。成功部署後，以 Managed 程式碼所建立的觸發程序，其呼叫和執行方式會類似其他 Transact-SQL 觸發程序的方式。以 Managed 語言撰寫的觸發程序可以使用 SqlTriggerContext 類別，存取其他 Transact-SQL 觸發程序所使用的相同資訊。

注意事項
您的電腦可能會在下列說明中，以不同名稱或位置顯示某些 Visual Studio 使用者介面項目。您所擁有的 Visual Studio 版本以及使用的設定會決定這些項目。如需詳細資訊，請參閱 Visual Studio 設定。

建立 SQL Server 觸發程序

若要建立 SQL Server 觸發程序

開啟現有的 [SQL CLR 資料庫專案]，或建立一個新專案。如需詳細資訊，請參閱HOW TO：針對使用 SQL Server Common Language Run-time 整合的資料庫物件建立專案。
在 [專案] 功能表中選取 [加入新項目]。
在 [加入新項目] 對話方塊中，選取 [觸發程序]。
為新觸發程序輸入 [名稱]。
加入執行觸發程序時執行的程式碼。請參閱遵循此程序的第一個範例。

開啟 [方案總管] 中的 [TestScripts] 資料夾，並按兩下 Test.sql 檔案。

注意事項
您可以指定其他指令碼做為預設偵錯指令碼。如需詳細資訊，請參閱HOW TO：編輯 Test.sql 指令碼以執行使用 SQL Server Common Language Run-time 整合的物件。

將程式碼加入至 Test.sql 檔案，以執行觸發程序。請參閱遵循此程序的第二個範例。

按 F5，建置、部署和偵錯觸發程序。如需如何在不偵錯的情況下進行部署的詳細資訊，請參閱 HOW TO：將 SQL CLR 資料庫專案項目部署至 SQL Server。

重要事項
SQL Server 2005 和 SQL Server 2008 僅支援使用 .NET Framework 2.0、3.0 或 3.5 版所建置的 SQL Server 專案。如果您嘗試部署SQL Server專案，SQL Server 2005或SQL Server 2008，將顯示錯誤消息：Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database（在進行校驗是您要部署的程式集的名稱）。如需詳細資訊，請參閱HOW TO：針對使用 SQL Server Common Language Run-time 整合的資料庫物件建立專案。

SQL Server 2005 和 SQL Server 2008 僅支援使用 .NET Framework 2.0、3.0 或 3.5 版所建置的 SQL Server 專案。如果您嘗試部署SQL Server專案，SQL Server 2005或SQL Server 2008，將顯示錯誤消息：Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database（在進行校驗是您要部署的程式集的名稱）。如需詳細資訊，請參閱HOW TO：針對使用 SQL Server Common Language Run-time 整合的資料庫物件建立專案。

查看所示的結果輸出視窗和選擇顯示輸出：資料庫輸出。

範例

此範例將示範的情況為使用者選擇任何想要的使用者名稱，但是您想要知道哪些使用者輸入電子郵件地址做為使用者名稱。此觸發程序將會偵測到該項資訊，並將它記錄到稽核表中。

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]+$");
    }
}

將程式碼加入至專案中 TestScripts 資料夾的 Test.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

請參閱

Share via

HOW TO：使用 Common Language Run-time 整合建立和執行 SQL Server 觸發程序

建立 SQL Server 觸發程序

若要建立 SQL Server 觸發程序

範例

請參閱

工作

參考

概念

其他資源

其他資源