Structured Query Language – Assignment 2 – Semester 1, 2011
Due: 25-MAY (Week 13)
For this assignment, you will need access to an Oracle database user account (such as HR or oraNN). SQL428-A2-201110-setup.sql
Your Tasks: 1. Triggers
A client needs to manage: creating customers, deleting customers and other administration of the customers table. We also need to keep an audit log of changes to the customers table in a table called cust_audit; you will be provided with code to create the audit log table. Download and open the file 428-A2stub.SQL. Save this file with a different name that includes your CIT number: 428-A2CIT012345.SQL. In the space provided in the file, insert code to create triggers to solve the following challenges that will protect the integrity of the database: 1.1. Add code to convert Email addresses to lowercase whenever a record is inserted or updated in the customers table. 1.2. When a customer record is added, changed or removed, log details of the change in the archive table (cust_audit). The archive table is to contain the Customer ID, Last Name, Reward Points as well as the DML action (Insert/Update/Delete), the date & time removed and the name of the person making the change (use the USER function). When the DML action is Update, log the old Last Name and RewardPoints, not the new values. 1.3. Code is provided in the stub to test your code.
2. Packages
Create a package named cust_admin to manage adding customers, deleting customers and other administration of the customers table. There is space provided in the stub file for a package to solve the following challenges:
2.1. Create a public variable called number_added that is initialized to zero the first time the package is run. 2.2. Create a public function called add_customer that takes the following parameters: last_name, first_name, email, phone_number, rewardpoints. Return a customer_id that is generated from the customers_seq sequence. Use the values passed in together with the sequence to insert a record in the customers table. Output to the screen the number of customers added. Increment the number_added counter every time add_customer is called. 2.3. Create a public procedure called delete_customer that accepts one parameter – either an customer_id or
