Database Tutorial

Course Tutorial Site

Site Admin

CIT325: Lab 11 Instructions

without comments

Lab #10: Oracle Assignment

Objectives

The lab is designed to teach you how to write Data Manipulation Language (DML) triggers with Oracle’s PL/SQL. You create a logger table where you record changes to the rows in the item table. Those changes occur when you call an INSERT, UPDATE, or DELETE statement. You create two DML triggers and a manage_item package to accomplish this task, which lets you leverage overloading. Overloading lets you write procedures that logs new and old row information from an INSERT or UPDATE statements. Overloading also lets you write a procedure that logs old row information when you delete a row from the item table.

  • Learn how to create a event logging table.
  • Learn how to create insert, update, and delete DML triggers.
  • Learn how to leverage overloading in a package.
  • Learn how to test insert, update, and delete DML triggers.

Business Scenario

Application programming solutions often require the ability to capture and correct end-user inputs that violate rules. Sometimes, you simply correct the input, like ensuring multiple part names have a hyphen between each element. Other times, you need to log the attempt to enter the data while preventing the data entry. You typically raise an exception when when you want to prevent data entry.

You accomplish this part of application programming with triggering events. In the concept of databases, you accomplish this with data manipulation language (DML) database triggers. DML database triggers let you hid auditing code beneath the application programming interface. INSERT, UPDATE, and DELETE statements fire database triggers. Statement-level DML triggers fire once for each INSERT, UPDATE, or DELETE statement but row-level DML triggers fire once for every row touched by an INSERT, UPDATE, ore DELETE statement.

Help Section

The lab builds on some work done in prior labs. You should put the following two lines at the beginning of your apply_plsql_lab11.sql script:

@/home/student/Data/cit325/lib/cleanup_oracle.sql
@/home/student/Data/cit325/lib/Oracle12cPLSQLCode/Introduction/create_video_store.sql

After you have recreated a generic video store with the scripts from the Oracle Database 12c PL/SQL Programming book, you need to add a text_file_name column to the item table. The text_file_name column should use a VARCHAR2(30) data type. (HINT: You can add the column with the ALTER TABLE command.)

You can run a verification script to ensure you have the correct text_file_name column in the item table definition.

Modified ITEM Table →

COLUMN table_name   FORMAT A14
COLUMN column_id    FORMAT 9999
COLUMN column_name  FORMAT A22
COLUMN data_type    FORMAT A12
SELECT   table_name
,        column_id
,        column_name
,        CASE
           WHEN nullable = 'N' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        CASE
           WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN
             data_type||'('||data_length||')'
           ELSE
             data_type
         END AS data_type
FROM     user_tab_columns
WHERE    table_name = 'ITEM'
ORDER BY 2;

It should display the following:

TABLE_NAME     COLUMN_ID COLUMN_NAME		NULLABLE DATA_TYPE
-------------- --------- ---------------------- -------- ------------
ITEM		       1 ITEM_ID		NOT NULL NUMBER(22)
ITEM		       2 ITEM_BARCODE		NOT NULL VARCHAR2(20)
ITEM		       3 ITEM_TYPE		NOT NULL NUMBER(22)
ITEM		       4 ITEM_TITLE		NOT NULL VARCHAR2(60)
ITEM		       5 ITEM_SUBTITLE			 VARCHAR2(60)
ITEM		       6 ITEM_DESC		NOT NULL CLOB
ITEM		       7 ITEM_PHOTO			 BFILE
ITEM		       8 ITEM_RATING		NOT NULL VARCHAR2(8)
ITEM		       9 ITEM_RATING_AGENCY	NOT NULL VARCHAR2(4)
ITEM		      10 ITEM_RELEASE_DATE	NOT NULL DATE
ITEM		      11 CREATED_BY		NOT NULL NUMBER(22)
ITEM		      12 CREATION_DATE		NOT NULL DATE
ITEM		      13 LAST_UPDATED_BY	NOT NULL NUMBER(22)
ITEM		      14 LAST_UPDATE_DATE	NOT NULL DATE
ITEM		      15 TEXT_FILE_NAME 		 VARCHAR2(30)

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.
  • The item_delete_trig trigger (if you opt for two triggers).

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 instructional material shows you:

  • How to write a stored package to manage insertions into the logging table.
  • How to test the overloaded procedures of a stored package.
  • How to write an insert, update, and delete trigger.
  • How to test the insert, update, and delete trigger.

Instructional Material →

  1. The first step requires that you create an avenger and logging table, which you can do with the following code: 

    CREATE TABLE avenger
( avenger_id    NUMBER
, avenger_name  VARCHAR2(30));
 
CREATE SEQUENCE avenger_s;
 
CREATE TABLE logging
( logging_id  NUMBER
, old_avenger_id    NUMBER
, old_avenger_name  VARCHAR2(30)
, new_avenger_id    NUMBER
, new_avenger_name  VARCHAR2(30)
, CONSTRAINT logging_pk PRIMARY KEY (logging_id));
 
CREATE SEQUENCE logging_s;

  1. The second step requires that you verify an avenger and logging table, which you can do with the following scripts: 

    COLUMN table_name   FORMAT A14
COLUMN column_id    FORMAT 9999
COLUMN column_name  FORMAT A22
COLUMN data_type    FORMAT A12
SELECT   table_name
,        column_id
,        column_name
,        CASE
           WHEN nullable = 'N' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        CASE
           WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN
             data_type||'('||data_length||')'
           ELSE
             data_type
         END AS data_type
FROM     user_tab_columns
WHERE    table_name = 'LOGGING'
ORDER BY 2;
 
SELECT   table_name
,        column_id
,        column_name
,        CASE
           WHEN nullable = 'N' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        CASE
           WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN
             data_type||'('||data_length||')'
           ELSE
             data_type
         END AS data_type
FROM     user_tab_columns
WHERE    table_name = 'AVENGER'
ORDER BY 2;

    The tables should look like this:

    TABLE_NAME     COLUMN_ID COLUMN_NAME		NULLABLE DATA_TYPE
