Calculate non-standard auto-increment automatically
The table I am working with does not have a standard auto-increment field to use as a primary key, so I need to come up with a way to automatically calculate the value that should be used in the field.
My first thought was to create a trigger to happen AFTER INSERT, however, as far as I can tell, there's no easy way to reference the row that was just inserted. I could do something like
UPDATE `table` SET `reference_number` = (SELECT ....) WHERE `reference_number` IS NULL
but because reference_number is a PRIMARY KEY, it cannot be null. (Does that mean it would be an empty string ''?)
Is there a better way to do this?
CREATE TRIGGER mkuuid BEFORE INSERT ON SomeTable FOR EACH ROW BEGIN SET NEW.primary_key = UUID_SHORT(); END