DBM 405 Lab 5 User Defined Database Triggers
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 your 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.
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 an 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, debug them and once you have a clean compile, move on to Step 2.
Step 2: Creating the Second Trigger
The second trigger you are going to create is to be named RETURNING_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 an 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 to 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, debug them and once you have a clean compile, 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. removing the update statement in the MOVIE_RENT_SP that decreases the quantity by one in the mm_movie table; and
2. removing 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, debug them and once you have a clean compile, 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, and 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.
This concludes the Lab for Week 5.
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more