-------------- --------- ---------------------- -------- ------------
LOGGING 	       1 LOGGING_ID		NOT NULL NUMBER(22)
LOGGING 	       2 OLD_AVENGER_ID 		 NUMBER(22)
LOGGING 	       3 OLD_AVENGER_NAME		 VARCHAR2(30)
LOGGING 	       4 NEW_AVENGER_ID 		 NUMBER(22)
LOGGING 	       5 NEW_AVENGER_NAME		 VARCHAR2(30)
 
 
TABLE_NAME     COLUMN_ID COLUMN_NAME		NULLABLE DATA_TYPE
-------------- --------- ---------------------- -------- ------------
AVENGER 	       1 AVENGER_ID			 NUMBER(22)
AVENGER 	       2 AVENGER_NAME			 VARCHAR2(30)

  1. The third step requires that write a log_avenger package with overloaded avenger_insert procedures that write new, old and new, and old values from the avenger table: 

    CREATE OR REPLACE
  PACKAGE log_avenger IS
 
  PROCEDURE avenger_insert
  ( pv_new_avenger_id    NUMBER
  , pv_new_avenger_name  VARCHAR2 );
 
  PROCEDURE avenger_insert
  ( pv_new_avenger_id    NUMBER
  , pv_new_avenger_name  VARCHAR2
  , pv_old_avenger_id    NUMBER
  , pv_old_avenger_name  VARCHAR2 );
 
  PROCEDURE avenger_insert
  ( pv_old_avenger_id    NUMBER
  , pv_old_avenger_name  VARCHAR2 );
 
END log_avenger;
/

    The package definition should look like this when you DESCRIBE it:

    PROCEDURE AVENGER_INSERT
 Argument Name			TYPE			IN/OUT DEFAULT?
 ------------------------------ ----------------------- ------ --------
 PV_NEW_AVENGER_ID		NUMBER			IN
 PV_NEW_AVENGER_NAME		VARCHAR2		IN
PROCEDURE AVENGER_INSERT
 Argument Name			TYPE			IN/OUT DEFAULT?
 ------------------------------ ----------------------- ------ --------
 PV_NEW_AVENGER_ID		NUMBER			IN
 PV_NEW_AVENGER_NAME		VARCHAR2		IN
 PV_OLD_AVENGER_ID		NUMBER			IN
 PV_OLD_AVENGER_NAME		VARCHAR2		IN
PROCEDURE AVENGER_INSERT
 Argument Name			TYPE			IN/OUT DEFAULT?
 ------------------------------ ----------------------- ------ --------
 PV_OLD_AVENGER_ID		NUMBER			IN
 PV_OLD_AVENGER_NAME		VARCHAR2		IN

    You implement the package body like this:

    CREATE OR REPLACE
  PACKAGE BODY log_avenger IS
 
  PROCEDURE avenger_insert
  ( pv_new_avenger_id    NUMBER
  , pv_new_avenger_name  VARCHAR2 ) IS
 
    /* Set an autonomous transaction. */
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    /* Insert log entry for an avenger. */
    log_avenger.avenger_insert(
        pv_old_avenger_id => NULL
      , pv_old_avenger_name => NULL
      , pv_new_avenger_id => pv_new_avenger_id
      , pv_new_avenger_name => pv_new_avenger_name);
  EXCEPTION
    /* Exception handler. */
    WHEN OTHERS THEN
     RETURN;
  END avenger_insert;
 
  PROCEDURE avenger_insert
  ( pv_new_avenger_id    NUMBER
  , pv_new_avenger_name  VARCHAR2
  , pv_old_avenger_id    NUMBER
  , pv_old_avenger_name  VARCHAR2 ) IS
 
    /* Declare local logging value. */
    lv_logging_id  NUMBER;
 
    /* Set an autonomous transaction. */
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    /* Get a sequence. */
    lv_logging_id := logging_s.NEXTVAL;
 
    /* Set a savepoint. */
    SAVEPOINT starting;
 
    /* Insert log entry for an avenger. */
    INSERT INTO logging
    ( logging_id
    , new_avenger_id
    , new_avenger_name
    , old_avenger_id
    , old_avenger_name )
    VALUES
    ( lv_logging_id
    , pv_new_avenger_id
    , pv_new_avenger_name
    , pv_old_avenger_id
    , pv_old_avenger_name );
 
    /* Commit the independent write. */
    COMMIT;
  EXCEPTION
    /* Exception handler. */
    WHEN OTHERS THEN
      ROLLBACK TO starting;
      RETURN;
  END avenger_insert;
 
  PROCEDURE avenger_insert
  ( pv_old_avenger_id    NUMBER
  , pv_old_avenger_name  VARCHAR2 ) IS
 
    /* Set an autonomous transaction. */
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    /* Insert log entry for an avenger. */
    log_avenger.avenger_insert(
        pv_old_avenger_id => pv_old_avenger_id
      , pv_old_avenger_name => pv_old_avenger_name
      , pv_new_avenger_id => NULL
      , pv_new_avenger_name => NULL);
  EXCEPTION
    /* Exception handler. */
    WHEN OTHERS THEN
     RETURN;
  END avenger_insert;
END log_avenger;
/

  1. The fourth step requires that write and test calls to log_avenger package: 

    DECLARE
  /* Define input values. */
  lv_new_avenger_id    NUMBER        := 1;
  lv_new_avenger_name  VARCHAR2(30) := 'Thor';
BEGIN
  log_avenger.avenger_insert(
      pv_new_avenger_id => lv_new_avenger_id
    , pv_new_avenger_name => lv_new_avenger_name );
END;
/
 
DECLARE
  /* Define input values. */
  lv_new_avenger_id    NUMBER        := 2;
  lv_new_avenger_name  VARCHAR2(30) := 'Hulk';
BEGIN
  log_avenger.avenger_insert(
      pv_new_avenger_id => lv_new_avenger_id
    , pv_new_avenger_name => lv_new_avenger_name );
END;
/
 
DECLARE
  /* Define input values. */
  lv_avenger_id        NUMBER       := 3;
  lv_old_avenger_name  VARCHAR2(30) := 'Thor';
  lv_new_avenger_name  VARCHAR2(30) := 'King Thor';
BEGIN
  log_avenger.avenger_insert(
      pv_old_avenger_id => lv_avenger_id
    , pv_old_avenger_name => lv_old_avenger_name
    , pv_new_avenger_id => lv_avenger_id
    , pv_new_avenger_name => lv_new_avenger_name );
