Re: Part Number Generation in MySQL

[Barry Galbraith]

Quote

That would mean that, ideally, when I add a new material to Table 1, 2500 new PNs would automatically be created in Table 3.

That can be done with a TRIGGER. Something like this.

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `test`.`new_comp` AFTER INSERT
    ON `test`.`compounds`
    FOR EACH ROW BEGIN
	INSERT INTO part_no (actual_pn) SELECT DISTINCT CONCAT(new.comp_code,'-',s.sizes)actual_pn FROM
	compounds c JOIN sizes s
ORDER BY c.comp_code;
    END$$

DELIMITER ;

But, you haven't said how your computerPN is generated, other than "incremental". What are your rules to increment AS1000034?

Good luck,
Barry.