Trigger handle multiple updates

Question

I've got a trigger after update:

CREATE TRIGGER tgr_passagier_vlucht
ON Vlucht
AFTER UPDATE
AS
BEGIN
IF @@ROWCOUNT=0
    RETURN
SET NOCOUNT ON
IF EXISTS 
     (SELECT *
     FROM inserted I
     WHERE EXISTS(SELECT * FROM PassagierVoorVlucht P WHERE P.vluchtnummer = I.vluchtnummer))
BEGIN
    ROLLBACK TRANSACTION
    RAISERROR('Cannot update, Passenger is linked to flight ', 16,1)            
END                                                                   
END

i got also some test cases:

/*TestCase 1 --------------True*/
UPDATE Vlucht
SET gatecode = 'B'
WHERE vluchtnummer = 5314

/*TestCase 2 --------------True*/
UPDATE Vlucht
SET gatecode = 'A'
WHERE vluchtnummer = 5318

/*TestCase 3 --------------False*/
UPDATE Vlucht
SET gatecode = 'B'
WHERE vluchtnummer = 5316

/*TestCase 4 --------------False*/
UPDATE Vlucht
SET gatecode = 'B'
WHERE vluchtnummer = 5317

if i run the test in this order it work fine. I got the message:

(1 row(s) affected) (1 row(s) affected) Msg 50000, Level 16, State 1, Procedure tgr_passagier_vlucht, Line 19 Cannot update, Passenger is linked to flight Msg 3609, Level 16, State 1, Line 16 The transaction ended in the trigger. The batch has been aborted.

But when i run the test cases with the order 4, 3, 2, 1 for example it says:

Msg 50000, Level 16, State 1, Procedure tgr_passagier_vlucht, Line 19 Cannot update, Passenger is linked to flight Msg 3609, Level 16, State 1, Line 6 The transaction ended in the trigger. The batch has been aborted.

The trigger got stuck by the false test cases. Why is that?

You should not have transaction logic in your trigger. If your calling code has a transaction it was cause havoc with your process. You should raise an error in your trigger like you are doing. — Sean Lange
– Sean Lange, Commented Feb 23, 2017 at 20:02

Phil Pledger · Accepted Answer · 2017-02-23 20:02:08Z

5

The problem is not in your trigger.

If you look carefully at the error message, you will see "The batch has been aborted."

Since all of these updates are in the same batch, once you get an error, the batch aborts, and no further statements proceed.

You can add a batch separator (GO) after each update to get them to continue after the error.

UPDATE Vlucht
SET gatecode = 'B'
WHERE vluchtnummer = 5317
GO

answered Feb 23, 2017 at 20:02

Phil Pledger

4762 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trigger handle multiple updates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related