Stock Update trigeer

 

--This trigger will update the stock table when the Purchase will happen.

DROP TRIGGER REST.STOCK_INS_UPD_3;

CREATE OR REPLACE TRIGGER REST.stock_ins_upd_3
   AFTER INSERT OR UPDATE
   ON REST.PURCHASE_DETAIL
   REFERENCING OLD AS old NEW AS new
   FOR EACH ROW
DECLARE
   v                  NUMBER;
   v_product_id       NUMBER;
   v_product_id_old   NUMBER;
BEGIN
   SELECT NVL (MAX (stock_id), 0) + 1 INTO v FROM stock;

   SELECT COUNT (product_id)
     INTO v_product_id
     FROM stock where product_id=:new.product_id;


   IF v_product_id <= 0
   THEN
      INSERT INTO stock (STOCK_ID, PRODUCT_ID, STOCK_QTY)
         SELECT v, :new.PRODUCT_ID,                       --:new.PRODUCT_NAME,
                                   :new.PRODUCT_QUANTITY FROM DUAL;
   -- COMMIT;
   ELSE
      

      UPDATE stock
         SET STOCK_QTY = STOCK_QTY+ :new.PRODUCT_QUANTITY
       WHERE product_id = :new.product_id;

   END IF;
END;
/