3 Replies Latest reply: Dec 19, 2012 2:38 AM by 849238 RSS

    how to reduce quantity

    849238
      how to reduce quantity from one table due to other table
      for exple.
      i have a purchase a shampo in 20 quantity

      and then sale this shampo 2 quantity

      so i want now there should be 18 quantity of shampo in purchase table automatically

      Purchase table                    
      Product Id          
      Product Name     
      Quantity


      SALE TABLE
      Sale id
      Product name
      Quantity

      Edited by: Hussain Afridi on Dec 18, 2012 4:27 AM
        • 1. Re: how to reduce quantity
          yoonas
          Hi,

          When you are selling the item update the purchase table quantity column.

          update Purchasetable set Quantity = Quantity-sales_quanity where Product Id = xxx

          Regards
          Yoonas
          • 2. Re: how to reduce quantity
            CraigB
            Hussain Afridi,
            The best option would the have the quantity automatically decremented in the database via a trigger on the table. This eliminates the need for the developer to have to "remember" to decrement the inventory quantity. If you try this method, I recommend you create a Before Insert and Before Update trigger on the table you record your orders in. Then, when a order is written to this table, the available quantity column in your inventory table is automatically adjusted (plus or minus) the ordered quantity.

            The basic syntax for the table triggers are:
            CREATE or REPLACE TRIGGER "TRIGGER_NAME"
               BEFORE INSERT ON "TABLE_NAME"
               FOR EACH ROW
            DECLARE
               -- Declare any Trigger Variables here...
               -- -------------------------------------
            BEGIN
               -- Trigger Code here...
               -- Evaluate the :NEW so you know how much
               -- to adjust the quantity in your "inventory" table.
               -- -------------------------------------------------
            EXCEPTION
               WHEN ...
               -- Handle any exceptions here.
               -- ---------------------------
            END;
            There are numerous examples on the Internet for creating BEFORE INSERT and BEFORE UPDATE triggers.

            Hope this helps,
            Craig...
            • 3. Re: how to reduce quantity
              849238
              sir plz write a correct trigger for the reduce quantity..