Home Dashboard Directory Help
Search

Wrong error message when using COMMIT in trigger by SQL_pro


Status: 

Resolved
 as By Design Help for as By Design


4
2
Sign in
to vote
Type: Bug
ID: 772879
Opened: 11/30/2012 1:04:03 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

When doing a COMMIT in a trigger, the result is an error message wich is wrong.

The error message says "The transaction ended in the trigger. The batch has been aborted.", wich is not true because the transaction does the job and no rollback has been applied.

This error isvisible in all edition/version of SQL Server since almost 2008 and perhaps 2005.

In fact I think this has nothing to do with an error and we can say that no error message must be throw to the client application in such a case. Only a simple warning will be acceptable.
Details
Sign in to post a comment.
Posted by Microsoft on 12/5/2012 at 12:00 PM
Hello,
The behavior is by design. Trigger is always executed in the context of a transaction (we bump trancount upon entering the trigger). So if you end the transaction inside the trigger - commit/rollback then the batch will be aborted. It is considered a fatal error since further changes by the trigger cannot be handled in a transactional manner.
So there is no need to commit inside a trigger. You can however issue rollback upon hitting some error condition or based on your business logic.

--
Umachandar, SQL Programmability Team
Posted by Martin Smith on 12/2/2012 at 10:28 AM
The error message seems correct to me.

The transaction did end in the trigger when you committed it. It doesn't say that it was rolled back.

This is unexpected/discouraged and regarded as a batch aborting error.

If you change your insert statements batch to

INSERT INTO T VALUES (1);
SELECT 'After insert'

You will see that the second statement does not run. This is because the batch was aborted as stated.
Sign in to post a workaround.