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.

  CREATE TRIGGER tg_product_details_INSERT
  BEFORE INSERT ON product_details
     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'));

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.

  • 1
    Are you aware that the select max(Slno) from product_details subquery will cause all your inserts to serialize? This is usually a terrible idea, which is why most RDBMSs provide some sort of auto_increment feature Commented Mar 16, 2014 at 21:20
  • Use two tables? Commented Mar 17, 2014 at 7:10

2 Answers 2

  • 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).


This will work

SET NEW.Prod_id = CONCAT(SUBSTRING(NEW.Product_Name,1,2), NEW.Prod_id);

Thanks Acube.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.