共用方式為


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

將 [觸發程序] 項目加入至 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 觸發程序

  1. 開啟現有的 [SQL CLR 資料庫專案],或建立一個新專案。 如需詳細資訊,請參閱HOW TO:針對使用 SQL Server Common Language Run-time 整合的資料庫物件建立專案

  2. 在 [專案] 功能表中選取 [加入新項目]。

  3. 在 [加入新項目] 對話方塊中,選取 [觸發程序]。

  4. 為新觸發程序輸入 [名稱]。

  5. 加入執行觸發程序時執行的程式碼。 請參閱遵循此程序的第一個範例。

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

    注意事項注意事項

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

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

  8. 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 整合的資料庫物件建立專案

  9. 查看所示的結果輸出視窗和選擇顯示輸出:資料庫輸出

範例

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

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

請參閱

工作

HOW TO:針對使用 SQL Server Common Language Run-time 整合的資料庫物件建立專案

HOW TO:使用 Common Language Run-time 整合建立和執行 SQL Server 預存程序

HOW TO:使用 Common Language Run-time 整合建立和執行 SQL Server 彙總

HOW TO:使用 Common Language Run-time 整合建立和執行 SQL Server 使用者定義函式

HOW TO:使用 Common Language Run-time 整合建立和執行 SQL Server 使用者定義型別

逐步解說:使用 Managed 程式碼建立預存程序

HOW TO:偵錯 SQL CLR 預存程序

參考

SQL CLR 資料庫專案和資料庫物件的屬性

概念

SQL Server CLR 整合簡介 (ADO.NET)

使用 Managed 程式碼建立資料庫物件的好處

以 Managed 程式碼建立 SQL Server 物件

其他資源

SQL CLR Database Debugging