6.1 Triggers on Nested Table View Columns

Oracle8 Release 8.0 allowed developers to create INSTEAD OF triggers, which could then be applied to any view but were especially handy with object views and any inherently unmodifiable view. Oracle8i expands further the usefulness of triggers by allowing you to define a trigger to fire when one or more attributes of a nested table view column are modified. This feature allows you to change an element of a collection synthesized using the CAST...MULTISET operation.

Figure 6.1 illustrates the ability of the nested table trigger to zoom inside the outer table structure and respond to changes made to the nested table.

Figure 6.1: Nested table trigger fired by change to nested table

Let's walk through an example illustrating the steps you would take to achieve this effect (use the nesttrig.sql file on the companion disk to run all of the following statements in sequence). I'll use membership in the National Rifle Association as an example here. Using the NRA slogan (but not necessarily the belief of all its members) "Guns don't kill people, people kill people" and a little sleight of hand, I've generated the following two relational tables:

/* Filename on companion disk: nesttrig.sql */
CREATE TABLE nra_members (   
   person_id INTEGER,                 
   last_name VARCHAR2(100),           
   first_name VARCHAR2(20),  );
   age NUMBER);

CREATE TABLE non_killers (
   person_id INTEGER,
   gun_name VARCHAR2(75)

I want to build an object view over these two tables that implements the non_killers table as a nested table column. First I must create two object types (a bug in Oracle 8.1.5 requires that I create a table of objects, rather than scalars, for the nested table column to function properly):

CREATE OR REPLACE TYPE gun_name_ot AS OBJECT (
   gun_name VARCHAR2(75)
);
/
CREATE OR REPLACE TYPE non_killer_t
    AS TABLE OF gun_name_ot;
/
CREATE OR REPLACE TYPE nra_member_t 
AS OBJECT (
   person_id INTEGER,
   last_name VARCHAR2(100),
   first_name VARCHAR2(12),
   age INTEGER,
   gun_names non_killer_t
);
/

Now I can create my object view, using CAST...MULTISET to convert my normalized relational table into a nested table column:

CREATE OR REPLACE VIEW nra_members_ov 
   OF nra_member_t
   WITH OBJECT OID (person_id)
AS
   SELECT luvguns.person_id, 
          luvguns.last_name, 
          luvguns.first_name, 
          luvguns.age,
          CAST (MULTISET (
             SELECT gun_name
               FROM non_killers bestfriend
              WHERE bestfriend.person_id = 
                    bestfriend.person_id)
            AS non_killer_t)
FROM nra_members luvguns;

Once I have this view in place, I also need to provide INSTEAD OF triggers to allow a user to update, insert, or delete through the view, making the illusion of my use of objects complete. These capabilities have been present since Oracle 8.0, so I will not repeat the code here (see the nesttrig.sql trigger named nra_members_nest_insert for an example). I will, instead, focus on the new nested table trigger capability.

Here is the trigger definition; notice that the only difference is the line in bold, indicating that the trigger applies only to the specified nested table:

/* Filename on companion disk: nesttrig.sql */
CREATE OR REPLACE TRIGGER nra_members_gun_rename
INSTEAD OF INSERT OR UPDATE

ON NESTED TABLE gun_names OF nra_members_ov
BEGIN
   IF INSERTING 
   THEN
      INSERT INTO non_killers (person_id, gun_name)
      VALUES (:PARENT.person_id, :NEW.gun_name);
   END IF;
   
   IF UPDATING
   THEN
      UPDATE non_killers
         SET gun_name = :NEW.gun_name
       WHERE gun_name = :OLD.gun_name
         AND person_id = :PARENT.person_id;
   END IF;
END;
/

Let's try it out. I inserted Charlton Heston (national spokesperson of the NRA as of May 1999) and hypothetical information about his guns into the two tables:

INSERT INTO nra_members (
   person_id, last_name, first_name, age)
VALUES (100, 'HESTON', 'CHARLTON', 70);

INSERT INTO non_killers (
   person_id, gun_name)
   VALUES (100, 'COLT-45');
INSERT INTO non_killers (
   person_id, gun_name)
   VALUES (100, 'M-16');
INSERT INTO non_killers (
   person_id, gun_name)
   VALUES (100, 'DOUBLE-BARRELED JUSTICE');

Suppose then that Charlton Heston undergoes a sea change in philosophy. To demonstrate his new principles, he renames each of his guns, stored in that nested table. Here is the update in a single statement:

UPDATE TABLE (SELECT gun_names
                FROM nra_members_ov
               WHERE person_id = 100)
   SET gun_name =
         DECODE (gun_name,
           'COLT-45', 'Pretty Pony',
           'M-16', 'I Love Mom',
           'DOUBLE-BARRELED JUSTICE', 'Peace on Earth',
           gun_name);

I use the TABLE...SELECT combination to extract just the nested table column from the object view. The SET clause then applies to the attributes of that nested table.

TIP: Tests indicate that these new nested table triggers will fire only when the DML action occurs on the nested table column, and not on any other columns in the table.