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 class note: 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 class note. 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.  Ten points will be taken if the spelling is incorrect. (Body of the trigger: 25 points)

Tips/Hints:

1. You may have to study the trigger section of "Class Note: Chapter 5" to develop a full understanidng on triggers before attempting this project. If you follow the clas note, this project is straightforward. Of course, you are welcome to search the Internet for a solution.
2. There is more than one solution for reinforcing the business rules.
3. A "BEFORE" or "AFTER" trigger is required for this type of application. I personally prefer "BEFORE" trigger. Either way should work.
4. The trigger will be executed due to any change in each row in the "ITEMS" table.
5. Test the trigger after creation for all possible scenarios. The following sequence worked well with me: delete, rollback, 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. For example, the following is a segment of SQL script that I will use to test your trigger:
   
   
   • --Test the DELETE trigger
     Prompt Delete o_id = 14 from items table to test DELETE trigger.
     DELETE FROM items
     WHERE o_id=14;
   • PAUSE | Press RETURN to continue or CTRL-C to abort.
   • prompt Query from items table: The o_id = 14 should be deleted from the table.
   • select * from items
     where o_id>12;
   • PAUSE | Press RETURN to continue or CTRL-C to abort.
   • Prompt Query from parts table.
     Prompt
     Prompt If the DELETE trigger works properly,
     Prompt the ONHAND for "Copy Machine" should be--144.
   • PAUSE | Press RETURN to continue or CTRL-C to abort.
     prompt
     select * from parts
     where id=2;
     
   
   
   
   
6. 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.

 

 

Create a single SQL script for the project (10 points off if not in one file).  Please test your script before submission.  Twenty (20) points will be taken if the script is not running (even with minor syntax error) while the instructor tests on his system.  Please note the importance of this practice as a database professional in the field.  This practice will be reinforced for all remaining projects in the course. 

Submit your project through D2L. 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_project5.sql, where firstname and lastname are your first and last name, respectively, and "_" is underscore.

Please note that five (5) points will be taken for all projects this semester if the file naming convention is not followed.

 

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