CLR觸發器的示例分析

這篇文章主要介紹了CLR觸發器的示例分析，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

CLR觸發器實現代碼：

[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();        }     }  }}

當在CLR觸發器內部調用 Transaction.Rollback 方法時，將引發異常并顯示不明確的錯誤消息，必須在try/catch 塊中包裝此方法或命令。您會看到如下錯誤消息：

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.

此異常是預期行為，需要 try/catch 塊才能繼續執行代碼。當完成執行CLR觸發器代碼時，將引發另一個異常。

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.
此異常也是預期行為。

調用該CLR觸發器的例子

盡管引發了兩個異常，仍可以回滾事務，并且更改不會提交到表中。

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

感謝你能夠認真閱讀完這篇文章，希望小編分享的“CLR觸發器的示例分析”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!