Background:

One of the important challenges to a database management system (DBMS) is to maintain valid data among all tables if some changes occur with one table. For example, in "PARTS" table, it consists of the following information:

The quantity "ONHAND" needs to be updated due to customer's order. If a customer has ordered one fax machine, the "ONHAND" will has to be reduced from 277 to 276. This issue is referred to data integrity.

In chapter 5, the customers order information is processed through "ITEMS" table. Typical data in "ITEMS" table look as follows.

This table is typically used to record customers' orders. The "QUANTITY" column represents the number of any particular parts ordered by the customer.

Now the challenge to the DBMS is to update the "PARTS" table automatically corresponding to the changes in "ITEMS" table. This challenge is effectively solved by using database triggers.

You will need to run the script "chap05.sql" before working at this project, in a similar way to what you have worked at the textbook. This action will help create consistent results.

Your task is to create a database trigger in your database schema to maintain the data integrity in "PARTS" and "ITEMS" tables. Specifically, the following business rules need to be applied:

1. If any row is deleted from "ITEMS" table, the "QUANTITY" deleted from "ITEMS" table needs to be added back to the corresponding "ONHAND" column in "PARTS" table. (25 points)
   
   
2. If any row is inserted in "ITEMS" table, the ordered "QUANTITY" in "ITEMS" table needs to be subtracted from the corresponding "ONHAND" column in "PARTS" table. (25 points)
   
   
3. If any row in "ITEMS" table is updated, the difference caused by the new value needs to be reflected in "PARTS" table. (25 points)
   
   
4. The trigger is named as "update_part". (Please use the exact spelling of the name.) (Body of the trigger: 25 points)

Tips/Hints:

1. There is more than one solution to reinforce the business rules.
   
   


2. A "BEFORE" or "AFTER" trigger is required for this type of application. I personally prefer "BEFORE" trigger. Either way should work.
   
   
3. The trigger will be executed due to any change in each row in the "ITEMS" table.
   
   
4. Test the trigger after creation for all possible scenarios. The following sequence worked well with me: delete, insert, rollback, update, rollback. The "rollback" was used to restore the original data in the table.
   
   Your script will be tested in the same sequence while it is graded.
   
   
5. To insure the trigger is fired, you may put a print statement in the trigger, such as "DBMS_OUTPUT.PUT_LINE('Trigger was fired.');". This approach is useful in learning or debugging process.
   
   
   

Submit your project through WebCT. If you need instructions on how to submit, click here.

Please submit your PL/SQL SQL script script for creating the trigger. (SQL script is in .sql format, which can be run in Oracle as SQL>@filepath\file.sql, for example, SQL>@C:\temp\mytrigger.sql.)

Convention for File Name: Please name your file as firstname_lastname_project6.sql, where firstname and lastname are your first and last name, respectively, and "_" is underscore. Seriously, the above convention will help the instructor's grading. Therefore, the instructor will be more generous when grading your project if you follow the convention.

 

Please note: Five (5) points will be taken off if the file convention is not followed.

 

At the end of this week. Please see the course schedule for exact due date.