April 30, 2013 at 7:38 am
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
April 30, 2013 at 9:15 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2013 at 9:21 am
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/
April 30, 2013 at 9:32 am
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.
April 30, 2013 at 9:49 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2013 at 10:57 am
thanks 🙂
July 22, 2015 at 7:35 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply