Chapter 7: Database Integrity and Transactions All Powder Board and Ski

1. Designing & Building Business Applications Oracle 9i Jerry Post Chapter 7: Database Integrity and Transactions All Powder Board and Ski

2. Compute Sales Tax Sales Tax From Figure 6.29

3. Action Action Use SQL to create the ComputeSalesTax function within a new Taxes package Test the function with an SQL statement

4. Create Oracle Package and Function The slash is required to separate the commands Package definition Package body Function definition

5. Test the Function in SQL Dual is a tiny system table used for testing because it has one column and one row Package name Function name Correct result: 7 percent of 500

6. Action Action Edit the Sale form Right click the SalesTax box and select the PRE-TEXT-ITEM trigger Add the code: :Sale.SalesTax := Taxes.ComputeSalesTax(:Sale.Subtotal); Run the form Click on the SalesTax box to test the calculation

7. Add Event Code to the Sales Form Choose the PRE-TEXT-ITEM event Call the new function

8. Debugging Debug/Debug Module Debug/Step Into Double click to set breakpoint See form and variable values with Debug/Debug Windows

9. Inventory Database Triggers Sale(SaleID, CustomerID, EmployeeID, SaleDate, …) SaleItem(SaleID, SKU, QuantitySold, SalePrice) Inventory(SKU, QuantityOnHand, …) If a new item is sold, subtract QuantitySold from QuantityOnHand Complications: changes to the data A SaleItem is revoked, the SaleItem row deleted The QuantitySold is changed The SKU is changed Sample values: SaleID=3000 SKU=500000 or 500010 CustomerID=582 EmployeeID=5

10. Action Action Insert a new row into the Sale table with a SaleID of 3000, CustomerID of 582 and EmployeeID of 5 Create the AFTER INSERT trigger for the SaleItem table Insert a new row into the SaleItem table (3000, 500000, 1, 100) Check the value of QuantityOnHand in the Inventory table for SKU=500000 and ensure it was decreased from 10 to 9

11. Database Event Triggers DELETE BEFORE INSERT AFTER UPDATE CREATE OR REPLACE TRIGGER NewSaleQOH AFTER INSERT ON SaleItem FOR EACH ROW BEGIN UPDATE INVENTORY SET QuantityOnHand = QuantityOnHand - :NEW.QuantitySold WHERE SKU = :NEW.SKU; END; New/inserted value

12. Setup Example INSERT INTO Sale (SaleID, CustomerID, EmployeeID) VALUES (3000, 582, 5); SELECT SKU, QuantityOnHand FROM Inventory WHERE SKU=500000; INSERT INTO SaleItem (SaleID, SKU, QuantitySold, SalePrice) VALUES (3000, 500000, 1, 100); Check the QuantityOnHand before and after the INSERT

13. Action Action Delete the SaleItem row (SaleID=3000 And SKU=500000) Check the quantity on hand Add the AFTER DELETE trigger Insert the SaleItem row again Check the quantity on hand Delete the SaleItem row Check the quantity on hand

14. Potential Problem: Delete Row DELETE FROM SaleItem WHERE SaleID=3000 And SKU=500000; Check the QuantityOnHand before and after the DELETE The value does not change! CREATE OR REPLACE TRIGGER DelSaleQOH AFTER DELETE ON SaleItem FOR EACH ROW BEGIN UPDATE INVENTORY SET QuantityOnHand = QuantityOnHand + :OLD.QuantitySold WHERE SKU = :OLD.SKU; END; Restore the deleted quantity

15. Problems • What if the clerk entered the wrong value and should have entered 1 instead of 2 units? • Test it, and the code subtracts 1 from the QOH, leaving 7. • You need to add the original 2 units back. QuantityOnHand = QuantityOnHand – QuantitySold + OldQuantity

16. Action Action Add the ON UPDATE trigger Check the quantity on hand Issue an update to change the QuantitySold in the SaleItem table Check the quantity on hand

17. Problem: Change the Quantity Test it UPDATE SaleITem SET QuantitySold = 2 WHERE SaleID=3000 And SKU=500000; CREATE or REPLACE TRIGGER ChangeSaleQOH AFTER UPDATE ON SaleItem FOR EACH ROW BEGIN UPDATE Inventory SET QuantityOnHand = QuantityOnHand + :OLD.QuantitySold - :NEW.QuantitySold WHERE SKU = :OLD.SKU; END; Add back the old quantity (1) and subtract the new value (2) Test it again

