Re: pre_insert trigger not working
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!
Subject
Views
Written By
Posted
1546
June 06, 2019 01:03PM
632
June 06, 2019 03:17PM
704
June 06, 2019 03:22PM
681
June 06, 2019 04:15PM
629
June 06, 2019 04:54PM
604
June 06, 2019 05:10PM
Re: pre_insert trigger not working
655
June 07, 2019 06:18AM
636
June 07, 2019 10:36AM
611
June 07, 2019 10:56AM
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.