So I have a project where I am indexing inventory items by upc barcode values. My inventory table has the primary unique column upcID, the column is not auto-incremented. On instances where I scan a barcode to add to the table it has the barcode gs1 number it uses as the id. We would like to add items that have no barcode, generating a unique 12 digit number starting in '99991' as the fake manufacturer part of the gs1 upc format. The generated id should add a unique portion using a further 7 digits, so we end up with for example '999910000001'. Once the id for the class of inventory item is in the table, I can print barcode labels and affix them to those items.

So Ive been looking at how to do a before insert trigger, which would generate a code, if the id of the insert is NULL, check it against the rows in the table, and increment it as needed until it was unique, then do the insert with it as the id. Theoretically something like this (please keep in mind it's been a long time since I've had to write a mysql trigger of procedure)

BEGIN
DECLARE isupcunique TINYINT;
DECLARE generatedupc DOUBLE;
SET isupcunique=0;
SET generatedupc=999910000000;
IF new.upcID IS NULL
WHILE isupcunique = 0 

'see if its there
IF (select upcID from thetable where upcID=generatedupc limit 1) THEN
'do nothing but add to generatedupc 
SET generatedupc+=1;
ELSE
SET new.upcID=generatedupc;
SET isupcunique=1
END IF;

END WHILE;
END IF;
END;

I realize my code isn't close I don't think IF's exist in triggers... Just the general gist of what I think I'd need to do.

Any ideas for me?