Oracle PL/SQL Assignment 9-4: Updating Stock Levels When an Order Is Canceled

At times, customers make mistakes in submitting orders and call to cancel an order. Brewbeans wants to create a trigger that automatically updates the stock level of all products associated with a canceled order and updates the ORDERPLACED column of the BB_BASKET table to zero, reflecting that the order wasnt completed. Create a trigger named BB_ORDCANCEL_TRG to perform this task, taking into account the following points:

The trigger needs to fire when a new status record is added to the BB_BASKETSTATUS table and when the IDSTAGE column is set to 4, which indicates an order has been canceled.

Each basket can contain multiple items in the BB_BASKETITEM table, so a CURSOR FOR loop might be a suitable mechanism for updating each items stock level.

Keep in mind that coffee can be ordered in half or whole pounds.

Use basket 6, which contains two items, for testing.

1. Run this INSERT statement to test the trigger:

INSERT INTO bb_basketstatus (idStatus, idBasket, idStage, dtStage) VALUES (bb_status_seq.NEXTVAL, 6, 4, SYSDATE);

2. Issue queries to confirm that the trigger has modified the baskets order status and product stock levels correctly.

3. Be sure to run the following statement to disable this trigger so that it doesnt affect other assignments:

ALTER TRIGGER bb_ordcancel_trg DISABLE;