0

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 (Prefix 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. Since this a priority one issue kindly help. All Db kings can answer.

Thanks, Acube.

acube
  • 11
  • 6
  • No body in stack overflow has solution to this problem statement, Kindly help me with the code. thanks Acube. – acube Mar 18 '14 at 06:28

1 Answers1

0

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
acube
  • 11
  • 6