Reference no: EM133043836

Assignment 9-1: Creating a Trigger 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 falls 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.

FIGURE 9-36 Checking stock data

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 whether a product stock request was inserted in the BB_PRODUCT_REQUEST table.

FIGURE 9-37 Updating the stock level for product 4

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 Request Is Filled

Brewbean's has a BB_PRODUCT_REQUEST table where requests to refill stock levels are 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 BS_REQFILL_TRG to handle this task, using the following steps as a guideline:

1. In SQL Developer, run the following INSERT statement to create a product request you can use in this assignment:
INSERT INTO bb_product_request (idRequest, idProduct, dtRequest, qty) VALUES (3, 5, SYSDATE, 45);
COMMIT;

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.

FIGURE 9-38 Querying the data for product 5 stock and reorder amount

4. Now update the product request to record it as fulfilled by using the UPDATE statement shown in Figure 9-39.

FIGURE 9-39 Updating the product request Chapter 9

5. Issue queries to verify that the trigger fired and the stock level of product 5 has been modified correctly. Then issue a ROLLBACK statement to undo the 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 Canceled

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's 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 hasn't been filled, the product's stock
level needs to be corrected or reduced. too. Modify the BB_REQFILL_TRG trigger 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 date in it and is now being set to NULL.

2. Issue the following DML actions to create and 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) ;
UPDATE bb product
SET stock = 86
WHERE idProduct = 5;
COMMIT;

3. Run the following UPDATE statement to test the trigger, and issue queries to verify that the data has been modified correctly.
UPDATE bb product request 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 bbreqfilltrg DISABLE;

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;