SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rollback in trigger


Rollback in trigger

Author
Message
Krishna1
Krishna1
SSC Eights!
SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)

Group: General Forum Members
Points: 836 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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63400 Visits: 17966
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 Modens 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)
Adi Cohn
Adi Cohn
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8305 Visits: 6594
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/
Krishna1
Krishna1
SSC Eights!
SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)

Group: General Forum Members
Points: 836 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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63400 Visits: 17966
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 Modens 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)
Krishna1
Krishna1
SSC Eights!
SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)

Group: General Forum Members
Points: 836 Visits: 556
thanks Smile
Mr.Sahand
Mr.Sahand
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63400 Visits: 17966
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 Modens 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search