Re: pre_insert trigger not working

[Todd Gould]

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!