how to autoincrement varchar datatype depending upon the product details name using trigger

Question

I have a product details table, the fields in the table are *Prod_Id, Product_Name, Quantity, Slno*. Now i have created trigger for varchar data type field and implemented auto increment for Prod_Id (Primary key) and for Slno. The output will be

Prod_Id -->>> PROD0001, PROD0002 and so on. Now if the product name is RACK it should create a id RACK_001 with the start two letters of the product name and auto increment automatically. For all Product name it should create accordingly,how to do this.

As of now i am using a trigger for auto increment in varchar data type. Help with the trigger code.

DELIMITER $$
  CREATE TRIGGER tg_product_details_INSERT
  BEFORE INSERT ON product_details
  FOR EACH ROW
   BEGIN
     INSERT INTO product_details_seq VALUES (NULL);
     SET NEW.Created_Date = NOW();
     SET NEW.Submitted_Date = NOW();
     SET NEW.Slno = coalesce((select max(Slno) from product_details), 0) + 1;
     SET NEW.Prod_id = CONCAT((NEW.Prefix), LPAD(LAST_INSERT_ID(), 3, '0'));
   END
DELIMITER ;

product name -->> BOOK, SHOE, DRESS, FURNITURE

for all book it should create a autoincrement id BOOK_001, BOOK_002 and for all Shoe it should create a autoincrement id like SHOE_001, SHOE_002 and so on...!!!

Thanks, Acube.

Answer 1

• Store the "BO" and "1" as two columns.

• INDEX that pair of columns so that you can find the max

• Construct 'BO_002' when fetching the row by doing CONCAT(prefix, '_', seq). (And use ZEROFILL instead of LPAD, etc).

Answer 2

This will work

SET NEW.Prod_id = CONCAT('PROD', LPAD(LAST_INSERT_ID(), 3, '0'));
SET NEW.Prod_id = CONCAT(SUBSTRING(NEW.Product_Name,1,2), NEW.Prod_id);

Thanks Acube.