Re: CLR Trigger Rollback

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


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

"honzas" <jansotola@xxxxxxxxx> escribió en el mensaje
news:1160575233.565903.88760@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Cor Ligthert [MVP] wrote:
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" <jansotola@xxxxxxxxx> schreef in bericht
news:1160384994.575251.48140@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
A disccussion on this topic (how to perform rollback from CLR trigger)
went here last year.
(http://groups.google.com/group/microsoft.public.dotnet.framework.adonet/browse_thread/thread/10ff4535253f9241/698222981b1d15b6?lnk=st&q=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.




.

Relevant Pages

  • Re: SQL 7 vs. 2000 issue -trigger and nulls
    ... >We're having trouble with a trigger updating some tables. ... course be locked by the current transaction, ... locking data and updating rows when the COMMIT inside the trigger is ... I seriously hope that SQL Server 7.0 simply disregarded these two ...
    (microsoft.public.sqlserver.mseq)
  • Re: CLR Trigger Rollback
    ... The code throws still the same exception. ... using (SqlCommand cmd = new SqlCommand("ROLLBACK", ... CREATE TRIGGER ti_ble ON ble FOR INSERT ... Transaction is not allowed to roll ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Auditing changes made to table design (syscolumns table)
    ... With out using profiler the only way I know of is to get this info from the ... Keep in mind I know the database and the tables I would ... I attempted to write a trigger to log when a change was made to the ... > certain criteria against every transaction. ...
    (microsoft.public.sqlserver.security)
  • Transaction Abort in a trigger SQL 2008, writing to event log or t
    ... the catch block of a trigger, capture the error information and write it out ... code table and ServerOption table, and a trigger on ServerOption that ... message that the transaction was aborted in the trigger, ... declare @li_eNumber int = error_number ...
    (microsoft.public.sqlserver.programming)
  • Re: Enable/Disable a database trigger when running a dbunit test
    ... The dabase id's in this database are generated by a trigger. ... that the transaction is commited after enabling the trigger: ... rollback transaction ...
    (comp.lang.java.help)