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触发器的示例分析”这篇文章对大家有帮助，同时也希望大家多多支持编程网，关注编程网精选频道，更多相关知识等着你来学习!