Example Analysis of CLR Flip-flop

This article mainly introduces the CLR trigger example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

CLR trigger implementation code:

[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 used AS B WHERE A.DingDanID=B.DingDanID AND A.GongYingShangGUIDB.GongYingShangGUID", connection); int I = (int) command.ExecuteScalar () If (I > 0) {try {/ / rollback if the record to be inserted is illegal. Transaction trans = Transaction.Current; trans.Rollback ();} catch (SqlException ex) {connection.Close ();}

When the Transaction.Rollback method is called inside a CLR trigger, an exception is thrown and an ambiguous error message is displayed, and the method or command must be wrapped in a try/catch block. You will see the following error message:

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.

This exception is expected behavior and requires a try/catch block to continue code execution. When you finish executing the CLR trigger code, another exception is thrown.

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.

This exception is also the expected behavior.

An example of calling the CLR trigger

Although two exceptions are thrown, the transaction can be rolled back and the changes are not committed to the table.

Try {/ / the method used for this trigger} catch (SqlException ex) {if (ex.Number = = 3991) {LabelInfor.Text = "the same order must be the same vendor." Catch (Exception ex) {. } Thank you for reading this article carefully. I hope the article "sample Analysis of CLR triggers" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!