Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

share|improve this question
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 –  Jack Douglas Mar 16 at 21:20
    
then help me how to auto increment Slno and Prod_Id in same table, Prod_Id should create a id relevant to the product name like if it is book then BOOK_0001, BOOK_0002 and so on.. –  Acube Mar 17 at 3:19
    
Use two tables? –  Jack Douglas Mar 17 at 7:10

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

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.