SQL Server CLR 整合全攻略（四）：CLR 觸發器實作與部署

前言：關於 CLR 觸發器的效能考量

CLR 支援 DML（資料操作）與 DDL（資料定義）兩種觸發器。但一般不建議在觸發器中使用 CLR 執行耗時操作（如發送郵件），因觸發器屬於交易範圍，外部等待時間會計入交易，嚴重影響並發效能。

1. CLR DML 觸發器範例

針對 pubs 資料庫 authors 資料表的 INSERT、UPDATE、DELETE 操作，輸出對應動作名稱。

[Microsoft.SqlServer.Server.SqlTrigger(
    Name = "UF_DML_Trigger",
    Target = "dbo.authors",
    Event = "FOR INSERT, UPDATE, DELETE")]
public static void UF_DML_Trigger()
{
    switch (SqlContext.TriggerContext.TriggerAction)
    {
        case TriggerAction.Insert:
            SqlContext.Pipe.Send("Trigger Insert");
            break;
        case TriggerAction.Update:
            SqlContext.Pipe.Send("Trigger Update");
            break;
        case TriggerAction.Delete:
            SqlContext.Pipe.Send("Trigger Delete");
            break;
        default:
            break;
    }
}

2. CLR DDL 觸發器範例

禁止刪除 pubs 資料庫上的預存程序，嘗試刪除時自動回滾。

[Microsoft.SqlServer.Server.SqlTrigger(
    Name = "UF_DDL_Trigger",
    Target = "pubs",
    Event = "DropProcedure")]
public static void UF_DDL_Trigger()
{
    switch (SqlContext.TriggerContext.TriggerAction)
    {
        case TriggerAction.DropProcedure:
            try
            {
                Transaction trans = Transaction.Current;
                trans.Rollback();
                SqlContext.Pipe.Send("Drop Proc has Rollback");
            }
            catch (SqlException ex)
            {
                // 捕獲預期例外
            }
            break;
        default:
            break;
    }
}

3. 部署與呼叫 T-SQL 腳本

組件建立方法請參閱前文章。部署觸發器：

-- DML 觸發器
CREATE TRIGGER UF_DML_Trigger
ON dbo.authors
FOR INSERT, UPDATE, DELETE
AS EXTERNAL NAME CLRDemoAssembly.Triggers.UF_DML_Trigger;
GO

-- DDL 觸發器（資料庫範圍）
CREATE TRIGGER UF_DDL_Trigger
ON DATABASE
FOR DROP_PROCEDURE
AS EXTERNAL NAME CLRDemoAssembly.Triggers.UF_DDL_Trigger;
GO

4. TriggerAction 列舉完整列表

包含所有 DDL/DML 事件動作，如 Insert、Update、Delete、CreateTable、DropProcedure、AlterIndex、GrantStatement 等 100 餘項。完整列表請參考原文件或 MSDN 文件。

5. 完整程式碼（C#）

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Transactions;

public partial class Triggers
{
    [Microsoft.SqlServer.Server.SqlTrigger(
        Name = "UF_DML_Trigger",
        Target = "dbo.authors",
        Event = "FOR INSERT, UPDATE, DELETE")]
    public static void UF_DML_Trigger()
    {
        switch (SqlContext.TriggerContext.TriggerAction)
        {
            case TriggerAction.Insert:
                SqlContext.Pipe.Send("Trigger Insert"); break;
            case TriggerAction.Update:
                SqlContext.Pipe.Send("Trigger Update"); break;
            case TriggerAction.Delete:
                SqlContext.Pipe.Send("Trigger Delete"); break;
            default: break;
        }
    }

    [Microsoft.SqlServer.Server.SqlTrigger(
        Name = "UF_DDL_Trigger",
        Target = "pubs",
        Event = "DropProcedure")]
    public static void UF_DDL_Trigger()
    {
        switch (SqlContext.TriggerContext.TriggerAction)
        {
            case TriggerAction.DropProcedure:
                try
                {
                    Transaction.Current.Rollback();
                    SqlContext.Pipe.Send("Drop Proc has Rollback");
                }
                catch (SqlException) { }
                break;
            default: break;
        }
    }
}

最佳實務建議

• 避免在觸發器中進行網路呼叫、長時間運算
• DDL 觸發器適合用於審計、防護性防禦（如禁止 DROP）
• DML 觸發器若需非同步處理，建議搭配 Service Broker 或資料表佇列
• 充分測試交易回滾行為，避免資料不一致