Lemur zaprasza
[Chapter 6] New Trigger Features in Oracle8iChapter 6 6. New Trigger Features in Oracle8iContents:Triggers on Nested Table View ColumnsDatabase-Level Event TriggersSchema-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:DML statements (DELETE, INSERT, and UPDATE) DDL events (e.g., CREATE, DROP, and ALTER)Database events (SERVERERROR, LOGON, LOGOFF, STARTUP, and SHUTDOWN) 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 triggersYou 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 tableLet'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.5.5 Analyzing the Impact of Bulk Operations6.2 Database-Level Event Triggers O'Reilly & Associates. All rights reserved. |