HELP: CLR Trigger Fails when inside a user defined transaction!

[Leon_Amirreza]

when I use my C# Application to update My DB using a transation I get the
following Exception

The context transaction which was active before entering user defined
routine, trigger or aggregate "UpdateIntegrityCheck" has been ended inside
of it, which is not allowed. Change application logic to enforce strict
transaction nesting.

What should I do?

I have the following trigger in my DB:

[Microsoft.SqlServer.Server.SqlTrigger (Name="UpdateIntegrityCheck",
Target="Files", Event="FOR INSERT, UPDATE")]
public static void UpdateIntegrityCheck()
{
using (SqlConnection connection = new SqlConnection(@"context connection
= true"))
{
connection.Open();

SqlDataAdapter adapterI = new SqlDataAdapter(@"SELECT * FROM
inserted;", connection);
DataSet ds = new DataSet();
DataTable dtI = new DataTable("Inserted");
ds.Tables.Add(dtI);
adapterI.Fill(dtI);

bool error = false;
...
try
{
if (error)
Transaction.Current.Rollback();
}
catch (SqlException /*ex*/)
{
// Catch the expected exception.
}
connection.Close();

}

}

My C# code that generates this error is:

// adapter is generated by on VS that operates on a Table

try
{
adapter.Connection.Open();
SqlTransaction t = adapter.Connection.BeginTransaction();
try
{
adapter.Adapter.UpdateCommand.Transaction = t;
adapter.Adapter.InsertCommand.Transaction = t;
adapter.Adapter.DeleteCommand.Transaction = t;
//the following line
generates the Exception!
adapter.Update(_FilesList.Files);
t.Commit();
_FilesList.AcceptChanges();
result = true;
}
catch (Exception e)
{
result = false;
t.Rollback();
}
}
finally
{
adapter.Connection.Close();
}

[Erland Sommarskog]

Leon_Amirreza (amirreza...@yahoo.com) writes:
> when I use my C# Application to update My DB using a transation I get the
> following Exception
>
>
> The context transaction which was active before entering user defined
> routine, trigger or aggregate "UpdateIntegrityCheck" has been ended inside
> of it, which is not allowed. Change application logic to enforce strict
> transaction nesting.
>
> What should I do?

Read
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/302a4e4a-3172-42b6-9cc0-
4a971ab49c1c.htm
scroll down to "Validating and Cancelling Invalid Transactions" which
discusses this error, and says it it is expected and that you need to
handle it.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx