How to get statement which causes error 3609?

Question

How to find out which statement causes error Msg 3609, Level 16, State 1, Line 4 The transaction ended in the trigger. The batch has been aborted.?

The code seems very straight-forward:

-- 1. Check 1
IF (EXISTS (
... check ...
   ))
BEGIN
  RAISERROR('Does not allow 1', 16, 1);
  IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
  RETURN;
END;

-- 2. Check 2
IF (EXISTS (
... check ...
   ))
BEGIN
  RAISERROR('Does not allow 2', 16, 1);
  IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
  RETURN;
END;

I have check if transaction exists, so I must not see that error after raised error.

EDIT:

I agree with you @gbn. I know that operation in the TRIGGER is one transaction.

The problem is that in our code (written by me and one colleague) there is a case in which RAISERROR does not rollbacks trigger. I see that in practive - the error is raised by the trigger but the new data is inserted. The syntax there was opposite - first ROLLBACK and then RAISERROR. I found that switching places will fix situation (I don't know why it works only in that way).

I decided to play a bit with @@TRANCOUNT. But when I inserted several trancount the trigger start giving me 3609. Removing @@TRANCOUNT did not fix the issue. Because I have ROLLBACK with check if there is open transaction 3609 is very strange for me. There is another trigger, but even if I disable it I still receive 3609.

Answer 1

If you use TRY/CATCH in the calling code and trigger, then the error handling process becomes a lot more predictable and reliable.

Without this, an error in the trigger (here caused by RAISERROR) aborts the batch. So you'll never see the error from RAISERROR.

See Why TRY CATCH does not suppress exception in trigger for more

Note: a trigger is always in a transaction because it is part of the DML. Any trigger code/actions must be atomic with the DML of course.