Database Tutorial

Course Tutorial Site

Site Admin

CIT325: Lab 11 Instructions

without comments

The lab builds on work done in Lab 7, Lab 8, Lab 9 and Lab 10 concepts. However, it requires that you load the video store model and the scripts from Lab 10. Those setup scripts are provided by the Oracle Database 12c PL/SQL Programming textbook.

You will need to use the following components from the video store model:

  • The item and contact tables.
  • The item_s1 and contact_s1 sequences.

You will create the following components in this lab:

  • The logger table.
  • The logger_s sequence.
  • The overloaded insert_item autonomous procedures inside the manage_item package.
  • The item_trig trigger.

This lab creates critical and non-critical triggers. However, this lab will leverage Oracle’s special feature of creating database triggers that work with INSERT, UPDATE, and DELETE statements. That means you’ll write:

  • One row-level trigger that manages all of the DML events created by an INSERT, UPDATE, or DELETE statement (e.g., a row-level trigger that captures and processes all unconditional DML statements), or
  • Two row-level triggers:
    • One manages the INSERT and UPDATE DML events conditionally. You accomplish that by leveraging the (a) OF column_name subclause and (b) the WHEN subclause. You can use the WHEN subclause with a regular expression function to limit the trigger to only managing events where the item_title contains a colon (check Appendix E in the Oracle Database 12c PL/SQL Programming textbook, pages 1000 to 1017).
    • One manages the DELETE DML event by capturing any deletion from the item table.

The following two articles cover the basics of working with database triggers and trigger logs in an Oracle Database:

There are three elements to this lab. First, you create a logger table and logger_s sequence. Second, you create overloaded insert_item autonomous procedures in the manage_item package. Third, you create an item_trig trigger that manages INSERT, UPDATE, and DELETE statement events against the item table; and the overloaded insert_item autonomous procedure process or reject events while writing (or logging) both the new_ and old_ column values to the logger table.

  1. [5 points] Creates a logger table and logger_s sequence. (These have a different structure than the logger table you created in the last lab, which means you need to drop that table at the beginning of your script for this lab.)

    The sections cover the detailed tasks of the lab:

  1. [10 points] Create overloaded item_insert autonomous procedures inside a manage_item package. These item_insert procedures are being designed to insert the old_ and new_ pseudo columns from the item table into the logger table within the scope of a database trigger. (For these tasks, you may want to review the row-level trigger section of the textbook on pages 518-526.)

    The sections cover the detailed tasks of the lab:

  1. [10 points] You have two options with this step. You can create an item_trig trigger that manages INSERT, UPDATE, and DELETE row-level events. Alternatively, you can create an item_trig trigger that manages INSERT or UPDATE row-level events and an item_delete_trig that manages the DELETE row-level event.

    The sections cover the detailed tasks of the lab:

Written by michaelmclaughlin

November 27th, 2016 at 11:23 pm

Posted in