END;
/
 
DECLARE
  /* Define input values. */
  lv_old_avenger_id    NUMBER        := 4;
  lv_old_avenger_name  VARCHAR2(30) := 'King Thor';
BEGIN
  log_avenger.avenger_insert(
      pv_old_avenger_id => lv_old_avenger_id
    , pv_old_avenger_name => lv_old_avenger_name );
END;
/

    You can check whether the test cases work with the following query:

    COL logger_id         FORMAT 999999  HEADING "Logging|ID #"
COL old_avenger_id    FORMAT 999999  HEADING "Old|Avenger|ID #"
COL old_avenger_name  FORMAT A25     HEADING "Old Avenger Name"
COL new_avenger_id    FORMAT 999999  HEADING "New|Avenger|ID #"
COL new_avenger_name  FORMAT A25     HEADING "New Avenger Name"
SELECT * FROM logging;

    The tables should look like this:

    	       Old			    NEW
	   Avenger			Avenger
LOGGING_ID    ID # Old Avenger Name	   ID # NEW Avenger Name
---------- ------- -------------------- ------- ------------------------------
	 1				      1 Thor
	 2				      2 Hulk
	 3	 3 Thor 		      3 King Thor
	 4	 4 King Thor

  1. The fifth step requires that write an average_trig trigger for insert and update statements: 

    CREATE OR REPLACE
  TRIGGER avenger_trig
  BEFORE INSERT OR UPDATE OF avenger_name ON avenger
  FOR EACH ROW
  DECLARE
    /* Declare exception. */
    e EXCEPTION;
    PRAGMA EXCEPTION_INIT(e,-20001);
  BEGIN
    /* Check for an event and log accordingly. */
    IF INSERTING THEN
      /* Log the insert change to the item table in the logger table. */
      log_avenger.avenger_insert(
          pv_new_avenger_id => :NEW.avenger_id
        , pv_new_avenger_name => :NEW.avenger_name );
 
      /* Check for an empty item_id primary key column value,
         and assign the next sequence value when it is missing. */
      IF :NEW.avenger_id IS NULL THEN
        SELECT avenger_s.NEXTVAL
        INTO   :NEW.avenger_id
        FROM   dual;
      END IF;
    ELSIF UPDATING THEN
      /* Log the update change to the item table in the logging table. */
      log_avenger.avenger_insert(
          pv_new_avenger_id => :NEW.avenger_id
        , pv_new_avenger_name => :NEW.avenger_name
        , pv_old_avenger_id => :old.avenger_id
        , pv_old_avenger_name => :old.avenger_name );
    END IF;
  END avenger_trig;
/

  1. The sixth step requires that write an average_delete_trig trigger for delete statements: 

    CREATE OR REPLACE
  TRIGGER avenger_delete_trig
  BEFORE DELETE ON avenger
  FOR EACH ROW
  DECLARE
    /* Declare exception. */
    e EXCEPTION;
    PRAGMA EXCEPTION_INIT(e,-20001);
  BEGIN
    IF DELETING THEN
      /* Log the delete change to the item table in the logging table. */
      log_avenger.avenger_insert(
          pv_old_avenger_id => :old.avenger_id
        , pv_old_avenger_name => :old.avenger_name );
    END IF;
  END avenger_trig;
/

  1. The seventh step requires that write an insert, update, and delete statement: 

    INSERT INTO avenger
( avenger_id
, avenger_name )
VALUES
( avenger_s.NEXTVAL
,'Captain America');
 
UPDATE avenger
SET    avenger_name = 'Captain America "Wanted"'
WHERE  avenger_name = 'Captain America';
 
DELETE
FROM   avenger
WHERE  avenger_name LIKE 'Captain America%';

  1. The eighth step requires that write a query to see what was inserted into the logging table: 

    COL logger_id         FORMAT 999999  HEADING "Logging|ID #"
COL old_avenger_id    FORMAT 999999  HEADING "Old|Avenger|ID #"
COL old_avenger_name  FORMAT A25     HEADING "Old Avenger Name"
COL new_avenger_id    FORMAT 999999  HEADING "New|Avenger|ID #"
COL new_avenger_name  FORMAT A25     HEADING "New Avenger Name"
SELECT * FROM logging;

    You will see the following results:

    	       Old				 New
	   Avenger			     Avenger
LOGGING_ID    ID # Old Avenger Name		ID # New Avenger Name
---------- ------- ------------------------- ------- -------------------------
	 1					   1 Thor
	 2					   2 Hulk
	 3	 3 Thor 			   3 King Thor
	 4	 4 King Thor
	 5					   1 Captain America
	 6	 1 Captain America		   1 Captain America "Wanted"
	 7	 1 Captain America "Wanted"

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:

Instruction Details →

