Oracle 11g PL/SQL Joan Casteel Chapter 9 Hands-On Assignments Part 9-1 to 9-4
Assignment 9-1: creating a Tigger to Handle Product Restocking
Brewbean’s has a couple of columns in the Product table to assist in inventory tracking. The REORDER column contains the stock level at which the product should be reordered. If the stock fall to this level. Brewbean’s wants the application to insert a row in the BB_PRODUCT_REQUEST table automatically to alert the ordering clerk that additional inventory is needed. Brewbean’s currently uses the reorder level amount as the quantity that should be ordered. This task can be handled by using a trigger.
1. Take out some scrap paper and a pencil. Think about the tasks the triggers needs to perform. Including checking. whether the new stock level falls below the reorder point. If so, check whether the product is already on order by viewing the product request table; if not, enter a new product request. Try to write the trigger code on paper. Even though you learn a lot by reviewing code, you improve your skills faster when you create the code on your own.
2. Open the c9reorder.txt file in the Chapter09 folder. Review this trigger code, and determine how it compares with your code.
3. In SQL Developer, create the trigger with the provided code.
4. Test the trigger with product ID 4. First, run the query shown in Figure 9-36 to verify the current stock data for this product. Notice that a sale of one more item should initiate a reorder.
5. Run the UPDATE statement shown in Figure 9-37. It should cause the trigger to fire. Notice the query to check whether the trigger fired and weather a product stock request was inserted in the BB_PRODUCT_REQUEST table.
6. Issue a ROLLBACK statement to undo these DML actions to restore data to its original state for use in later assignments.
7. Run the following statement to disable this trigger so that it doesn’t affect other projects:
ALTER TRIGGER bb_reorder_trg DISABLE;
Assignment 9-2: Updating Stock Information When a Product Is Filled
Brewbean’s has a BB_PRODUCT_REQUEST table where requests to refill stock level was inserted automatically via a trigger. After the stock level falls below the reorder level, this trigger fires and enters a request in the table. This procedure works great; however, when store clerks record that the product request has been filled by updating the table’s DTRECD and COST columns. they want the stock level in the product table to be updated. Create a trigger named BB_REQFILL_TRG to handle this task, using the following steps as a guideline:
1. In SOL Developer, run the following INSERT statement to create a product request you can use in this assignment:
INSERT INTO bb_product_requect (idRequest, idProduct, dtRequest, qty)
VALUES (3, 5, SYSDATE, 45);
2. Create the trigger (BB_REQFILL_TRG) so that it fires when a received date is entered in the BB_PRODUCT_REQUEST table. This trigger needs to modify the STOCK column in the BB_PRODUCT table to reflect the increased inventory.
3. Now test the trigger. First, query the stock and reorder data for product 5. as shown in Figure 9-38.
4. Now update the product request to record it as fulfilled by using UPDATE statement shown in figure 9-39.
5. Issue queries to verify that the trigger fired and the stock level of product 5 has been modified correctly. Then issue the ROLLBACK statement to undo modifications.
6. If you aren’t doing assignment 9-3, disable the trigger so that it doesn’t affect other assignments.
Assignment 9-3: Updating the Stock Level If a Product Fulfillment is Cancelled
The Brewbean’s developers have made progress on the inventory-handling processes; however, they hit a snag when a store clerk incorrectly recorded a product request as fulfilled. When the product request was updated to record a DTRECD value, the product stock level was updated automatically via an existing trigger, BB_REQFILL_TRG. If the clerk empties the DTRECD column to indicate that the product request has been filled, the product stock level need to be corrected or reduced, too. Modify the BB_REQFILL_TRG to solve this problem.
1. Modify the trigger code from Assignment 9-2 as needed. Add code to check whether the DTRECD column already has a data in it and is now being set to NULL.
2. Issue the following DML actions to create or update rows that you can use to test the trigger:
INSERT INTO bb_product_request (idRequest, idProduct, dtRequest, qty, dtRecd, cost)
VALUES (4, 5, SYSDATE, 45, ’15-JUN-2012′, 225);
SET stock = 86
WHERE idProduct = 5;
3. Run the following UPDATE statement to test the trigger, and issue queries to verify that the data has been modified correctly.
SET dtRecd = NULL
WHERE idRequest = 4;
4. Be sure to run the following statement to disable this trigger so that it doesn’t affect other assignments:
ALTER TRIGGER bb_reqfill_trg DISABLE;
Assignment 9-4: Updating Stock Levels When an Order ls Cancelled
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 cancelled 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 need 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 cancelled.
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 the queries to confirm that the trigger has modified the basket’s order status and product stock level 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;