CLR觸發(fā)器的原理比較簡(jiǎn)單，但是實(shí)現(xiàn)起來要花一番功夫，筆者總結(jié)了以下經(jīng)驗(yàn)，還有一個(gè)異常的實(shí)例。

CLR觸發(fā)器實(shí)現(xiàn)代碼：

[Microsoft.SqlServer.Server.SqlTrigger(Name = "Trigger1", Target = 
"ERP_STOCKYaoHuoDingDan", Event = "FOR INSERT")]  
public static void DingDanIDSameGongYingShangGUIDMustSame()  
{  
  using (SqlConnection connection = new SqlConnection
(@"context connection=true"))  
  {  
    connection.Open();  
    SqlCommand command = new SqlCommand(@"SELECT COUNT(A.DingDanID)
 FROM ERP_STOCKYaoHuoDingDan AS
 A,INSERTED AS B WHERE A.DingDanID=B.DingDanID AND
 A.GongYingShangGUID<>B.GongYingShangGUID", connection);  
    int i=(int)command.ExecuteScalar();  
     if (i>0)  
   {  
    try  
     {  
      //如果要插入的記錄不合法,則回滾.   
       Transaction trans = Transaction.Current;  
       trans.Rollback();  
     }  
     catch (SqlException ex)  
       {  
              connection.Close(); 
       }  
   }
  }
}
 

當(dāng)在CLR觸發(fā)器內(nèi)部調(diào)用 Transaction.Rollback 方法時(shí)，將引發(fā)異常并顯示不明確的錯(cuò)誤消息，必須在try/catch 塊中包裝此方法或命令。您會(huì)看到如下錯(cuò)誤消息：

Msg 6549, Level 16, State 1, Procedure trig_InsertValidator, Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'trig_InsertValidator': System.Data.SqlClient.SqlException: Transaction is not allowed to roll back inside a user defined routine, trigger or aggregate because the transaction is not started in that CLR level. Change application logic to enforce strict transaction nesting… User transaction, if any, will be rolled back.

此異常是預(yù)期行為，需要 try/catch 塊才能繼續(xù)執(zhí)行代碼。當(dāng)完成執(zhí)行CLR觸發(fā)器代碼時(shí)，將引發(fā)另一個(gè)異常。

Msg 3991, Level 16, State 1, Procedure trig_InsertValidator, Line 1 The context transaction which was active before entering user defined routine, trigger or aggregate "trig_InsertValidator" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting. The statement has been terminated.
此異常也是預(yù)期行為。

調(diào)用該CLR觸發(fā)器的例子

盡管引發(fā)了兩個(gè)異常，仍可以回滾事務(wù)，并且更改不會(huì)提交到表中。

try  
{  
 
    //用到此觸發(fā)器的方法  
}  
catch (SqlException ex)  
{  
   if (ex.Number == 3991)  
   {  
      LabelInfor.Text = "同一張訂單必須是同一家供應(yīng)商。";  
   }  
}  
catch (Exception ex)  
{  
 ......  
} 

以上就是對(duì)CLR觸發(fā)器的實(shí)戰(zhàn)演練。

【編輯推薦】

1. 淺析C#數(shù)組操作方法
2. C#數(shù)組操作全面分析
3. C#數(shù)組和串操作經(jīng)驗(yàn)總結(jié)
4. 為你解疑：CLR是什么？
5. 分析與對(duì)比CLR Via C#靜態(tài)構(gòu)造函數(shù)的性能