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.