The following qualifies the instructions for this element’s steps:

  • You should verify the item table definition looks like the following example. You can display the result by using the following SQL*Plus command at the SQL> prompt: 

    desc item

    It will display the following item table definition:

     Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ITEM_ID				   NOT NULL NUMBER
 ITEM_BARCODE				   NOT NULL VARCHAR2(20)
 ITEM_TYPE				   NOT NULL NUMBER
 ITEM_TITLE				   NOT NULL VARCHAR2(60)
 ITEM_SUBTITLE					    VARCHAR2(60)
 ITEM_DESC				   NOT NULL CLOB
 ITEM_PHOTO					    BINARY FILE LOB
 ITEM_RATING				   NOT NULL VARCHAR2(8)
 ITEM_RATING_AGENCY			   NOT NULL VARCHAR2(4)
 ITEM_RELEASE_DATE			   NOT NULL DATE
 CREATED_BY				   NOT NULL NUMBER
 CREATION_DATE				   NOT NULL DATE
 LAST_UPDATED_BY			   NOT NULL NUMBER
 LAST_UPDATE_DATE			   NOT NULL DATE
 TEXT_FILE_NAME 				    VARCHAR2(40)

  • You create the logger table and logger_s sequence. The logger table should hold two copies of the item table columns that aren’t large object data types. The logger_id column should be the primary key column and hold a value generated by the logger_s sequence.

    You can display the result by using the following SQL*Plus command at the SQL> prompt:

    desc logger

    It will display the following logger table definition:

     Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOGGER_ID				   NOT NULL NUMBER
 OLD_ITEM_ID					    NUMBER
 OLD_ITEM_BARCODE				    VARCHAR2(20)
 OLD_ITEM_TYPE					    NUMBER
 OLD_ITEM_TITLE 				    VARCHAR2(60)
 OLD_ITEM_SUBTITLE				    VARCHAR2(60)
 OLD_ITEM_RATING				    VARCHAR2(8)
 OLD_ITEM_RATING_AGENCY 			    VARCHAR2(4)
 OLD_ITEM_RELEASE_DATE				    DATE
 OLD_CREATED_BY 				    NUMBER
 OLD_CREATION_DATE				    DATE
 OLD_LAST_UPDATED_BY				    NUMBER
 OLD_LAST_UPDATE_DATE				    DATE
 OLD_TEXT_FILE_NAME				    VARCHAR2(40)
 NEW_ITEM_ID					    NUMBER
 NEW_ITEM_BARCODE				    VARCHAR2(20)
 NEW_ITEM_TYPE					    NUMBER
 NEW_ITEM_TITLE 				    VARCHAR2(60)
 NEW_ITEM_SUBTITLE				    VARCHAR2(60)
 NEW_ITEM_RATING				    VARCHAR2(8)
 NEW_ITEM_RATING_AGENCY 			    VARCHAR2(4)
 NEW_ITEM_RELEASE_DATE				    DATE
 NEW_CREATED_BY 				    NUMBER
 NEW_CREATION_DATE				    DATE
 NEW_LAST_UPDATED_BY				    NUMBER
 NEW_LAST_UPDATE_DATE				    DATE
 NEW_TEXT_FILE_NAME				    VARCHAR2(40)

    • Test Case

  • The purpose of this test is to verify that you have a working INSERT statement into the logger table, which you’ll use in subsequent components of the lab.

    You test that the logger table component work by two steps. You describe the logger table and insert one or more rows into the logger table.

    • You should be able to write an INSERT statement against the logger table with literal values without any help. You probably do need help if you want to test a dynamic INSERT statement into the logger table. That type of INSERT statement uses dynamic assignments of local variables, like you will do inside a stored procedure.

    The following anonymous PL/SQL block shows you how to test your INSERT statement into the logger table:

    1
2
3
4
5
6
7
8
9
10
11
12
13

    DECLARE
  /* Dynamic cursor. */
  CURSOR get_row IS
    SELECT * FROM item WHERE item_title = 'Brave Heart';
BEGIN
  /* Read the dynamic cursor. */
  FOR i IN get_row LOOP
 
    ... insert the data into the logger table ...
 
  END LOOP;
END;
/

    The following test query returns the rows selected by the get_row cursor of the preceding sample code. The get_row cursor selects the item_title column where the item_title equals a string literal value of 'Brave Heart'. Then, you write an INSERT statement inside the loop that:

    • Writes the logger_s sequence value
    • Writes one copy of the item table’s columns into the old_ columns of the logger table
    • Writes one copy of the item table’s columns into the new_ columns of the logger table

    /* Query the logger table. */
COL logger_id       FORMAT 9999 HEADING "Logger|ID #"
COL old_item_id     FORMAT 9999 HEADING "Old|Item|ID #"
COL old_item_title  FORMAT A20  HEADING "Old Item Title"
COL new_item_id     FORMAT 9999 HEADING "New|Item|ID #"
COL new_item_title  FORMAT A30  HEADING "New Item Title"
SELECT l.logger_id
,      l.old_item_id
,      l.old_item_title
,      l.new_item_id
,      l.new_item_title
FROM   logger l;

    The test query should return the following rows:

    	 Old			    New
Logger	Item			   Item
  ID #	ID # Old Item Title	   ID # New Item Title
------ ----- -------------------- ----- ------------------------------
     1	1014 Brave Heart	   1014 Brave Heart

    You should notice that the values in the old_ columns are the same as the values in the new_ columns of the logger table because all you’re testing is that your INSERT statement works. It’s always a good testing strategy to build components in steps because testing them in larger groups is more difficult.

  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:

Instruction Details →

The following qualifies the instructions for this step:

  • Create overloaded item_insert autonomous procedures inside a manage_item package. The item_insert autonomous procedures should take only the necessary values for an INSERT, UPDATE or DELETE statement.

    You can display the result by using the following SQL*Plus command at the SQL> prompt:

    desc manage_item

    It will display the following package definition:

    PROCEDURE ITEM_INSERT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PV_NEW_ITEM_ID 		NUMBER			IN
 PV_NEW_ITEM_BARCODE		VARCHAR2		IN
 PV_NEW_ITEM_TYPE		NUMBER			IN
 PV_NEW_ITEM_TITLE		VARCHAR2		IN
 PV_NEW_ITEM_SUBTITLE		VARCHAR2		IN
 PV_NEW_ITEM_RATING		VARCHAR2		IN
 PV_NEW_ITEM_RATING_AGENCY	VARCHAR2		IN
 PV_NEW_ITEM_RELEASE_DATE	DATE			IN
 PV_NEW_CREATED_BY		NUMBER			IN
 PV_NEW_CREATION_DATE		DATE			IN
 PV_NEW_LAST_UPDATED_BY 	NUMBER			IN
 PV_NEW_LAST_UPDATE_DATE	DATE			IN
 PV_NEW_TEXT_FILE_NAME		VARCHAR2		IN
 