18. Problem: Change the SKU SELECT SKU, QuantityOnHand FROM Inventory WHERE SKU=500000 Or SKU=500010; Test it by changing both QuantitySold and SKU UPDATE SaleITem SET QuantitySold = 3, SKU = 500010 WHERE SaleID=3000 And SKU=500000; SELECT SKU, QuantityOnHand FROM Inventory WHERE SKU=500000 Or SKU=500010;

19. Action Action Create the full ON UPDATE trigger Check the quantity on hand Change the QuantitySold and SKU (to 500010) in the SaleItem row Check the quantity on hand for SKU 500000 and 500010

20. Trigger to Handle SKU Changes CREATE or REPLACE TRIGGER ChangeSaleQOH AFTER UPDATE ON SaleItem FOR EACH ROW BEGIN IF (:OLD.SKU = :NEW.SKU) THEN UPDATE Inventory SET QuantityOnHand = QuantityOnHand + :OLD.QuantitySold - :NEW.QuantitySold WHERE SKU = :OLD.SKU; ELSE UPDATE Inventory SET QuantityOnHand = QuantityOnHand + :OLD.QuantitySold WHERE SKU = :OLD.SKU; UPDATE Inventory SET QuantityOnHand = QuantityOnHand - :NEW.QuantitySold WHERE SKU = :NEW.SKU; END IF; END; Test it again

21. Transactions for Discounts New table

22. Rental Form Button to open discount form

23. Action Action Create the Rental form Create the Rental Discount form with no data Add the text boxes and button Save the form Add a button to the Rental form that opens the Discount form

24. Rental Discount Form RentID and Amount are determined by the Rental form Date default value is set to $$DATETIME$$ This is an unbound form built from design view with no Data Block source

25. Action Action Edit the Rental Discount form Add the specified code to the WHEN-BUTTON-PRESSED trigger Test the forms

26. Rental Form Code: Discount Button Rental Form, Button to open Discount form Trigger event: WHEN-BUTTON-PRESS :global.RentID := :Rental.RentID; :global.Amount := :Rental.SubCharges; Call_Form('D:\Students\AllPowder\GiveRentDiscount'); Save the RentID and total repair charges into global variables that can be retrieved by the discount form when it starts.

27. Discount Form Triggers Form: WHEN-NEW-FORM-INSTANCE :RentalID := :global.RentID; :Amount := :global.Amount; Button: WHEN-BUTTON-PRESSED UPDATE RentItem SET RepairCharges=0 WHERE RentID = :RentalID; INSERT INTO RentalDiscount(RentID, DiscountDate, DiscountAmount, Reason) VALUES (:RentalID, :TransDate, :Amount, :Reason); Commit; :txtMessage := 'Changes recorded.';

28. Transaction Code WHEN-BUTTON-PRESSED BEGIN UPDATE RentItem SET RepairCharges=0 WHERE RentID = :RentalID; INSERT INTO RentalDiscount(RentID, DiscountDate, DiscountAmount, Reason) VALUES (:RentalID, :TransDate, :Amount, :Reason); Commit; :txtMessage := 'Changes recorded.'; EXCEPTION WHEN OTHERS THEN Rollback; END; If something goes wrong, cancel the first update

29. Action Action Create a new query Tables: Sale and SaleItem Create column TO_CHAR(SaleDate, ‘ww’) AS SaleWeek Create column QuantitySold*SalePrice AS Value Sum the Value column by week

30. Query for Cursor: Weekly Sales CREATE VIEW WeeklySales AS SELECT TO_CHAR(SaleDate, 'ww') AS SalesWeek, Sum(SalePrice*QuantitySold) AS Value FROM Sale INNER JOIN SaleItem ON Sale.SaleID=SaleItem.SaleID WHERE SaleDate Is Not Null GROUP BY TO_CHAR(SaleDate, 'ww');

31. Action Action Create the SalesAnalysis package with the AvgPercentWeeklyChange function Use SQL to call the function: SELECT SalesAnalysis.AvgPercentWeeklyChange FROM dual

32. Set up Package to Compute Average CREATE OR REPLACE PACKAGE SalesAnalysis AS FUNCTION AvgPercentWeeklyChange return REAL; END SalesAnalysis; / CREATE or REPLACE PACKAGE BODY SalesAnalysis AS FUNCTION AvgPercentWeeklyChange return REAL IS CURSOR c1 IS SELECT SalesWeek, Value FROM WeeklySales; Avg1 REAL; N Integer; PriorValue WeeklySales.Value%TYPE; Define the SELECT statement for the cursor to trace through Create variable to hold the value from the previous row with the same data type as the column in the table

