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:
You can now define triggers to respond to such schema-level events as CREATE, DROP, and ALTER.
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.
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);
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.