PROCEDURE ITEM_INSERT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PV_OLD_ITEM_ID 		NUMBER			IN
 PV_OLD_ITEM_BARCODE		VARCHAR2		IN
 PV_OLD_ITEM_TYPE		NUMBER			IN
 PV_OLD_ITEM_TITLE		VARCHAR2		IN
 PV_OLD_ITEM_SUBTITLE		VARCHAR2		IN
 PV_OLD_ITEM_RATING		VARCHAR2		IN
 PV_OLD_ITEM_RATING_AGENCY	VARCHAR2		IN
 PV_OLD_ITEM_RELEASE_DATE	DATE			IN
 PV_OLD_CREATED_BY		NUMBER			IN
 PV_OLD_CREATION_DATE		DATE			IN
 PV_OLD_LAST_UPDATED_BY 	NUMBER			IN
 PV_OLD_LAST_UPDATE_DATE	DATE			IN
 PV_OLD_TEXT_FILE_NAME		VARCHAR2		IN
 PV_NEW_ITEM_ID 		NUMBER			IN
 PV_NEW_ITEM_BARCODE		VARCHAR2		IN
 PV_NEW_ITEM_TYPE		NUMBER			IN
 PV_NEW_ITEM_TITLE		VARCHAR2		IN
 PV_NEW_ITEM_SUBTITLE		VARCHAR2		IN
 PV_NEW_ITEM_RATING		VARCHAR2		IN
 PV_NEW_ITEM_RATING_AGENCY	VARCHAR2		IN
 PV_NEW_ITEM_RELEASE_DATE	DATE			IN
 PV_NEW_CREATED_BY		NUMBER			IN
 PV_NEW_CREATION_DATE		DATE			IN
 PV_NEW_LAST_UPDATED_BY 	NUMBER			IN
 PV_NEW_LAST_UPDATE_DATE	DATE			IN
 PV_NEW_TEXT_FILE_NAME		VARCHAR2		IN
 
PROCEDURE ITEM_INSERT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PV_OLD_ITEM_ID 		NUMBER			IN
 PV_OLD_ITEM_BARCODE		VARCHAR2		IN
 PV_OLD_ITEM_TYPE		NUMBER			IN
 PV_OLD_ITEM_TITLE		VARCHAR2		IN
 PV_OLD_ITEM_SUBTITLE		VARCHAR2		IN
 PV_OLD_ITEM_RATING		VARCHAR2		IN
 PV_OLD_ITEM_RATING_AGENCY	VARCHAR2		IN
 PV_OLD_ITEM_RELEASE_DATE	DATE			IN
 PV_OLD_CREATED_BY		NUMBER			IN
 PV_OLD_CREATION_DATE		DATE			IN
 PV_OLD_LAST_UPDATED_BY 	NUMBER			IN
 PV_OLD_LAST_UPDATE_DATE	DATE			IN
 PV_OLD_TEXT_FILE_NAME		VARCHAR2		IN
  • You need to implement a manage_item package body. It’s easier to implement the overloaded insert_item autonomous procedures in the following way:

    • UPDATE Statement item_insert procedure: You implement the item_insert procedure for the UPDATE statement first because it has to manage the old_ and new_ pseudo columns of a row-level trigger.

      The update activity of a row in the item table will log the logger_id, new_ column values and old_ column values from the item table. You should put your dynamic INSERT statement into the logger table inside the version of the overloaded item_insert procedure.

    • INSERT Statement item_insert procedure: You implement the insert_item procedure that accepts only new_ column values to support on insert row-level database trigger.

      The insert activity of a row into the item table will log only the logger_id and new_ column values. This presents the opportunity to simply call the other overloaded item_insert procedure that accepts old_ and new_ column values.

      You make that call to the other overloaded item_insert procedure by sending null values to old_ column values of the item_insert procedure. The list of call parameters determines which of the overloaded procedure handles the request. Only one version of the overloaded item_insert procedure contains the old_ and new_ column values.

      The benefit of cross-calling between the overloaded procedures isn’t straight forward. However, it’s ultimately simple. You only write one INSERT statement to the logger table, and you put it in the most complete (or largest parameter list version) of the item_insert procedure. The other versions of the overloaded procedure become wrappers that add null values to the missing parameter items and call the most complete version of the overloaded procedure. Taking this approach ultimately lowers maintenance coding costs.

    • DELETE Statement item_insert procedure: You implement the insert_item procedure that accepts only old_ column values to support on delete row-level database trigger.

      The delete activity of a row from the item table will log only the logger_id and old_ column values. This presents the opportunity, like the insert activity to simply call the other overloaded item_insert procedure that accepts old_ and new_ column values. Please refer to the explanation above as to why this is an effective solution.

    Test Case

  • You test that the overloaded insert_item procedures write the correct data to the logger table. You should copy and complete the sample anonymous block PL/SQL programs for each of the three scenarios:

    • Logging the result of inserting a new record in the item table in the logger table.
    • Logging the result of updating a new record in the item table in the logger table.
    • Logging the result of deleting a new record in the item table in the logger table.

    You should use the following anonymous PL/SQL block to test your overloaded item_insert procedures. 

    1
2
3
4
5
6
7
8
9
10
11
12
13

    DECLARE
  /* Dynamic cursor. */
  CURSOR get_row IS
    SELECT * FROM item WHERE item_title = 'King Arthur';
BEGIN
  /* Read the dynamic cursor. */
  FOR i IN get_row LOOP
 
    ... insert into, update in, and delete from the logger table ...
 
  END LOOP;
END;
/

    You can use the same anonymous PL/SQL block because you’re currently not making any changes to the item table’s rows. You’re only mimicking what such changes would look like to test your overloaded item_insert procedures, which write to the logger table.

    The test cases should follow the pattern described below:

    • UPDATE Statement: Call the overloaded item_insert procedure that includes the old_ and new_ column values and holds your dynamic INSERT statement to the logger table. Assuming you are using the cross-calling technique, this test case must work before you move to the other test cases.

      You should add the '-Change' string to the new_ column value for the item_title column, like:

      25

        	   , pv_new_item_title => i.item_title || '-Changed'

      The snippet of code and line number assumes you’re using the anonymous PL/SQL testing block provided to support the lab. A copy of the anonymous PL/SQL block appears in the previous section and below.

    • INSERT Statement: Call the overloaded item_insert procedure that only supports the new_ column values. You should add the '-Change' string to the new_ column value for the item_title column, like: 

      12

        	   , pv_new_item_title => i.item_title || '-Inserted'

      The snippet of code and line number assumes you’re using the anonymous PL/SQL testing block provided to support the lab. A copy of the anonymous PL/SQL block appears in the previous section and below.

    • DELETE Statement: Call the overloaded item_insert procedure that only supports the old_ column values. You should add the '-Deleted' string to the old_ column value for the item_title column, like: 

      12

        	   , pv_new_item_title => i.item_title || '-Deleted'

      Like the INSERT statement example, this snippet of code and line number assumes you’re using the anonymous PL/SQL testing block provided to support the lab. A copy of the anonymous PL/SQL block appears in the previous section and below.

    • The following test query returns the rows from all three test scenarios. You should have an entry for King Arthur, but if you don’t add one.

    /* Query the logger table. */