33. Code to Compute Average Change BEGIN Avg1 := 0; N := 0; PriorValue := -1; FOR recSales in c1 LOOP IF PriorValue > 0 THEN Avg1 := Avg1 + (recSales.Value - PriorValue)/PriorValue; N := N + 1; END IF; PriorValue := recSales.Value; END LOOP; RETURN (Avg1/N); END AvgPercentWeeklyChange; END SalesAnalysis; / Skip the first week because there is no prior value Compute the percent change and keep a running total Save the current row value and move to the next row

34. Action Action Create a sequence to generate key values for the Sale table beginning with a value of 10000 Create a trigger for the Sale table that generates a new sequence value and uses it for the SaleID Test the process by inserting a row into the Sale table without using a SaleID

35. A Sequence for the Sale Table CREATE SEQUENCE seq_Sale INCREMENT BY 1 START WITH 10000 NOMAXVALUE NOCYCLE CACHE 10; Start at a high number to avoid collisions with existing data

36. Trigger to Generate Key Automatically generate and use a new key value for SaleID whenever a row is added to the Sale table CREATE OR REPLACE TRIGGER GenKeyForSale BEFORE INSERT ON Sale FOR EACH ROW BEGIN SELECT seq_Sale.NEXTVAL INTO :NEW.SaleID FROM dual; END; / Generate next value Use it as the new SaleID

37. Test the Key Generator Insert a row into Sale without specifying a SaleID INSERT INTO Sale (CustomerID, EmployeeID) VALUES (582, 5); SELECT seq_Sale.CURRVAL FROM dual; SELECT * FROM Sale WHERE SaleID=10000; See what key value was generated Retrieve the sales data to ensure the row was created

38. Keys: Create Sales and Items (barcode) Customer ID card is scanned Create new sale Get SaleID Scan an item Save SaleID, SKU, Quantity Save sale item, update QOH and totals Repeat until done (payment key)

39. Action Action Create a new form with no data Add boxes for CustomerID, EmployeeID, SKU, and txtSaleID as the generated key Create a command button and add the indicated code Test the form Place a breakpoint at the top of the code and step through the code

40. Generate Sale Form IDs and SKU would be scanned, but to test code, set default values

41. Action Action Create a new form with no data Add a text box and LOV for Customer ID Add a text box to enter a new ZIP Code Create a button and add the indicated code for it Test the form Use the wizards to create a second form that displays CustomerID and ZIP Code in a tabular list

42. Concurrency and Lock Test Form

43. PL/SQL to Change ZIP Code BEGIN UPDATE Customer SET ZIP = :ZIPCode WHERE CustomerID = :CustomerID; Commit; END;

44. Customer List Form

45. Action Action Open both forms so they are both visible on the screen In the list form, change the last digit but do not save the changes In the test form, enter the same Customer ID and a different ZIP code, then click the save button You should see the test form go into wait mode (hourglass cursor) Return to the list form and save the changes Determine the final value of the ZIP code

46. Read Consistent Lock on the Form Open both forms and use the testing form to change the ZIP code for CustomerID=1 Return here and try to change the ZIP code Error message that value was changed

47. Action Action Execute the query in the list form to obtain current database values Alter the ZIP code in the testing form and save the new value In the list form, try to change the matching ZIP code. You should receive an error message

48. Stronger Lock on the Test Form Name the concurrency error DECLARE concurrency_hit EXCEPTION; PRAGMA EXCEPTION_INIT(concurrency_hit, -8177); BEGIN SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; UPDATE Customer SET ZIP = :ZIPCode WHERE CustomerID = :CustomerID; Commit; EXCEPTION WHEN concurrency_hit THEN message ('Data has been changed by another process.'); WHEN OTHERS THEN message ('Unknown error.'); END; Set strongest isolation level Catch error raised by this update interrupting another one Notify user who can decide to try again or exit

49. Action Action Change the code in the testing form Save and recompile the form Re-open the list and testing forms Repeat the first experiment by changing one digit of the ZIP code on the list form Enter a new ZIP code on the test form and click the button to save it You should receive an error message Return to the list form and save the changes Determine the current value of the ZIP code in the data table

50. Serializable Isolation Level The change is not made and the error is trapped because the row is locked