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: Today @ 3:21 PM
Points: 13,083, Visits: 11,918
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: Wednesday, July 9, 2014 1:28 AM
Points: 2,105, Visits: 5,393
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: Today @ 3:21 PM
Points: 13,083, Visits: 11,918
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse