Exercise Oracle Trigger and Package

Scenario/Summary

This week we are going to continue to expand the functionality of our database schema by adding a couple of triggers to that will help us automate some of the processing we already have in place. Triggers can be used to automate repetitive tasks within the database, such as adjusting inventory levels based on other actions taken in the database. Once you have created and tested your triggers you will need to make some adjustments to the code in the package that was created in lab 4
For the lab you will need to create a script file containing the PL/SQL code that will address the lab steps below. Run the script file in your SQL*Plus session using the SET ECHO ON session command at the beginning to capture both the PL/SQL block code and output from Oracle after the block of code has executed. You will be running tests to verify that your triggers are working once your package has been updated. Spool your output and name your files with your last name plus lab 5 and give the file a text (txt) extension. For example, if you last name was Johnson then the file would be named johnson_lab5.txt. Submit both the spooled output files AND the script file for grading of the lab.

L A B S T E P

Step 1: Creating the first trigger

The first trigger you are going to create is to be named RENTING_MOVIE and is going to take care of the process of updating the mm_movie table to reflect a change downward in the quantity column for a movie when it is rented. Keep the following in mind:

1. The trigger needs to be a AFTER INSERT trigger on the mm_rental table. We want it to be an AFTER trigger so that in case there are any exceptions raised the trigger will not fire.
2. The trigger needs to be able to fire for each row that is inserted into the table.
3. The trigger process will only involve the update statement to lessen the quantity amount in the mm_movie table by one for the referenced movie id.

Test your code by running the script in SQL*Plus. If you have any errors then debug them and once you have a clean compile then move on to step 2.

Step 2: Creating the second trigger

The second trigger you are going to create is to be named RETURNNG_MOVIE and is going to take care of the process of updating the mm_movie table to reflect a change upward in the quantity column for a movie when it is returned. Keep the following in mind:

1. The trigger needs to be a AFTER UPDATE trigger on the mm_rental table based on the updating of the check in date in the mm_rental table.
2. The trigger needs to be able t fire for each row that is updated.
3. The trigger process will only involve the update statement to increase the quantity amount in the mm_movie table by one for referenced movie id.

Test your code by running the script in SQL*Plus. If you have any errors then debug them and once you have a clean compile then move on to step 2.

Step 3: Modifying the package code

Now we have two triggers that will handle changes in our movie rental stock each time a movie is rented or returned. Since that same process currently exists in the procedures in our package then we need to make some changes.
To keep from having repetitive processes, and thus have a scenario for generating invalid inventory data we need to take the processes out of the procedures in the package body by:

1. Remove the update statement in the MOVIE_RENT_SP that decreases the quantity by one in the mm_movie table.
2. Remove the update statement in the MOVIE_RETURN_SP that increases the quantity by one in the mm_movie table.

Recompile the package body (you do not have to recompile the package specifications). If you have any errors then debug them and once you have a clean compile then move on to step 4.

Step 4: Testing

To test your changes you will only need to test a valid movie rental and a valid movie return. The following steps will help you with the process.

1. 
   
   
   Query the mm_movie table to see all data for movie id 1.
   
   
   
   
2. 
   
   
   Execute the movie_rent_sp procedure in the package and use 1, 13, 2 for the parameters.
   
   
   
   
3. 
   
   
   Query the mm_movie table to verify the change in quantity for movie id 1.
   
   
   
   
4. 
   
   
   Query the mm_rental table to get the current rental id for movie id 1.
   
   
   
   
5. 
   
   
   Execute the movie_return_sp procedure in the package using the rental id from step 4.
   
   
   
   
6. 
   
   
   Query the mm_movie table to verify the change in quantity for movie id 1.