Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2014
    Posts
    2

    Unanswered: how to autoincrement varchar datatype depending upon the product detail's name using

    I have a product details table, the fields in the table are *Prod_Id, Product_Name, Quantity, Prefix, 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.


    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 auto increment id BOOK_001, BOOK_002 and for all Shoe it should create a auto increment id like SHOE_001, SHOE_002 and so on...!!!

    Thanks,
    Acube.

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    What is not working? Give us some examples...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •