Oracle 11g PL/SQL Joan Casteel Chapter 9 Hands-On Assignments Part 9-5 to 9-8
Assignment 9-5: Processing Discount
Brewbean’s is offering a new discount for return shoppers. Every fifth completed order gets a 10% discount. The count of orders for a shopper is placed in a packaged variable named pv_disc_num during the ordering process. The count needs to be tested at checkout to determine whether a discount should be applied. Create a trigger named BB_DISCOUNT_TRG so that when an order is confirmed (the ORDERPLACED value is changed from 0 to 1), the pv_disc_num packaged variable is checked. If it’s equal to 5, set a second variable named pv_disc_txt to Y. This variable is used in calculating the order summary so that a discount is applied, if necessary.
create a package specification named DISC_PKG containing the necessary packaged variables. Use an anonymous block to initialize the packaged variables to use for testing the trigger. Test the trigger with the following UPDATE statement:
SET orderplaced = 1
WHERE idBasket = 13;
If you need to test the trigger multiple times, simply reset the ORDERPLACED column to 0 for basket 13 and then run the UPDATE again. Also, disable this trigger when you’re finished so that it doesn’t affect other assignments.
Assignment 9-6: Use Triggers to Maintain Referential Integrity
At times, Brewbean’s has changed the ID numbers for existing products. In the past, developers had to add a new product row with the new id to the BB_PRODUCT table, modify all the corresponding BB_BASKETITEM and BB_PRODUCTOPTION table rows, and then delete the original product row. Can a trigger be developed to avoid all these steps and handle the update of the BB_BASKETITEM and BB_PRODUCTOPTION table rows automatically for a given change in product ID? If so, create the trigger and test by issuing an update statement, which changes the IDPRODUCT of 7 to 22. Do a rollback to return the data back to its original state. Also, disable the new trigger after you have completed the assignment.
Assignment 9-7: Updating Summary Data Tables
The Brewbean’s owner uses several summary sales data tables every day to monitor business activity. The BB_SALES_SUM table holds the product ID, total sales in dollars, and total quantity sold for each product. A trigger is needed so that every time an order is confirmed or the ORDERPLACED column is updated to 1, the BB_SALES_SUM table is updated accordingly. Create a trigger named BB_SALESUM_TRG that perform this task. Before testing, reset the ORDERPLACED column to 0 for basket 3, as shown in the following code, and use this basket to test the trigger.
SET orderplaced = 0
WHERE idBasket = 3;
Notice that the BB_SALES_SUM table already contains some data. Test the trigger with the following UPDATE statement, and confirm that the trigger is working correctly:
SET orderplaced = 1
WHERE idBasket = 3;
Do a rollback and disable the trigger when you’re finished so it doesn’t affect the other assignments.
Assignment 9-8: Maintaining an Audit Trail of Product Table Changes
The accuracy of product table data is critical and the Brewbean’s. owner wants to have an audit file that containing information on all DML activity on the BB_PRODUCT table. This information should indicate the ID of the user performing a DML action, the date, the original values of the changed row, and the new values. This audit table needs to track specific columns of concern, including PRODUCTNAME, PRICE, SALESTART, SALEEND, and SALEPRICE. Create a table named BB_PRODCHG_AUDIT that can hold the relevant data. Then create a trigger named BB_AUDIT_TRG that fires an update to this table whenever one of the specified columns in the BB_PRODUCT table is changed.
Be sure to issue the following command. If you created the SALES_DATE_TRG trigger in the chapter, it conflicts with this assignment.
ALTER TRIGGER SALES_DATE_TRG DISABLE;
Use the following update statement to test your trigger:
SET salestart = ’05-MAY-03′, saleend = ’12-MAY-03′, saleprice = 9
WHERE idproduct = 10;
When you have finished, do a rollback and disable the trigger so that it does not affect other assignments.