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

Question

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. Brewbean’s
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 wasn’t 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 item’s 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 basket’s order status and product
stock levels correctly.

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

ALTER TRIGGER bb_ordcancel_trg DISABLE;

Explanation / Answer

DECLARE 2 V_Empno NUMBER(4) := &EnterEmpNo; 3 TYPE EmpRecordType --Record Type Creation 4 IS RECORD 5 ( 6 V_Ename VARCHAR2(30), 7 V_Deptno NUMBER(2), 8 V_Job VARCHAR2(30) 9 ); 10 EmpRecord EmpRecordType; --Record Type Instance 11 BEGIN 12 SELECT 13 Ename, Deptno, Job 14 INTO 15 EmpRecord.V_Ename, EmpRecord.V_Deptno, EmpRecord.V_Job 16 FROM Emp 17 WHERE Empno = V_Empno; 18 DBMS_OUTPUT.PUT_LINE('The Details of Employee Number '||V_Empno||' Requested by You Are ...'); 19 DBMS_OUTPUT.PUT_LINE('The Employee Name : '||EmpRecord.V_Ename); 20 DBMS_OUTPUT.PUT_LINE('The Department Number : '||EmpRecord.V_Deptno); 21 DBMS_OUTPUT.PUT_LINE('The Designation : '||EmpRecord.V_Job); 22 END; 23 / Enter value for enterempno: 7654 The Details of Employee Number 7654 Requested by You Are ... The Employee Name : MARTIN The Department Number : 30 The Designation : SALESMAN PL/SQL procedure successfully completed.

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.