how to autoincrement varchar datatype depending upon the product detail's name using trigger?

See more:

SQL

MySQL

trigger

I have a product details table, the fields in the table are Prod_Id, Product_Name, Quantity, Prefix, Slno. Now i have created trigger for varchar data type field and implemented auto increment for Prod_Id (Primary key) and for Slno. the code creates prod_Id like book_001, Shoe_002, Book_003,Flower_004,shoe_005 and so on.
Now what i need is, if the product name is RACK it should create a id RACK_001 with the start Prefix field(input will be given by user) of the product name and auto increment automatically using prefix field. 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. H

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 ;

I have concat prefix field and id field, so i get book_001 but i need a different sequence.

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

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


I need like for each product the auto increment should be separate(SHOE_001,BOOK_001,SHOE_002,FLOWER_001,BOOK_002,SHOE_003....and so on)
Kindly help with the trigger code.


Thanks,
Acube.