/* Query the logger table. */
COL logger_id       FORMAT 9999 HEADING "Logger|ID #"
COL old_item_id     FORMAT 9999 HEADING "Old|Item|ID #"
COL old_item_title  FORMAT A20  HEADING "Old Item Title"
COL new_item_id     FORMAT 9999 HEADING "New|Item|ID #"
COL new_item_title  FORMAT A30  HEADING "New Item Title"
SELECT l.logger_id
,      l.old_item_id
,      l.old_item_title
,      l.new_item_id
,      l.new_item_title
FROM   logger l;

    The test query should return the following rows:

    	 Old			    New
Logger	Item			   Item
  ID #	ID # Old Item Title	   ID # New Item Title
------ ----- -------------------- ----- ------------------------------
     1	1014 Brave Heart	   1014 Brave Heart
     2				   1035 King Arthur
     3	1035 King Arthur	   1035 King Arthur-Changed
     4	1035 King Arthur-Deleted

    Rows 1 thru 4 are the result of testing whether your INSERT statement to the logger table worked. Rows 5 thru 6 are the results of testing whether your overloaded PL/SQL item_insert procedures work for an INSERT statement. Rows 9 thru 12 are the results of testing an UPDATE statement, and rows 13 thru 16 are the results of testing a DELETE statement.

  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:

Instruction Details →

You have technically three options with this step. One uses three separate triggers, one uses two separate triggers, and one uses one trigger. However, you really only have one effective choice. That choice requires you to write one item_trig trigger that fires on an INSERT or UPDATE row-level event when there’s a colon in the :new.item_title column; and one item_delete_trig trigger that fires on a DELETE row-level event for any delete from the item table.

Two-trigger Solution

  • Create an item_trig trigger that performs the following tasks for INSERT and UPDATE statements:

    • You use an if INSERTING block to call the manage_item.insert_item procedure to write the log entries for an INSERT statement event.
      • You use regular expression functions to check for a colon. If you don’t find a colon the title is only a title and you should assign it to the :new.item_title value, but when you find a colon divide the title from the subtitle and assign them to the :new.item_title and the :new.item_subtitle values.
    • You use an else if UPDATING block to call the manage_item.insert_item procedure to write the log entries for an UPDATE statement event.
      • You write column values to their respective :new.* and :old.* values by calling the manage_item.insert_item procedure. Then, you throw an ORA-20001 exception with the following text message “No colons allowed in item titles.”

  • Create an item_delete_trig trigger that performs the following tasks for a DELETE statement:
    • You write the item_delete_trig trigger to fire on any DELETE row-level event on the item table. The trigger should write the :old.* values by calling the manage_item.insert_item procedure.

Testing the logic of database triggers is very complex. As a result, it seems helpful to first create a stand alone procedure that demonstrates how to hand new input values.

Sample Code →

The sample test_insert procedure demonstrates the logic outside of a database trigger.

CREATE OR REPLACE
  PROCEDURE test_insert
  ( pv_input_title  VARCHAR2 ) IS
  /* Declare local variables. */
  lv_input_title   VARCHAR2(40);
  lv_title         VARCHAR2(20);
  lv_subtitle      VARCHAR2(20);
 
BEGIN
  /* Assign the title */
  lv_input_title := SUBSTR(pv_input_title,1,40);
 
  /* Check for a subtitle. */
  IF REGEXP_INSTR(lv_input_title,':') > 0 AND
     REGEXP_INSTR(lv_input_title,':') = LENGTH(lv_input_title) THEN
    /* Shave off the colon. */
    lv_title   := SUBSTR(lv_input_title, 1, REGEXP_INSTR(lv_input_title,':') - 1);
  ELSIF REGEXP_INSTR(lv_input_title,':') > 0 THEN
    /* Split the string into two parts. */
    lv_title    := SUBSTR(lv_input_title, 1, REGEXP_INSTR(lv_input_title,':') - 1);
    lv_subtitle := LTRIM(SUBSTR(lv_input_title,REGEXP_INSTR(lv_input_title,':') + 1, LENGTH(lv_input_title)));
  ELSE
    /* Assign the input value as the title. */
    lv_title := pv_input_title;
  END IF;
  /* Print the substrings. */
  dbms_output.put_line('['||lv_title||']');
  dbms_output.put_line('['||lv_subtitle||']');
END;
/

You can test the three input scenarios with the following test cases:

EXECUTE test_insert('Harry Potter');
 
EXECUTE test_insert('Harry Potter:');
 
EXECUTE test_insert('Harry Potter: Goblet of Fire');

They should print the following results, which you should insert into the column with your item_trig trigger (minus the square brackets). You should enable ECHO to see the calls and results together, as shown below:

SQL> EXECUTE test_insert('Harry Potter');
[Harry Potter]
[]
 
PL/SQL procedure successfully completed.
 
SQL> 
SQL> EXECUTE test_insert('Harry Potter:');
[Harry Potter]
[]
 
PL/SQL procedure successfully completed.
 
SQL> EXECUTE test_insert('Harry Potter: Goblet of Fire');
[Harry Potter]
[Goblet of Fire]
 
PL/SQL procedure successfully completed.

Test Case

  • You test that the components work using the following techniques or scripts.

    • You need to test all three trigger events by writing INSERT, UPDATE, and DELETE statements against the item table. Since you will run multiple tests to resolve your working code components, you need to structure the test case carefully.
    • You need to build some infrastructure elements to make the test case simple, like altering the item table’s structure to help cleanup test case data elements. The following are those steps:
      • Alter the fk_item_1 foreign key constraint from the item table because you can’t modify an existing foreign key constraint. Drop the fk_item_1 foreign key constraint from the item table.
      • Create a new fk_item_1 foreign key constraint on the item_type column of the item table with the ON DELETE CASCADE clause.
      • Include the following diagnostic query inside your script file: 

        COL item_id        FORMAT 9999 HEADING "Item|ID #"
