DBM 405 LAB 5: User Defined Database Triggers

Type: Instant Download

Format: Notepad

Version: Current 2013-14 (Click on tutorial image to view snapshot)

Frequently Asked Questions

What is included in the full course package? The entire course study guide includes the output file homework solution in notepad for DBM 405 Lab 5: User Defined Database Triggers (bachelors program)

Your tutorial will include these files:

• DBM 405 Lab 5 outputfile (Notepad document)

What if I find a question is missing? Just shoot us an email or contact us via live chat. Our expert DBM tutors will add the answers to the study guide within 1 to 3 days. You will receive a free update to the study guide with the answers you need. 

Will this help me with DBM 405 problems? Yes, this guide is designed to help students get through the DBM 405 Lab 5 exercise with ease.

DBM 405 LAB 5: User Defined Database Triggers:

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.

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.

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.

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.