CLR Trigger Rollback

[honzas]

A disccussion on this topic (how to perform rollback from CLR trigger)
went here last year.
(http://groups.google.com/group/micr...=CLR+trigger+rollback&rnum=1#698222981b1d15b6)

Pablo Castro (and also some MSDN articles) was answering:

Try:

System.Transactions.Transaction.Current.Rollback();


That should do it for your scenario.

I tryed it but I'm still recieving following error message:

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.

What am I doing wrong?

Jan Sotola.

 

[Cor Ligthert [MVP]]

Jan,

What am I doing wrong?

There is probably something in your code that does not go right.

Cor

 

[honzas]

Jan,


There is probably something in your code that does not go right.

OK, my question was quite stupid.
I should have asked "What would I be doing wrong?"
Or, better, I should have attached a source code that is throwing the
exception - so here it is:

1) I've got such a simple class with one method:
---------------------------------------------------
using System;
using System.Data;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace SqlClr
{
public class ClrTriggers
{
[SqlTrigger(Name = @"ti_ble", Target = "[dbo].[ble]", Event =
"FOR INSERT")]
public static void TiBle()
{
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(@"SELECT val FROM
INSERTED;", connection);
SqlDataReader reader = command.ExecuteReader();
reader.Read();
string val = (string)reader[0];
reader.Close();
if (val == "wrong")
System.Transactions.Transaction.Current.Rollback();
}
}
}
}
---------------------------------------------------

2) I have compilled it and registered the assembly in my SQL Server
2005 developer edition (running on my localhost).
Than I've created a trigger on a very simple table:

CREATE TABLE ble (id int, val varchar(255))

CREATE TRIGGER ti_ble ON ble FOR INSERT
AS EXTERNAL NAME SqlClr.[SqlClr.ClrTriggers].TiBle

3) At least I've tried to insert a value "wrong" into the "ble" table
(this value should fire rollback inside the clr trigger:

INSERT INTO ble(val) VALUES ('wrong')

And I'm receiving following error message:

Msg 6549, Level 16, State 1, Procedure ti_ble, Line 1
A .NET Framework error occurred during execution of user defined
routine or aggregate 'ti_ble':
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.
System.Data.SqlClient.SqlException:
at System.Data.SqlServer.Internal.StandardEventSink.HandleErrors()
at
System.Data.SqlServer.Internal.ClrLevelContext.SuperiorTransaction.Rollback()
at
System.Transactions.TransactionStateSubordinateActive.Rollback(InternalTransaction
tx, Exception e)
at System.Transactions.Transaction.Rollback()
at SqlClr.ClrTriggers.TiBle()
.. User transaction, if any, will be rolled back.
The statement has been terminated.

 

[Cor Ligthert [MVP]]

Honzas,

The transaction is in my idea nowhere started in your code.

http://msdn2.microsoft.com/en-us/library/ms172152.aspx

Cor

Jan,


There is probably something in your code that does not go right.

OK, my question was quite stupid.
I should have asked "What would I be doing wrong?"
Or, better, I should have attached a source code that is throwing the
exception - so here it is:

1) I've got such a simple class with one method:
---------------------------------------------------
using System;
using System.Data;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace SqlClr
{
public class ClrTriggers
{
[SqlTrigger(Name = @"ti_ble", Target = "[dbo].[ble]", Event =
"FOR INSERT")]
public static void TiBle()
{
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(@"SELECT val FROM
INSERTED;", connection);
SqlDataReader reader = command.ExecuteReader();
reader.Read();
string val = (string)reader[0];
reader.Close();
if (val == "wrong")
System.Transactions.Transaction.Current.Rollback();
}
}
}
}
---------------------------------------------------

2) I have compilled it and registered the assembly in my SQL Server
2005 developer edition (running on my localhost).
Than I've created a trigger on a very simple table:

CREATE TABLE ble (id int, val varchar(255))

CREATE TRIGGER ti_ble ON ble FOR INSERT
AS EXTERNAL NAME SqlClr.[SqlClr.ClrTriggers].TiBle

