Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rollback in trigger Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 7:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
Dear All

I have read that if there is a rollback in the trigger then it rollback the transaction in the trigger plus the original dml statemt to which the trigger is attached.

I tried this with folloing code

drop table a
go
create table a( i int)
go
create trigger a_insert
on a
for insert
as
begin
declare @a as varchar(40)
set @a = 'count of transaction' + CONVERT(varchar(10), @@TRANCOUNT)
print @a
rollback
end
go

insert into a (i) values (2)


REsult of the above insert is as follows

count of transaction1
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

why is it giving the error message?. How to avoid it?


Regards
Krishna
Post #1448031
Posted Tuesday, April 30, 2013 9:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 14,367, Visits: 14,111
The error message is happening because when you do an insert there is an implicit transaction started. This is required for atomicity. Your trigger has now rolled back the transaction that was started from the insert. When control returns to the insert it determines the transaction was rolled back.

The easiest way to avoid this is to NOT handle your transactions inside your trigger.

I have a feeling that this thread is because you are about to embark on nested transactions. Nested transactions in sql are a myth. The engine will let you do what appears to be nested transactions but the reality is they are not actually nested.

This is easy enough to prove.

begin transaction 

select @@TRANCOUNT --this will be 1 as we would expect

begin transaction

select @@TRANCOUNT --this will now be 2 as we would expect

rollback transaction

select @@TRANCOUNT --Will this be 1 or 0?



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1448101
Posted Tuesday, April 30, 2013 9:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 28, 2015 8:03 AM
Points: 2,146, Visits: 5,710
This is the way that SQL Server works and I don't think that there is a way to prevent it, but you can get around it, by working with instead of trigger and not working with a trigger. If you have instead of trigger, the code inside it replaces the statement that fired the trigger, so you can check in its code whatever needs to be checked, and if everything checks O.K, you can run the statement that fired the instead of trigger. If there is a problem, you won't run the statement. This way the statement can be cancelled, but you don't need to rollback the statement.

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1448107
Posted Tuesday, April 30, 2013 9:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
i am repalcing the roll back with raise error. Using follwing code. And it gives me error as shown below the code.
I want the code not to return error but rollback the transaction. Can anybody help to correct the code.

drop table a
go
create table a( i int)
go
create trigger a_insert
on a
for insert
as
begin
declare @a as varchar(40)
set @a = 'count of transaction' + CONVERT(varchar(10), @@TRANCOUNT)
print @a
--rollback
raiserror ( 'aa',16,0)
end
go

DROP PROCEDURE a1
go
create procedure a1 as

begin try
begin tran
insert into a (i) values (2)
select * from a
commit tran
end try
begin catch
if @@TRANCOUNT > 1
rollback tran
end catch

execute a1

It still gives me error message as
count of transaction1

(0 row(s) affected)
Msg 266, Level 16, State 2, Procedure a1, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.


Post #1448116
Posted Tuesday, April 30, 2013 9:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 14,367, Visits: 14,111
After running this through a formatter so it was legible it becomes pretty clear.



DROP TABLE a
GO

CREATE TABLE a (i INT)
GO

CREATE TRIGGER a_insert ON a
FOR INSERT
AS
BEGIN
DECLARE @a AS VARCHAR(40)

SET @a = 'count of transaction' + CONVERT(VARCHAR(10), @@TRANCOUNT)

PRINT @a

--rollback
RAISERROR ('aa', 16, 0)
END
GO

DROP PROCEDURE a1
GO

CREATE PROCEDURE a1
AS
BEGIN TRY
BEGIN TRANSACTION

INSERT INTO a (i)
VALUES (2)

SELECT *
FROM a

COMMIT TRANSACTION
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 1
ROLLBACK TRANSACTION
END CATCH

GO

EXECUTE a1


Your catch block checks to see if @@trancount > 1. Your trigger raised an exception which has rendered your transaction uncommitable but you don't roll it back. The check for @@trancount in your proc will never evaluate to true because you only begin 1 transaction. Remove "IF @@TRANCOUNT > 1" and it will work fine.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1448122
Posted Tuesday, April 30, 2013 10:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
thanks :)
Post #1448146
Posted Wednesday, July 22, 2015 12:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 22, 2015 12:54 AM
Points: 17, Visits: 34
Try this, should work for you.

CREATE TRIGGER a_insert ON a
FOR INSERT
AS
BEGIN

Set XACT_Abort oFF
begin try
DECLARE @a AS VARCHAR(40)

SET @a = 'count of transaction' + CONVERT(VARCHAR(10), @@TRANCOUNT)

PRINT @a
end try
begin catch
rollback tran
end catch

END
GO
Post #1704656
Posted Wednesday, July 22, 2015 7:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 14,367, Visits: 14,111
Mr.Sahand (7/22/2015)
Try this, should work for you.

CREATE TRIGGER a_insert ON a
FOR INSERT
AS
BEGIN

Set XACT_Abort oFF
begin try
DECLARE @a AS VARCHAR(40)

SET @a = 'count of transaction' + CONVERT(VARCHAR(10), @@TRANCOUNT)

PRINT @a
end try
begin catch
rollback tran
end catch

END
GO


A rollback inside a trigger is almost always a poor decision. If your calling code is expecting there to be a transaction it will likely fail because the transaction was rolled back inside the trigger. It is usually best to let an exception bubble up from inside a trigger and let the calling code handle the exception.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1704771
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse