New trigger capabilities

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 newly defined system events and by usingOracle Advanced Queuing (AQ) 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. The trigger syntax is extended to support system and other data events on a database or a schema. Trigger syntax also supports a CALL to a procedure as the trigger body.

You can now 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)

These are the new trigger features available in Oracle8i:

 

Triggers on nested table columns

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

 

Database-level event triggers

You can now define triggers to respond to suchsystem events as LOGON, DATABASE SHUTDOWN, and even SERVERERROR.

 

Schema-level event triggers

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

Chapter 18 covers these new trigger features, as well as the more traditional DML triggers with which you can define actions on INSERT, UPDATE, and DELETE statements.

1.4.1.6 Calling Java from PL/SQL

Java is a very powerful language, more robust in many ways than PL/SQL. Java also offers hundreds of classes that provide clean, easy-to-use application programming interfaces (APIs) to a wide range of functionality. In Oracle8i and above, you can now invoke Java Stored Procedures (which are, in reality, Java methods stored in the database) from within your PL/SQL applications. You do so by constructing a "wrapper" or PL/SQL layer that encapsulates a call to a Java method, thereby making it available to any environment that can invoke PL/SQL programs.

Here is an example of such a wrapper, allowing me to delete files from PL/SQL:

CREATE OR REPLACE PACKAGE xfileIS FUNCTION delete (file IN VARCHAR2) RETURN INTEGER AS LANGUAGE JAVA NAME 'JFile1.delete (java.lang.String) return int';END;

Java in the Oracle database is a big topic; Java programming all by itself is an even bigger topic. Complete treatment of either is outside the scope of this book, but Chapter 22 will give you all the information you need to leverage Java from within your PL/SQL programs.