Shulou(Shulou.com)06/02 Report--

This article mainly introduces how to create CLR objects, 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.

A CLR object is created

1. Add a new C#SQL database project to the solution, named "ServiceClient"

two。 Select or add comments for your target database. (if not prompted: right-click the ServiceClient project, select Properties, Database, browse and select your connection)

3. Add a declaration to the created service

1) right-click the "service" project in the solution browser and select "Debug" > "Start New Instance"

2) Service running: right-click the "ServiceClient" project and select "Add Service Reference"

3) enter: http://localhost:8000/services in "Service URI"

4) Click "OK"

Two CLR trigger cases

[Microsoft.SqlServer.Server.SqlTrigger (Name= "Trigger1", Target= "ERP_STOCKYaoHuoDingDan", Event= "FORINSERT")] publicstaticvoidDingDanIDSameGongYingShangGUIDMustSame () {using (SqlConnectionconnection=newSqlConnection (@ "contextconnection=true")) {connection.Open (); SqlCommandcommand=newSqlCommand (@ "SELECTCOUNT (A.DingDanID) FROMERP_STOCKYaoHuoDingDanASA,INSERTEDASBWHEREA.DingDanID=B.DingDanIDANDA.GongYingShangGUIDB.GongYingShangGUID", connection); inti= (int) command.ExecuteScalar (); if (I > 0) {try {/ / if the record to be inserted is invalid. TransactionTransactiontrans=Transaction.Current; trans.Rollback ();} catch (SqlExceptionex) {

When the Transaction.Rollback method is called inside the 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:

Msg6549,Level16,State1,Proceduretrig_InsertValidator,Line0A.NETFrameworkerroroccurredduringexecutionofuserdefinedroutineoraggregate 'trig_InsertValidator':System.Data.SqlClient.SqlException:Transactionisnotallowedtorollbackinsideauserdefinedroutine, triggeroraggregatebecausethetransactionisnotstartedinthatCLRlevel.Changeapplicationlogictoenforcestricttransactionnesting... Usertransaction,ifany,willberolledback. This exception is expected behavior and requires a try/catch block to continue code execution. When you finish executing the trigger code, another exception is thrown. Msg3991,Level16,State1,Proceduretrig_InsertValidator,Line1Thecontexttransactionwhichwasactivebeforeenteringuserdefinedroutine, triggeroraggregate "trig_InsertValidator" hasbeenendedinsideofit,whichisnotallowed.Changeapplicationlogictoenforcestricttransactionnesting. Thestatementhasbeenterminated. This exception is also the expected behavior. } connection.Close ();}}

An example of calling a 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 (SqlExceptionex) {if (ex.Number==3991) {LabelInfor.Text= "the same order must be the same vendor." ;} catch (Exceptionex) {} Thank you for reading this article carefully. I hope the article "how to create CLR objects" shared by the editor will be helpful to you. At the same time, I hope you will support us and follow the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.