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.

Product_Details table : (Fields) Prod_id(Primary key), Product_Name, Prefix and Slno(Auto incremented)

i need to increment Prod_Id based on the product name field and prefix field (Input will be given by user). i have already concat prefix and id. but i cant achieve the sequence as i show below

    Product Name      Prefix      Prod_id
    BOOK              BO          BO_001
    FLOWER            FLOW        FLOW_001
    BOOK              BO          BO_002
    DESK              DE          DE_001
    DESK              DE          DE_002
    FLOWER            FLOW        FLOW_002

AS OF NOW I HAVE IMPLEMENTED A TRIGGER

DELIMITER $$
    CREATE TRIGGER tg_customer_details_INSERT
    BEFORE INSERT ON customer_details
    FOR EACH ROW
      BEGIN
        INSERT INTO product_details_seq VALUES (NULL);
        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;

But in this code i am getting output like book_001, flower_002,book_003, desk_004,Flower_005....so on.. i need a separate sequence for each product name. Help with a trigger code will be much appreciated.

Thanks, Acube.

share|improve this question
    
Is it or can it be a MyISAM table? –  tombom Mar 18 at 8:40
    
if it is implemented only in MyISAM table no problem..Thanks for your post. i am looking for a solutions in any point –  Acube Mar 18 at 9:05
    
tombom could you please help me in this –  Acube Mar 18 at 10:45
    
Since the table is MyISAM, Would you be interested in seeing this without a trigger (It would require a different table layout) ??? –  RolandoMySQLDBA Mar 19 at 0:39
    
Could you please help me to Concat Prefix and Prod_id for the below code in innodb using trigger to achieve the sequence in Prod_id column.. –  Acube Mar 19 at 4:58

2 Answers 2

after lot of search i added a code for this, but its showing error "#1048 - Column 'Prod_Id' cannot be null "

 DELIMITER $$
        CREATE TRIGGER tg_customer_details_INSERT
        BEFORE INSERT ON customer_details
        FOR EACH ROW
    BEGIN
      INSERT INTO product_details_seq VALUES (NULL);
        SET NEW.Slno = coalesce((select max(Slno) from product_details), 0) + 1;
      SET NEW.Prod_Id = (SELECT MAX(Prod_Id) + 1 FROM product_details WHERE Product_Name = NEW.Product_Name);
      SET NEW.Prod_Id = CONCAT((NEW.Prefix), LPAD(LAST_INSERT_ID(), 3, '0'));
    END$$
    DELIMITER;
share|improve this answer

Finally created the increment sequence and need to concat the prefix and the Prod_id. Not able to do that, your help needed in this regards...

BEGIN
 select count(*) into @Product_Name from product_details where Product_Name = NEW.Product_Name;
         select Prod_id + 1 into @Prod_id from product_details where Product_Name = NEW.Product_Name order by Prod_Id desc limit 1;
        if @Product_Name > 0 then
           set NEW.Prod_id = @Prod_id;
       else
           set NEW.Prod_id = 1;

       end if;
    SET NEW.Slno = coalesce((select max(Slno) from product_details), 0) + 1;

    end
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.