1

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?

Improve this question
5
  • Use auto increment for the uniqe part and concatenate the unique value padding it to 7 digits with your prefix in a calculated column in a view or select statement. Commented Jan 19 at 20:05
  • Ok but we are setting the id most of the time manually via the actual barcode value, hence why we arent using an automatically incremental id on our tables to be set by default. We want to have the number '999910000001' as our base custom id incremented by 1 on insert until a unique one is achieved Commented Jan 19 at 20:17
  • Then have the auto increment in a separate generator table. Trying to generate a unique random number in a concurrent access environment is not an easy feat. Particularly not, if you have range restrictions like you do. Commented Jan 19 at 20:53
  • See answer1 and answer2 as a sample. Of course, generate the id value with predefined prefix only when NEW.id is not set. Commented Jan 20 at 4:50
  • I think because of the complexities of the required solution on the database side I will be generating and incrementing the upcID in the PHP script that looks to add records that have no upcID, doing so until it finds a valid one. Maybe I will create a stored procedure it calls first to check if the generated upcID is unique before it attempts an insert with it. There will be no occasion in my app where two of these types of records are being added at the same time. Commented Jan 20 at 12:15
Related questions
3215
Should I use the datetime or timestamp data type in MySQL?
1671
How to reset AUTO_INCREMENT in MySQL
1239
MySQL error code: 1175 during UPDATE in MySQL Workbench
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.