SQLserverCLR-Trigger建立、部屬 快速使用備忘錄（一）

CLR簡介：
CLR 中執行的程式碼稱為 Managed 程式碼。 CLR 提供程式執行所需的各種功能及服務，包括 just-in-time (JIT) 編譯、配置及管理記憶體、強制使用型別安全性、例外處理、執行緒管理及安全性。
利用 Microsoft SQL Server 中裝載的 CLR (稱為 CLR 整合)，您能夠以 Managed 程式碼撰寫預存程序、觸發程序、使用者定義函式、使用者定義型別及使用者定義彙總。 因為 Managed 程式碼在執行前會編譯成原生程式碼，所以在部分案例中可大幅提升效能。
Managed 程式碼使用程式碼存取安全性 (CAS)、程式碼連結及應用程式定義域，以防止組件執行某些作業。 SQL Server 使用 CAS 協助保護 Managed 程式碼，並防止損害作業系統或資料庫伺服器。
本節的目的是僅提供以 SQL Server CLR 整合進行程式設計快速入門所需的足夠資訊，並未包含廣泛資訊。 如需詳細資訊，請參閱您所使用之 SQL Server 版本的《SQL Server 線上叢書》版本。（摘錄自 微軟）
本篇不是教學文，只是個大約筆記。這東西，大概只有一開始架構會寫好，
再來就不會去動了。怕日後忘記，留個備忘，方便快速使用。
1.開啟vs20XX ，新增SqlServer 資料庫專案—>新增項目—>SQL CLR C#—> SQL CLR C# 觸發程序
2.由於不支援 Nuget ，我用得很習慣的log4net 不能用TAT ，只好自己寫了。
不寫也可以，但由於這個你沒辦法用debug模式偵錯，要找到錯誤比較麻煩。
所以還是建議寫一下。
public static void pasteTrigger ()
{
    SqlTriggerContext triggContext = SqlContext.TriggerContext;
    DateTime dt = DateTime.Now;
    string FileFullPath = "D:\\SqlCLR\\Log\\Paste" + dt.ToString("yyyy-MM-dd") + ".txt";
    // 以您自己的程式碼取代
    SqlContext.Pipe.Send("觸發程序 FIRED");
    if (triggContext.TriggerAction == TriggerAction.Update)
    {
        FileInfo Fi = new FileInfo(FileFullPath);
        if (!Fi.Directory.Exists)
        {
            Fi.Directory.Create();
        }
        using (StreamWriter rw = File.AppendText(FileFullPath))
        {
            if (triggContext.IsUpdatedColumn(8))
            {
                rw.WriteLine(DateTime.Now + "-Trigger 建立，偵測到資料變更，準備傳送命令..");
                //todo 建立websocket 傳送資料給 application
            }
        }
           
    }
} 先寫好測試的準備丟去sqlserver執行。
比較少見的語法
triggContext.TriggerAction == TriggerAction.Update 我們只要抓update的動作，至於程式怎麼判斷的可以參考這篇
簡單說，當資料表的資料有變更的時候，其實會建立兩個table 『inserted 、deleted』，
前者紀錄insert資料 以及update 後的資料;後者紀錄delete資料以及update前的資料。
而這段  
triggContext.IsUpdatedColumn(8) 主要是判斷inserted裡面的某欄位有沒有被更新
3.掛載到Sqlserver
編譯後，會在bin的資料夾內看到 .dll的檔案，
先到資料庫執行

sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

還有一段

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

不確定是不是一定要執行，等我想到再測吧。

然後，建立Assembly
CREATE ASSEMBLY   pasteTrigger
FROM 'd:\SqlCLR\KP2spcCLR.dll'
WITH PERMISSION_SET = UNSAFE然後....疑 錯誤。 組件 'KP2spcCLR' 的 CREATE ASSEMBLY 失敗，因為組件 'KP2spcCLR' 沒有 PERMISSION_SET = UNSAFE 的授權。下列之一為 True 時，組件才會獲得授權: 資料庫擁有者 (DBO) 有 UNSAFE ASSEMBLY 權限，且資料庫已開啟 TRUSTWORTHY 資料庫屬性; 或者組件已使用憑證或非對稱金鑰簽署，且對應的登入具有 UNSAFE ASSEMBLY 權限。
恩…我在建立CLR的時候，沒有把權限設定為unsafe，所以不給通過。詳情看這裡
權限預設是safe 只能連DB內的資料
而External_access 可以連外部資料,e.g. 開檔案 、socket
unsafe 則可以呼叫win32 API
要使用External_access以上的權限，CLR必須有簽章
簽章的建立步驟如下：
a.右鍵開啟專案屬性

b.找到SQL CLR ，修改權限等級

c.建立簽署


d.重建





此時先到資料夾底下找到金鑰(XXX.key)的檔案，然後bin內的dll也一併複製到同樣的地方。
資料庫建立該dll的簽署
USE master
CREATE ASYMMETRIC KEY PasteClr FROM EXECUTABLE FILE = 'D:\SqlCLR\KP2spcCLR.dll' 
PasteClr 是金鑰名稱，記住要跟dll放在一起。 CREATE LOGIN pasteClrLogin FROM ASYMMETRIC KEY PasteClr
GRANT EXTERNAL ACCESS ASSEMBLY TO pasteClrLogin
再來根據金鑰建立登入的使用者名稱 pasteClrLogin。
最後是設定權限給該使用者。
現在可以執行上面錯誤的那段了
CREATE ASSEMBLY   pasteTrigger
FROM 'd:\SqlCLR\KP2spcCLR.dll'
WITH PERMISSION_SET = External_access

4.最後針對table建立trigger
CREATE TRIGGER pasteUpdate
ON pasteConfig
FOR UPDATE
AS
      EXTERNAL NAME pasteTrigger.Triggers.pasteTrigger
GO

『pasteUpdate』 ：trigger的名稱
on 『pasteConfig』 :table 的名稱
FOR 『update』 ：針對update
NAME 『pasteTrigger』：上面建立assembly 的名稱
pasteTrigger.『Triggers』：CLR裡面的最外圈class名稱，上面的程式碼並沒有寫到（請注意）
pasteTrigger.Triggers.『pasteTrigger』：要執行的Funcation名稱