3) At least I've tried to insert a value "wrong" into the "ble" table
(this value should fire rollback inside the clr trigger:

INSERT INTO ble(val) VALUES ('wrong')

And I'm receiving following error message:

Msg 6549, Level 16, State 1, Procedure ti_ble, Line 1
A .NET Framework error occurred during execution of user defined
routine or aggregate 'ti_ble':
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.
System.Data.SqlClient.SqlException:
at System.Data.SqlServer.Internal.StandardEventSink.HandleErrors()
at
System.Data.SqlServer.Internal.ClrLevelContext.SuperiorTransaction.Rollback()
at
System.Transactions.TransactionStateSubordinateActive.Rollback(InternalTransaction
tx, Exception e)
at System.Transactions.Transaction.Rollback()
at SqlClr.ClrTriggers.TiBle()
. User transaction, if any, will be rolled back.
The statement has been terminated.

Cor

 

[Jesús López]

I think you should use a ROLLBACK command instead of
Transaction.Current.Rollback():

using (SqlCommand cmd = new SqlCommand("ROLLBACK", cn))
{
cmd.ExecuteNonQuery();
}

Regards:

Jesús López

Jan,


There is probably something in your code that does not go right.

OK, my question was quite stupid.
I should have asked "What would I be doing wrong?"
Or, better, I should have attached a source code that is throwing the
exception - so here it is:

1) I've got such a simple class with one method:
---------------------------------------------------
using System;
using System.Data;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace SqlClr
{
public class ClrTriggers
{
[SqlTrigger(Name = @"ti_ble", Target = "[dbo].[ble]", Event =
"FOR INSERT")]
public static void TiBle()
{
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(@"SELECT val FROM
INSERTED;", connection);
SqlDataReader reader = command.ExecuteReader();
reader.Read();
string val = (string)reader[0];
reader.Close();
if (val == "wrong")
System.Transactions.Transaction.Current.Rollback();
}
}
}
}
---------------------------------------------------

2) I have compilled it and registered the assembly in my SQL Server
2005 developer edition (running on my localhost).
Than I've created a trigger on a very simple table:

CREATE TABLE ble (id int, val varchar(255))

CREATE TRIGGER ti_ble ON ble FOR INSERT
AS EXTERNAL NAME SqlClr.[SqlClr.ClrTriggers].TiBle

3) At least I've tried to insert a value "wrong" into the "ble" table
(this value should fire rollback inside the clr trigger:

INSERT INTO ble(val) VALUES ('wrong')

And I'm receiving following error message:

Msg 6549, Level 16, State 1, Procedure ti_ble, Line 1
A .NET Framework error occurred during execution of user defined
routine or aggregate 'ti_ble':
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.
System.Data.SqlClient.SqlException:
at System.Data.SqlServer.Internal.StandardEventSink.HandleErrors()
at
System.Data.SqlServer.Internal.ClrLevelContext.SuperiorTransaction.Rollback()
at
System.Transactions.TransactionStateSubordinateActive.Rollback(InternalTransaction
tx, Exception e)
at System.Transactions.Transaction.Rollback()
at SqlClr.ClrTriggers.TiBle()
. User transaction, if any, will be rolled back.
The statement has been terminated.

Cor

 

[honzas]

I'm in an implicit transaction (executed within an INSERT command).

When I run the INSERT coman within an explicit transaction like this
BEGIN TRAN
INSERT ...
COMMIT TRAN
the error is the same.

I'm afraid using TransactionScope would not be the right thik for me
(or I do it undestand wrong).
Anyway, all my experiments with a TranscationScope ended with using DTC
(which I'm not allowed to use).

BTW - I've tried to call rollback the same way from a CLR Procedure and
I achieved the same error message.

Probably, I'm still forgetting to do some important step or setting...

CAN SOMEBODY SEND ME A COMPLETE EXAMPLE (clr code + T-SQL code to
execute it) WHERE
THE "System.Transactions.Transaction.Current.Rollback();" WORKS
PROPERLY?


Honzas.

Honzas,

The transaction is in my idea nowhere started in your code.

http://msdn2.microsoft.com/en-us/library/ms172152.aspx

Cor

Jan,

What am I doing wrong?

There is probably something in your code that does not go right.

OK, my question was quite stupid.
I should have asked "What would I be doing wrong?"
Or, better, I should have attached a source code that is throwing the
exception - so here it is:

1) I've got such a simple class with one method:
---------------------------------------------------
using System;
using System.Data;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace SqlClr
{
public class ClrTriggers
{
[SqlTrigger(Name = @"ti_ble", Target = "[dbo].[ble]", Event =
"FOR INSERT")]
public static void TiBle()
{
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(@"SELECT val FROM
INSERTED;", connection);
SqlDataReader reader = command.ExecuteReader();
reader.Read();
string val = (string)reader[0];
reader.Close();
if (val == "wrong")
System.Transactions.Transaction.Current.Rollback();
}
}
}
}
---------------------------------------------------

2) I have compilled it and registered the assembly in my SQL Server
2005 developer edition (running on my localhost).
Than I've created a trigger on a very simple table:

