My PLSQL trigger is not looping through the table

I have a group project and we are using PLSQL to make a shopping cart application. We are creating a trigger in order to cancel a shopping cart and return all of the items back to stock. As it is now, if you add multiple items to your cart, when the trigger runs, it only adds back the stock of one product, not all, so for some reason, the trigger is not looping through. Our project is due tomorrow so please help!

create or replace TRIGGER Cancel
Before delete on sc_cart
For each row

DECLARE
ws_prod_id number(3,0);
ws_item_quantity_in_cart number(7,0);

BEGIN

/*product and quantity in cart*/
select max(item_product_id), max(item_quantity_in_cart) into ws_prod_id, ws_item_quantity_in_cart
from sc_items
where item_cart_id = :old.cart_id;

update sc_product set prod_quan_avail = prod_quan_avail + ws_item_quantity_in_cart where ws_prod_id = prod_id;
update sc_product set prod_quan_sold = prod_quan_sold - ws_item_quantity_in_cart where ws_prod_id = prod_id;

delete from sc_items
where :old.cart_id = item_cart_id;

END;

We need the code to go through each row of the table and return each item in the cart back to stock.

Answer

I urge you to not put all this code in a trigger, but instead create a cancel_order procedure like this:

CREATE OR REPLACE PROCEDURE cancel_card (cart_id_in IN INTEGER)
IS
   ws_prod_id                 NUMBER (3, 0);
   ws_item_quantity_in_cart   NUMBER (7, 0);
BEGIN
   /*product and quantity in cart*/
   SELECT MAX (item_product_id), MAX (item_quantity_in_cart)
     INTO ws_prod_id, ws_item_quantity_in_cart
     FROM sc_items i
    WHERE i.item_cart_id = cancel_card.cart_id_in;

   UPDATE sc_product p
      SET prod_quan_avail = prod_quan_avail + ws_item_quantity_in_cart,
          prod_quan_sold = prod_quan_sold - ws_item_quantity_in_cart
    WHERE p.prod_id = cancel_card.ws_prod_id;

   DELETE FROM sc_items o
         WHERE i.cart_id = cancel_card.cart_id_in;

   DELETE FROM sc_cart c
         WHERE c.cart_id = cancel_card.cart_id_in;
END;

You then call this procedure as needed, but not from inside a trigger.

You should avoid SQL statements inside triggers. There is too much potential for side effects and mutating table errors.