MySQL Forums
Forum List  »  Triggers

Re: pre_insert trigger not working
Posted by: Todd Gould
Date: June 07, 2019 06:18AM

Thank you for your help Peter. However, I'm still not sure I fully understand what is going on here. In order to investigate this further, I have have tried each of the following 4 variations of the trigger (showing only the logic within BEGIN/END for brevity):

BEGIN
IF (NEW.id IS NULL) THEN
SET NEW.id = ma.uuid_to_bin(UUID());
END IF;
END

BEGIN
IF isnull(new.id) THEN
SET NEW.id = ma.uuid_to_bin(UUID());
END IF;
END

BEGIN
SET NEW.id = COALESCE(NEW.id, ma.uuid_to_bin(UUID()));
END

BEGIN
IF (ISNULL(new.id) OR new.id+0 = 0) THEN
SET NEW.id = ma.uuid_to_bin(UUID());
END IF;
END

Of these 4 tests, only the last one is successful in populating the otherwise unprovided NEW.id column value.

From this, I am concluding that both the "NEW.id IS NULL" (as in my original version) as well as the "ISNULL(new.id)" as per your suggestion are ineffective at determining that that row does not have an id column value specified. Do you agree? Either way, why would this be? FYI, the id column is declared as a NOT NULL BINARY(16) if that matters somehow. Please note that is does NOT have a default value declared on it.

Therefore, I am concluding that only the "new.id+0 = 0" check is what is allowing the otherwise empty value to be detected - and therefore triggering the assignment.

Do BINARY columns somehow default their value (to 0 in this case) even when not declared to do so?

I'd appreciate any assistance as I am struggling to understand why the null checks are not effective here?

Thanks again!

Options: ReplyQuote


Subject
Views
Written By
Posted
462
June 06, 2019 01:03PM
Re: pre_insert trigger not working
177
June 07, 2019 06:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.