Updating stock levels when an order is canceled

Assignment Help PL-SQL Programming

Reference no: EM133043853

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 bbbasketstatus (idStatus, idBasket, idStage, dtStage) VALUES (hi:Ls t atuss eq 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 bbordcanceltrg DISABLE;

Reference no: EM133043853

Questions Cloud

Differences between staffing in private and public sectors : What are the differences between staffing in the private and public sectors?

Examination of executive compensation contracts : Which HR issue involves a thorough examination of executive compensation contracts, benefit plans and policies

Positive impact on firm performance : Does a move to strategic human resource management [SHRM] really have a positive impact on firm performance?

Dolvett oversees a number of individuals : Dolvett oversees a number of individuals. He decides he must stimulate conflict to move his area forward. Which response indicates why he would want to stimulat

Updating stock levels when an order is canceled : Updating Stock Levels When an Order Is Canceled - Issue queries to confirm that the trigger has modified the basket's order status and product stock levels

Progress on the inventory-handling processes : Progress on the inventory-handling processes; however. they hit a snag when a store clerk incorrectly recorded a product request as fulfilled

Organizational culture with the wrong tolerance : For this discussion, the new CEO comes to you and tells you that he is a positive person, but there are things he wants you to know that he is very

Identify related parties and related party transactions : Explain the inherent limitations which mean that auditors may not identify related parties and related party transactions

Consumer assessment of healthcare providers and systems : What are Hospital Consumer Assessment of Healthcare Providers and Systems (HCAHPS), and how do they relate to the need for quality?

User Account

All Pages