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.
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.