Wrong error message when using COMMIT in trigger by SQL_pro

Resolved

as By Design Help for as By Design

Type:	Bug
ID:	772879
Opened:	11/30/2012 1:04:03 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

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 (expand)

French

SQL Server 2012 - Enterprise Edition

SQL Engine

Windows Server 2012 (all editions)

French

CREATE TABLE T (C INT);
GO

CREATE TRIGGER E
ON T
FOR INSERT, UPDATE
AS
IF EXISTS(SELECT *
         FROM T
         WHERE C <> 0)
     COMMIT;
GO

INSERT INTO T VALUES (1);
GO

--> see error message

SELECT * FROM T
GO

/*
C
----
1
*/

"
Error message :

Msg 3609, Niveau 16, État 1, Ligne 1
The transaction ended in the trigger. The batch has been aborted.
"

"
Msg ????, Niveau 16, État 1, Ligne 1
The transaction ended in the trigger. The batch has been done.
"

0 attachments

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.

Microsoft