CREATE TABLE ble (id int, val varchar(255))

CREATE TRIGGER ti_ble ON ble FOR INSERT
AS EXTERNAL NAME SqlClr.[SqlClr.ClrTriggers].TiBle

3) At least I've tried to insert a value "wrong" into the "ble" table
(this value should fire rollback inside the clr trigger:

INSERT INTO ble(val) VALUES ('wrong')

And I'm receiving following error message:

Msg 6549, Level 16, State 1, Procedure ti_ble, Line 1
A .NET Framework error occurred during execution of user defined
routine or aggregate 'ti_ble':
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.
System.Data.SqlClient.SqlException:
at System.Data.SqlServer.Internal.StandardEventSink.HandleErrors()
at
System.Data.SqlServer.Internal.ClrLevelContext.SuperiorTransaction.Rollback()
at
System.Transactions.TransactionStateSubordinateActive.Rollback(InternalTransaction
tx, Exception e)
at System.Transactions.Transaction.Rollback()
at SqlClr.ClrTriggers.TiBle()
. User transaction, if any, will be rolled back.
The statement has been terminated.

Cor

"honzas" <[email protected]> schreef in bericht
A disccussion on this topic (how to perform rollback from CLR trigger)
went here last year.
(http://groups.google.com/group/micr...=CLR+trigger+rollback&rnum=1#698222981b1d15b6)

Pablo Castro (and also some MSDN articles) was answering:

Try:

System.Transactions.Transaction.Current.Rollback();


That should do it for your scenario.


--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

I tryed it but I'm still recieving following error message:

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.

What am I doing wrong?

Jan Sotola.

 

[honzas]

I'm afraid it has not helped.
The code throws still the same exception.

Honzas.

I think you should use a ROLLBACK command instead of
Transaction.Current.Rollback():

using (SqlCommand cmd = new SqlCommand("ROLLBACK", cn))
{
cmd.ExecuteNonQuery();
}

Regards:

Jesús López

Jan,

What am I doing wrong?

There is probably something in your code that does not go right.

OK, my question was quite stupid.
I should have asked "What would I be doing wrong?"
Or, better, I should have attached a source code that is throwing the
exception - so here it is:

1) I've got such a simple class with one method:
---------------------------------------------------
using System;
using System.Data;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace SqlClr
{
public class ClrTriggers
{
[SqlTrigger(Name = @"ti_ble", Target = "[dbo].[ble]", Event =
"FOR INSERT")]
public static void TiBle()
{
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(@"SELECT val FROM
INSERTED;", connection);
SqlDataReader reader = command.ExecuteReader();
reader.Read();
string val = (string)reader[0];
reader.Close();
if (val == "wrong")
System.Transactions.Transaction.Current.Rollback();
}
}
}
}
---------------------------------------------------

2) I have compilled it and registered the assembly in my SQL Server
2005 developer edition (running on my localhost).
Than I've created a trigger on a very simple table:

CREATE TABLE ble (id int, val varchar(255))

CREATE TRIGGER ti_ble ON ble FOR INSERT
AS EXTERNAL NAME SqlClr.[SqlClr.ClrTriggers].TiBle

3) At least I've tried to insert a value "wrong" into the "ble" table
(this value should fire rollback inside the clr trigger:

INSERT INTO ble(val) VALUES ('wrong')

And I'm receiving following error message:

Msg 6549, Level 16, State 1, Procedure ti_ble, Line 1
A .NET Framework error occurred during execution of user defined
routine or aggregate 'ti_ble':
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.
System.Data.SqlClient.SqlException:
at System.Data.SqlServer.Internal.StandardEventSink.HandleErrors()
at
System.Data.SqlServer.Internal.ClrLevelContext.SuperiorTransaction.Rollback()
at
System.Transactions.TransactionStateSubordinateActive.Rollback(InternalTransaction
tx, Exception e)
at System.Transactions.Transaction.Rollback()
at SqlClr.ClrTriggers.TiBle()
. User transaction, if any, will be rolled back.
The statement has been terminated.

Cor

"honzas" <[email protected]> schreef in bericht
A disccussion on this topic (how to perform rollback from CLR trigger)
went here last year.
(http://groups.google.com/group/micr...=CLR+trigger+rollback&rnum=1#698222981b1d15b6)

Pablo Castro (and also some MSDN articles) was answering:

Try:

System.Transactions.Transaction.Current.Rollback();


That should do it for your scenario.


--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

I tryed it but I'm still recieving following error message:

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.

What am I doing wrong?

Jan Sotola.