COL item_title     FORMAT A20  HEADING "Item Title"
COL item_subtitle  FORMAT A20  HEADING "Item Subtitle"
COL item_type      FORMAT 9999 HEADING "Item|Type"
COL item_rating    FORMAT A6   HEADING "Item|Rating"
SELECT i.item_id
,      i.item_title
,      i.item_subtitle
,      i.item_type
,      i.item_rating
FROM   item i
WHERE  i.item_title = 'Star Wars';

        Initially (the first time you run it), you may return a row until you’ve completed your test case. When you’ve completed this test case you shouldn’t return any rows. You should see the following:

        no rows selected

      • Delete a row from the common_lookup table where the column_lookup_table is ITEM, the common_lookup_column is ITEM_TYPE, and the common_lookup_type is BLU-RAY. The deletion of a row from the common_lookup table will delete all test records from the item table.
      • Include the following diagnostic query again inside your script file: 

        COL item_id        FORMAT 9999 HEADING "Item|ID #"
COL item_title     FORMAT A20  HEADING "Item Title"
COL item_subtitle  FORMAT A20  HEADING "Item Subtitle"
COL item_type      FORMAT 9999 HEADING "Item|Type"
COL item_rating    FORMAT A6   HEADING "Item|Rating"
SELECT i.item_id
,      i.item_title
,      i.item_subtitle
,      i.item_type
,      i.item_rating
FROM   item i
WHERE  i.item_title = 'Star Wars';

        Like the prior version of the script, you shouldn’t return any rows from the item table. You should see the following:

        no rows selected
      • Again, you need to alter the fk_item_1 foreign key constraint from the item table because you can’t modify an existing foreign key constraint. Drop the fk_item_1 foreign key constraint from the item table.
      • Create the old fk_item_1 foreign key constraint on the item_type column of the item table without the ON DELETE CASCADE clause.
      • Insert a row into the common_lookup table with the following values:

        Table Name: COMMON_LOOKUP
        Column Name Column Value
        COMMON_LOOKUP_ID COMMON_LOOKUP_S1.NEXTVAL
        COMMON_LOOKUP_TABLE ITEM
        COMMON_LOOKUP_COLUMN ITEM_TYPE
        COMMON_LOOKUP_TYPE BLU-RAY
        COMMON_LOOKUP_CODE  
        COMMON_LOOKUP_MEANING Blu-ray
        CREATED_BY 3
        CREATION_DATE SYSDATE
        LAST_UPDATED_BY 3
        LAST_UPDATE_DATE SYSDATE

      • You can verify the addition to the common_lookup table with the following query:

        • COL common_lookup_table   FORMAT A14 HEADING "Common Lookup|Table"
COL common_lookup_column  FORMAT A14 HEADING "Common Lookup|Column"
COL common_lookup_type    FORMAT A14 HEADING "Common Lookup|Type"
SELECT common_lookup_table
,      common_lookup_column
,      common_lookup_type
FROM   common_lookup
WHERE  common_lookup_table = 'ITEM'
AND    common_lookup_column = 'ITEM_TYPE'
AND    common_lookup_type = 'BLU-RAY';

        You should see the following as the result set of the query:

        Common Lookup  Common Lookup Common Lookup
Table          Column        Type
-------------- -------------- --------------
ITEM           ITEM_TYPE      BLU-RAY

    • Write three INSERT statements that test the tree conditions of your inserting event trigger:

      Table Name: ITEM
      Column Name Column Value 1 Column Value 2 Column Value 3
      ITEM_ID ITEM_S1.NEXTVAL ITEM_S1.NEXTVAL ITEM_S1.NEXTVAL
      ITEM_BARCODE B01IHVPA8 B01AT251XY B018FK66TU
      ITEM_TYPE BLU-RAY BLU-RAY BLU-RAY
      ITEM_TITLE Bourne Bourne Legacy: Star Wars: The Force Awakens
      ITEM_SUBTITLE      
      ITEM_RATING PG-13 PG-13 PG-13
      ITEM_RATING_AGENCY MPAA MPAA MPAA
      ITEM_RELEASE_DATE 06-DEC-2016 05-APR-2016 05-APR-2016
      CREATED_BY 3 3 3
      CREATION_DATE SYSDATE SYSDATE SYSDATE
      LAST_UPDATED_BY 3 3 3
      LAST_UPDATE_DATE SYSDATE SYSDATE SYSDATE

    • You can verify that your item_trig trigger’s insert event works by querying the modified results that were inserted into the item table. The following query returns the results from the item table:

      • COL item_id        FORMAT 9999 HEADING "Item|ID #"
COL item_title     FORMAT A20  HEADING "Item Title"
COL item_subtitle  FORMAT A20  HEADING "Item Subtitle"
COL item_rating    FORMAT A6   HEADING "Item|Rating"
COL item_type      FORMAT A18   HEADING "Item|Type"
SELECT i.item_id
,      i.item_title
,      i.item_subtitle
,      i.item_rating
,      cl.common_lookup_meaning AS item_type
FROM   item i INNER JOIN common_lookup cl
ON     i.item_type = cl.common_lookup_id
WHERE  cl.common_lookup_type = 'BLU-RAY';

      You should see the following results, except for the item_id column values which may differ, from the item table:

       Item						Item   Item
 ID # Item Title	   Item Subtitle	Rating Type
----- -------------------- -------------------- ------ ------------------
 1094 Bourne					PG-13  Blu-ray
 1095 Bourne Legacy				PG-13  Blu-ray
 1096 Star Wars 	   The Force Awakens	PG-13  Blu-ray

      You verify the results from the logger table with the following query:

      /* Query the logger table. */
COL logger_id       FORMAT 9999 HEADING "Logger|ID #"
COL old_item_id     FORMAT 9999 HEADING "Old|Item|ID #"
COL old_item_title  FORMAT A20  HEADING "Old Item Title"
COL new_item_id     FORMAT 9999 HEADING "New|Item|ID #"
COL new_item_title  FORMAT A30  HEADING "New Item Title"
SELECT l.logger_id
,      l.old_item_id
,      l.old_item_title
,      l.new_item_id
,      l.new_item_title
FROM   logger l;

      You should see the following results from the logger table:

      	 Old			    New
Logger	Item			   Item
  ID #	ID # Old Item Title	   ID # New Item Title
