admin管理员组文章数量:1319465
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?
本文标签: triggersMysql generate uniqueid when id is NULLStack Overflow
版权声明:本文标题:triggers - Mysql generate uniqueid when id is NULL - Stack Overflow 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.betaflare.com/web/1742061263a2418600.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论