SQLCLR 開發系列（三）：CLR 觸發器開發與實戰範例

前言

SQL Server 2005 支援兩類觸發器：DDL 觸發器（回應 CREATE/ALTER/DROP）與 DML 觸發器（回應 INSERT/UPDATE/DELETE）。在 SQLCLR 中，觸發器同樣以靜態方法形式實作，並以 [SqlTrigger] 屬性標註元資料。本文將示範如何建立一個 DML UPDATE 觸發器，自動記錄欄位變更歷程。

觸發器屬性設定

[Microsoft.SqlServer.Server.SqlTrigger(
    Name = "NameInfoTrigger",
    Target = "NameInfo",
    Event = "FOR UPDATE")]
public static void GetChange()

• Name：觸發器名稱
• Target：目標資料表
• Event：觸發事件（FOR/AFTER/INSTEAD OF INSERT/UPDATE/DELETE）

完整程式碼實作

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

public partial class Triggers
{
    [Microsoft.SqlServer.Server.SqlTrigger(Name = "NameInfoTrigger", Target = "NameInfo", Event = "FOR UPDATE")]
    public static void GetChange()
    {
        using (SqlConnection cn = new SqlConnection())
        {
            cn.ConnectionString = "context connection=true";
            cn.Open();
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = "insert into ChangeHistory select b.name + '->' + a.name, getdate() from INSERTED a JOIN DELETED b ON a.id = b.id";
                SqlContext.Pipe.ExecuteAndSend(cmd);
            }
        }
    }
}

關鍵技術點

內容連接 (Context Connection)

連接字串 context connection=true 讓 CLR 程式碼在目前資料庫會話內執行，無需額外驗證，效能最佳。

INSERTED / DELETED 虛擬資料表

觸發器內可存取 INSERTED（新資料）與 DELETED（舊資料）虛擬表，透過主鍵聯結比對變更內容。

SqlContext.Pipe.ExecuteAndSend

直接在伺服器端執行命令並將結果輸出，適合觸發器內的資料操作。

部署與測試環境

資料庫包含兩張表：NameInfo（主表）與 ChangeHistory（異動記錄表）。

測試執行與結果驗證

第一批 UPDATE

update [NameInfo] set [name]='David' where id=1;
update [nameinfo] set [name]='Fan' where id=2;

第二批 UPDATE

update [NameInfo] set [name]='*David*' where id=1;
update [nameinfo] set [name]='*Fan*' where id=2;

開發心得

CLR 觸發器將複雜的業務邏輯封裝在 .NET 組件中，利用強型別與豐富的類別庫優勢，比純 T-SQL 觸發器更易維護。但需注意觸發器執行屬於同一交易範圍，避免長時間運算造成封鎖。