------ ----- -------------------- ----- ------------------------------
     1	1014 Brave Heart	   1014 Brave Heart
     2				   1035 King Arthur
     3	1035 King Arthur	   1035 King Arthur-Changed
     4	1035 King Arthur-Deleted
     5				   1095 Bourne Legacy:
     6				   1096 Star Wars: The Force Awakens
     7	1096 Star Wars		   1096 Star Wars: The Force Awakens

      You should note that the logger table contains what was attempted as the insert event, and the item table contains the original input’s modified input. The trigger logs the unmodified attempt but only also the insert of data that meets the business rule.

    • Update the Star Wars row that you previously inserted into the item table to test your inserting event trigger with the following values:

      Table Name: ITEM
      Column Name Column Value
      item_title Star Wars: The Force Awakens

      You receive the following error, which should be raised by the updating event of your item_trig trigger:

      UPDATE item
       *
ERROR at line 1:
ORA-20001: No colons allowed in item titles.
ORA-06512: at "STUDENT.ITEM_TRIG", line 75
ORA-04088: error during execution of trigger 'STUDENT.ITEM_TRIG'

    • You can verify that your item_trig trigger’s update event works by querying the modified results again. The query against the item table should display the results from the inserting and updating test cases:

      • COL item_id        FORMAT 9999 HEADING "Item|ID #"
COL item_title     FORMAT A20  HEADING "Item Title"
COL item_subtitle  FORMAT A20  HEADING "Item Subtitle"
COL item_rating    FORMAT A6   HEADING "Item|Rating"
COL item_type      FORMAT A18   HEADING "Item|Type"
SELECT i.item_id
,      i.item_title
,      i.item_subtitle
,      i.item_rating
,      cl.common_lookup_meaning AS item_type
FROM   item i INNER JOIN common_lookup cl
ON     i.item_type = cl.common_lookup_id
WHERE  cl.common_lookup_type = 'BLU-RAY';

      You should see the same result as you saw before because the UPDATE statement should raise an exception and disallow any change to the item table. As noted earlier, the item_id values may differ based on the number of tests you perform to get your code working.

       Item						Item   Item
 ID # Item Title	   Item Subtitle	Rating Type
----- -------------------- -------------------- ------ ------------------
 1094 Bourne					PG-13  Blu-ray
 1095 Bourne Legacy				PG-13  Blu-ray
 1096 Star Wars 	   The Force Awakens	PG-13  Blu-ray

      Now, you should query the logger table to see what transactions have occurred. You can use the following query:

      COL logger_id       FORMAT 9999 HEADING "Logger|ID #"
COL old_item_id     FORMAT 9999 HEADING "Old|Item|ID #"
COL old_item_title  FORMAT A20  HEADING "Old Item Title"
COL new_item_id     FORMAT 9999 HEADING "New|Item|ID #"
COL new_item_title  FORMAT A30  HEADING "New Item Title"
SELECT l.logger_id
,      l.old_item_id
,      l.old_item_title
,      l.new_item_id
,      l.new_item_title
FROM   logger l;

      The results should look like the following:

      	 Old			    New
Logger	Item			   Item
  ID #	ID # Old Item Title	   ID # New Item Title
------ ----- -------------------- ----- ------------------------------
     1	1014 Brave Heart	   1014 Brave Heart
     2				   1035 King Arthur
     3	1035 King Arthur	   1035 King Arthur-Changed
     4	1035 King Arthur-Deleted
     5				   1095 Bourne Legacy:
     6				   1096 Star Wars: The Force Awakens
     7	1096 Star Wars		   1096 Star Wars: The Force Awakens

    • Delete the Star Wars row that you previously inserted into the item table. You can test your deleting event trigger with the following values (note that this is true whether you embedded the deleting event in the item_trig trigger or wrote a separate item_delete_trig trigger to manage the deletion of a record.

      • Use the following query to verify the values in the item table after the deletion of the row:

      COL item_id        FORMAT 9999 HEADING "Item|ID #"
COL item_title     FORMAT A20  HEADING "Item Title"
COL item_subtitle  FORMAT A20  HEADING "Item Subtitle"
COL item_rating    FORMAT A6   HEADING "Item|Rating"
COL item_type      FORMAT A18   HEADING "Item|Type"
SELECT i.item_id
,      i.item_title
,      i.item_subtitle
,      i.item_rating
,      cl.common_lookup_meaning AS item_type
FROM   item i INNER JOIN common_lookup cl
ON     i.item_type = cl.common_lookup_id
WHERE  cl.common_lookup_type = 'BLU-RAY';

      The results should look like the following, except for the variation in the item_id column values:

       Item						Item   Item
 ID # Item Title	   Item Subtitle	Rating Type
----- -------------------- -------------------- ------ ------------------
 1094 Bourne					PG-13  Blu-ray
 1095 Bourne Legacy				PG-13  Blu-ray

      Query the logger table with the familiar query one more time to see what transactions you have logged. The results show you the results of the INSERT, UPDATE, and DELETE testing:

      COL logger_id       FORMAT 9999 HEADING "Logger|ID #"
COL old_item_id     FORMAT 9999 HEADING "Old|Item|ID #"
COL old_item_title  FORMAT A20  HEADING "Old Item Title"
COL new_item_id     FORMAT 9999 HEADING "New|Item|ID #"
COL new_item_title  FORMAT A30  HEADING "New Item Title"
SELECT l.logger_id
,      l.old_item_id
,      l.old_item_title
,      l.new_item_id
,      l.new_item_title
FROM   logger l;

      The results should look like the following:

      	 Old			    New
Logger	Item			   Item
  ID #	ID # Old Item Title	   ID # New Item Title
------ ----- -------------------- ----- ------------------------------
     1	1014 Brave Heart	   1014 Brave Heart
     2				   1035 King Arthur
     3	1035 King Arthur	   1035 King Arthur-Changed
     4	1035 King Arthur-Deleted
     5				   1095 Bourne Legacy:
     6				   1096 Star Wars: The Force Awakens
     7	1096 Star Wars		   1096 Star Wars: The Force Awakens
     8	1096 Star Wars

    At this point, you have completed the INSERT, UPDATE, and DELETE statement testing.

    Written by michaelmclaughlin

    November 27th, 2016 at 11:23 pm

    Posted in