Multiple Connections in Managed Trigger

Hi All,

I am trying to open multiple connections in a Managed Trigger but encoutering an error as :

System.Data.SqlClient.SqlException: Transaction context in use by another session.

Below is the sample code:

public partial class Triggers
{

[Microsoft.SqlServer.Server.SqlTrigger (Name="TrgInsertContract", Target="Contracts", Event="FOR INSERT")]
public static void TrgInsertContract()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();

SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * from " + "inserted WHERE Active=1";
SqlDataReader reader;
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
connection.Close();
SqlConnection connection1 = new SqlConnection("Initial Catalog=TestDB;Data Source=SHAIKDEV;User ID=sa;password=****");
connection1.Open();
}

Any help please ?

Thanks...

Hi Nagul!

Most probably, your trigger runs in a transaction. And I think, your second connection is a loopback connection to the same server (which creates a new session). Currently, two sessions cannot share one transaction. You can avoid the problem by placing the second connection in suppress-transaction TransactionScope:

using ( new TransactionScope (TransactionScopeOption.Suppress ) )

{

// work with second connection (open etc.)

}

|||

Hi Vadim,

This solution worked great for me! Thanks

Nick