SQL Server - Trigger,CLR,SP Exception

Hi,
I have written the trigger(Insert) to call .Net dll, The dll has the code to execute the Sp.
While inserting the data, the trigger shows the following error Message.

"The user transaction that has been started in user defined routine, trigger or aggregate tr_Taxonomy_XML" is not ended upon exiting from it. This is not allowed and the transaction will be rolled back. Change application logic to enforce strict transaction nesting. The statement has been terminated.."

Thanks
Nagaraj

This strikes me as bad design.

I'd call the sp directly from the trigger, unless there's some logic or state info (that you need for your CLR code) that's available in the trigger  but not in the SP. 

If I may use a process threading anology (analogy because the SQL Server engine doesn't have the same process control as Windows) the trigger is essentially a synchronus event handler that expects to return immediately after the last statement is called...you can use it to start another execution thread (e.g. exec proc ) where an 'asynchronous' (i.e. protracted) transaction can happen.

Stephen Lowe

Disclaimer: though I'm a Developer/Architect Evangelist at Microsoft, my opinions are my own.