在SQL Server 2008 R2上觸發 trigger 時呼叫C#(CLR)程式
trigger是當一個table有出現insert、update、delete等動作時會額外觸發的行為，
例如可以用在當使用者被刪除時，可以設定trigger讓他一併刪除相關的資料，
優點是很方便，缺點是當trigger太多的時候，互相干擾之下會非常複雜!
這邊要說的是如何觸發trigger時呼叫C#(CLR)的程式，至於程式內容就不會多加著墨。
程式碼就先參考http://msdn.microsoft.com/en-us/library/938d9dz2(VS.80).aspx [注1]
我就從VS2010的觀點來做說明
先開一個Database Project
當新增這個專案之後，會要你設定連線的SQL，有的話最好就直接設定了，不然之後也可以到屬性頁面改。
而你用來連線的帳號必須對你的目標table有CREATE ASSEMBLY的權限。
空白的專案會長這樣
這裡要特別注意，SQL 2008 r2 只支援.net3.5和之前的版本，
所以記得到專案屬性頁面修改版本為.net3.5。(因為我新增專案的時候忘記改了)
專案底下新增一個Trigger類型的類別
他會將必要的參考加進去，預設的內容如下
1: public partial class Triggers {
2: // Enter existing table or view for the target and uncomment the attribute line
3: // [Microsoft.SqlServer.Server.SqlTrigger (Name="Trigger1", Target="Table1", Event="FOR UPDATE")]
4: public static void Trigger1() {
5: // Replace with your own code
6: SqlContext.Pipe.Send("Trigger FIRED");
7: }
8: }
這邊注意第三行被註解掉的內容
這要寫在function之前，用來告訴SQL觸發哪種類型的trigger，
範例程式碼是insert，所以我們將註解拿掉，並改成insert，接著把範例程式碼複製過來，
1: [SqlTrigger(Name="UserNameAudit", Target="Users", Event="FOR INSERT")]
2: public static void UserNameAudit()
3: {
4: SqlTriggerContext triggContext = SqlContext.TriggerContext;
5: SqlParameter userName = new SqlParameter("@username", System.Data.SqlDbType.NVarChar);
6:
7: if (triggContext.TriggerAction == TriggerAction.Insert)
8: {
9: using (SqlConnection conn = new SqlConnection("context connection=true"))
10: {
11: conn.Open();
12: SqlCommand sqlComm = new SqlCommand();
13: SqlPipe sqlP = SqlContext.Pipe;
14:
15: sqlComm.Connection = conn;
16: sqlComm.CommandText = "SELECT UserName from INSERTED";
17:
18: userName.Value = sqlComm.ExecuteScalar().ToString();
19:
20: if (IsEMailAddress(userName.ToString()))
21: {
22: sqlComm.CommandText = "INSERT UsersAudit(UserName) VALUES(userName)";
23: sqlP.Send(sqlComm.CommandText);
24: sqlP.ExecuteAndSend(sqlComm);
25: }
26: }
27: }
28: }
29:
30:
31: public static bool IsEMailAddress(string s)
32: {
33: return Regex.IsMatch(s, "^([\\w-]+\\.)*?[\\w-]+@[\\w-]+\\.([\\w-]+\\.)*?[\\w]+$");
34: }
SqlTrigger有幾個屬性要設定，Name是此trigger的名稱，隨你取，
Target是要對應的Table，Event是發生此事件才要呼叫。
如果有多個動作都要觸發的話，Event寫成"FOR INSERT UPDATE DELETE"就可以了。
到此為止是VS端的事情，接著要說明SQL端，
為了建立測試環境，先建立一個DB叫做test然後建立兩個Tabel，
在MSDN的範例裡有提供程式碼產生table
1: CREATE TABLE Users
2: (
3: UserName NVARCHAR(200) NOT NULL,
4: Pass NVARCHAR(200) NOT NULL
5: )
6:
7: CREATE TABLE UsersAudit
8: (
9: UserName NVARCHAR(200) NOT NULL
10: )
另外，SQL預設是關閉支援CLR的，所以我們要將它啟動
在SQL輸入
1: sp_configure 'clr enabled', 1
2: go
3: reconfigure
4: go
用來開啟支援CLR。
SQL端也準備好之後就可以測試了，我們可以在VS專案裡面找到Test.sql檔案，
可以在裡面寫下要測試的內容，然後按下F5，VS就會幫我們部屬到SQL Server上接著就Debug了。
另外我們也可以在SQL端查看部屬的情形
可以在Table的Trigger下看到此Trigger的名稱(非function的名稱，是SqlTrigger裡面的Name屬性)
然後在DB的Programmability底下看到此組件
根據MSDNhttp://msdn.microsoft.com/en-us/library/ms345101.aspx [注2]
有三種權限SAFE、EXTERNAL_ACCESS、UNSAFE，上面的網頁有說明可以做到的事情，
簡單來說SAFE只能連結DB內的資料。EXTERNAL_ACCESS可以連結外部資料，例如開檔案、Socket等。
而UNSAFE可以呼叫native code，也就是win32 API。
用此篇文章的方式產生的trigger權限是SAFE，能做的事情有限，如果要開檔案或是連線網路的話就不能用此權限。
下一篇會說明如何使用EXTERNAL_ACCESS。
參考資料:
How to: Create and Run a SQL Server Trigger by using Common Language Run-time Integration