SQL2005 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)
                {

 

当在触发器内部调用 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 块才能继续执行代码。当完成执行触发器代码时，将引发另一个异常。

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. 

此异常也是预期行为。

                }
            }

            connection.Close();
        }
    }

 

 

调用该触发器的例子

尽管引发了两个异常，仍可以回滚事务，并且更改不会提交到表中。

 try
 {

     //用到此触发器的方法
 }
 catch (SqlException ex)
 {
    if (ex.Number == 3991)
    {
       LabelInfor.Text = "同一张订单必须是同一家供应商。";
    }
 }
 catch (Exception ex)
 {
 }