PL/SQL Update Trigger Updates All Rows

Question

New to working with PL/SQL and trying to create a statement level trigger that will change the 'Reorder' value to 'Yes' when the product quantity (p_qoh) is either less than 10 or less than two times the product minimum (p_min). And if that's not the case, then to change the 'Reorder' value to 'No'. My problem is that when I perform an update for a specific product, it changes the reorder value of all rows instead of the one I'm specifying. Can't seem to figure out where I'm going wrong, think I've been staring at it too long, any help is greatly appreciated.

CREATE OR REPLACE TRIGGER TRG_AlterProd
AFTER INSERT OR UPDATE OF p_qoh, p_min ON product
DECLARE
  v_p_min  product.p_min%type;
  v_p_qoh  product.p_qoh%type;
CURSOR v_cursor IS SELECT p_min, p_qoh FROM product;
BEGIN
  OPEN v_cursor;
LOOP
  FETCH v_cursor INTO v_p_min, v_p_qoh;
  EXIT WHEN v_cursor%NOTFOUND;
IF v_p_qoh < (v_p_min * 2) OR v_p_qoh < 10 THEN
 UPDATE product SET p_reorder = 'Yes';
ELSE
  UPDATE product SET p_reorder = 'No';
END IF;
END LOOP;
END;
/

Answer 1

The update command :

UPDATE product SET p_reorder = 'Yes';

updates all of your rows because you are not specifying a WHERE clause. What you can do is to retrieve the product's id (product_id) using your cursor and save it so that you would use it this way:

UPDATE product SET p_reorder = 'Yes' WHERE id = product_id;