CODEWITHSUNDEEP
sqlmysqltriggers
Austin Hyde
Austin Hyde

Reputation: 27436

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?

Upvotes: 1

Views: 151

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562348

CREATE TRIGGER mkuuid BEFORE INSERT ON SomeTable
 FOR EACH ROW BEGIN
   SET NEW.primary_key = UUID_SHORT();
 END

Upvotes: 1

Related Questions