I chose to update my application and database to use UUIDs as primary key. As I'm trying to achieve this with as few changes to my application as possible, I thought about using mysql triggers.
explains how to generate UUIDs on insert. But using it as a key attribute, I need to handle the unlikely case of collision. Is it possible to react to constraint violations by retrying the insert with a new UUID using mysql triggers?
To answer the question that was asked ...
Q: Is it possible to react to constraint violations by retrying the insert with a new UUID using mysql triggers?
A: No, it's not possible to "retry" a MySQL
INSERT from within a
BEFORE INSERT or an
AFTER INSERT trigger. Not within a trigger. (Note that this restriction is independent of storing UUID values in a column, or using that column as a primary key.)
INSERT operation throws a duplicate key exception, there's no way to catch that in an
AFTER INSERT trigger.