Why part of a trigger is executed before the rest of it?

Question

I have a trigger like this:

DECLARE v1 VARCHAR(4);
and so on declaration
...

IF LENGTH(new.ID) < 11 OR new.ID= '' THEN         
        RAISERROR 23004 'Too short!';
ENDIF;

SET v1 = substring(new.ID,1,3);
and more action on v1...

What I want to say, is that the set part is being executed before the if and I don't know why. Do You have an idea how to fix it?

Thanks in advance

Answer 1

There is no way later code is executed before earlier code as coded in the trigger.

Your trigger is being executed twice.

The first execution truncates the id to 3 chars.
The second execution explodes due to the id being too short (because of the first execution).

You need to figure out why it's being called twice.

---

Possible fix, if you can't prevent it from being called twice, could be to allow a length of 3:

IF LENGTH(new.ID) < 11 AND LENGTH(new.ID) != 3 THEN         
    RAISERROR 23004 'Too short!';
ENDIF;

Note that testing from blank is covered by testing for length less than 11.