Oracle PL/SQL Programming Guide to Oracle 8i Features

Oracle PL/SQL Programming Guide to Oracle 8i FeaturesSearch this book
Previous: 5.5 Analyzing the Impact of Bulk OperationsChapter 6Next: 6.2 Database-Level Event Triggers
 

6. New Trigger Features in Oracle8i

Contents:
Triggers on Nested Table View Columns
Database-Level Event Triggers
Schema-Level Event Triggers

Oracle8i expands significantly the use of triggers to administer a database and publish information about events taking place within the database. By employing database triggers on the system events defined in Oracle8i, and using Oracle Advanced Queuing within those triggers, you can take advantage of the publish/subscribe capabilities of Oracle8i.

The database event publication feature allows applications to subscribe to database events just as they subscribe to messages from other applications. Trigger syntax is extended to support system and other data events on the database level or on a particular schema level. Trigger syntax also supports a CALL to a procedure as the trigger body.

You can now enable the publication of (i.e., define a programmatic trigger on) the following actions:

Here are the new trigger features available in Oracle8i:

Triggers on nested table columns

Use of the CAST...MULTISET operation allows you to trigger activity when only an attribute in a nested table column is modified.

Database-level (also known as system-level) event triggers

You can now define triggers to respond to such database-level events as LOGON, DATABASE SHUTDOWN, and even SERVERERROR.

Schema-level (also known as user-level) event triggers

You can now define triggers to respond to such schema-level events as CREATE, DROP, and ALTER.

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

Figure 6.1

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.


Previous: 5.5 Analyzing the Impact of Bulk OperationsOracle PL/SQL Programming Guide to Oracle 8i FeaturesNext: 6.2 Database-Level Event Triggers
5.5 Analyzing the Impact of Bulk OperationsBook Index6.2 Database-Level Event Triggers

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference