SQLTeam.com | Weblogs | Forums

Rollback tran in trigger

I have very complex database, with lot of triggers, where various conditions an dlimitations are checked.
In the case where not all data are following rules rollback source trasactions using
rollback tran
raiserror (xxxxx,ll,-1, cccc)
RETURN
I used the same logic from SQL server ver7.9 to sqlserver 2008R2 and everything was OK.
Moving database to SQLServer 2014, facing a problem.
When trying to enter data that dont pass some tests in trigger after rollback get messsage as never before:
"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.".
The fact is that there is no BEGIN TRANSACTION, Data are inserted from single insert statement.
But according to my present knowledge, if executive SQL statement is not a part of transaction. it is considered a transaction by itself. Like it is wrapped between unvisible BEGIN TRAN ...COMMIT TRAN. And it was acting that way, until now. Would be very thankfull for any useful advice how to solve problem.

hi

hope this link helps .. please see

https://gavindraper.com/2018/05/19/SQL-Server-Triggers-And-Transactions/

from sql server 2014 ( Version )

maybe you have to explicitly put a BEGIN Tran and End Tran

Please see the Version Release Documentation

You need to verify that a rollback is allowed before issuing it:

IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION;
RAISERROR(...)
...