Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
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 – Jack Douglas Mar 16 '14 at 21:20
Use two tables? – Jack Douglas Mar 17 '14 at 7:10
  • 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).

share|improve this answer
up vote 1 down vote accepted

This will work

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

Thanks Acube.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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