title: MYSQL trigger to create id
body: <p>I'm trying to make a id from 2 fields</p>
  
  <p><strong>producten_categorien</strong> is filled with the follow data:</p>
  
  <pre>productcat_id    productcat_name    productcat_count     productcat_code
  1                Sound              1                    SOU
  2                Light              1                    LIG
  3                Vision             1                    VIS
  </pre>
  
  <p>I want to make an id like <code>VIS0001 (productcat_code + productcat_count)</code></p>
  
  <p>I always get VIS000 back could you guys help me?</p>
  
  <h2>Below you see my code:</h2>
  
  <pre>
  USE `jarent`;
  DELIMITER $$
  CREATE TRIGGER `klanten_BINS`
  BEFORE INSERT ON `producten`
  FOR EACH ROW
  BEGIN
    DECLARE productcat VARCHAR(45);
    DECLARE productcatcount INT unsigned;
  
    SELECT productcat_code INTO productcat FROM producten_categorien WHERE productcat_id = NEW.product_catid;
    SELECT productcat_count INTO productcatcount FROM producten_categorien WHERE productcat_id = NEW.product_catid;
  
    SET productcatcount = productcatcount + 1;
  
    UPDATE producten_categorien SET productcat_count = productcatcount WHERE productcat_id = NEW.product_catid;
  
    SET NEW.product_id = CONCAT(productcat, LPAD(productcatcount, 4, '0'));
  END
  </pre>