Oracle PL/SQL Programming: Guide to Oracle8i Features

Oracle8 i allows you to define triggers to respond to database-level events (also known as system-level events), including the following:

• Logon to and logoff from a schema

• Startup and shutdown of the database

• Response to a server error

Here is the syntax for these triggers:

CREATE [ OR REPLACE ] TRIGGER trigger_name { BEFORE AFTER } { SERVERERROR LOGON LOGOFF STARTUP SHUTDOWN } ON DATABASE BEGIN pl/sql_statements END;

To create a trigger at the database level, you must have the ADMINISTER DATABASE TRIGGER system privilege. You will also need privileges to access any of the external references in the trigger's PL/SQL code.

Table 6.1 lists the different database-level events on which you may define a trigger.

Table 6.1. Database-Level Events for Trigger Definitions

Each database-level event has an associated with it a set of attributes. These attributes are actually functions owned by SYS that return the values of characteristics relevant to the event. Table 6.2 lists the current set of attributes.

Table 6.2. Attributes for Database-Level Events

Table 6.3 lists the restrictions and attributes for each database-level event.

Table 6.3. Restrictions and Attributes for Database-Level Events

Keep in mind the following rules when working with these triggers:

• Whenever a database-level event trigger fires, Oracle opens an autonomous transaction, fires the trigger, and commits any DML in the trigger logic independently of the existing user transaction.

• When defining LOGON, STARTUP, and SERVERERROR triggers, you can only specify the AFTER context. If you specify BEFORE, you will get this error:

ORA-30500: database open triggers and server error triggers cannot have BEFORE type

• When defining LOGOFF and SHUTDOWN triggers, you can only specify the BEFORE context. If you specify AFTER, you will get this error:

ORA-30509: client logoff triggers cannot have AFTER type

• You cannot define AFTER STARTUP and BEFORE SHUTDOWN triggers for a schema; these apply only to DATABASE.

• Calls to DBMS_OUTPUT.PUT_LINE do not generate any visible output in the current session. If you want to obtain a record of actions that occurred, you will need to write information to a database table, database pipe, or operating system file. You could also use DBMS_AQ.ENQUEUE to place a message in a queue.

• A SERVERERROR trigger will not fire for any of the following errors:

ORA-01403: no data found ORA-01422: exact fetch returns more than requested number of rows ORA-04030: out of process memory when trying to allocate nnn bytes ORA-01034: ORACLE not available ORA-01007: variable not in select list

6.2.1 Examples of Database-Level Event Triggers

There are many different ways you can use database-level event triggers to manage your database. The following examples offer some simple models from which you can build your own, more complex variations.

6.2.1.1 Pinning packages on database startup

A common requirement for a high-performance database is to pin one or more packages into shared memory. By pinning your code, it is exempted from the least recently used algorithm and will never be aged out of the shared pool area.

Before the existence of STARTUP triggers, the DBA would have to set up a script to run after the database was started. Now, I can create a STARTUP trigger like the following:

/* Filename on companion disk: startup.trg */ CREATE OR REPLACE TRIGGER pin_code_on_startup AFTER STARTUP ON DATABASE BEGIN /* Pin the default packages of PL/SQL to improve runtime performance. */ DBMS_SHARED_POOL.KEEP ( 'SYS.STANDARD', 'P'); DBMS_SHARED_POOL.KEEP ( 'SYS.DBMS_STANDARD', 'P'); END; /

With this trigger in place, I guarantee that all of my large code elements (including cursors , types, and triggers) and even my sequences are cached in the shared pool until the instance goes down.

If you are going to be pinning objects, you should consider building an infrastructure table to store the names of elements you want pinned. Then instead of hard-coding your pin commands in a trigger (as just shown), you could run a procedure that reads through the table and pins each element found. This approach is explained in detail in Chapter 12 of Oracle Built-in Packages .

6.2.1.2 Tracking logins to the database

Suppose that I want to keep track of logins to my database instance. To make this information available in a structured fashion, I am going to send my login information to a queue using the Oracle Advanced Queuing (AQ) facility.

These steps are illustrated in Figure 6.2. You will find all of the AQ- related steps in the aq.sql script on the companion disk; I'll concentrate on the trigger-related components here.

Let's start in reverse. Here is the LOGON trigger:

/* Filename on companion disk: aq.sql */ CREATE OR REPLACE TRIGGER publish_logon AFTER LOGON ON DATABASE BEGIN oraevent.put ('This is a logon'); END; /

As you can see, there isn't much to it: every time someone logs in to the database, we'll put a message in the queue with a call to oraevent.put. So let's take a look at that procedure:

PROCEDURE oraevent.put (details_in IN VARCHAR2) IS q_opts DBMS_AQ.ENQUEUE_OPTIONS_T; msg_props DBMS_AQ.MESSAGE_PROPERTIES_T; msg_handle RAW(16); event_l Event_t; BEGIN /* Setting visibility to IMMEDIATE will force the queue to "commit" before the client transaction commits. */ q_opts.visibility := DBMS_AQ.IMMEDIATE; event_l := Event_t.make(details_in); DBMS_AQ.ENQUEUE(queue_name => 'aqadmin.loginQ', enqueue_options => q_opts, message_properties => msg_props, payload => event_l, msgid => msg_handle); END;

This procedure calls DMBS_AQ.ENQUEUE to place a message, or payload , in the loginQ queue. Each message is an object of type Event_t, defined as follows :

CREATE TYPE Event_t AS OBJECT ( eventname VARCHAR2(64), details VARCHAR2(512), username VARCHAR2(30), timestamp DATE, STATIC FUNCTION make ( details_in IN VARCHAR2) RETURN Event_t ); / CREATE OR REPLACE TYPE BODY Event_t AS STATIC FUNCTION make ( details_in IN VARCHAR2) RETURN Event_t IS BEGIN RETURN Event_t( SYSEVENT, details_in, LOGIN_USER, SYSDATE); END; END; /

The RETURN statement relies on two of the event-related functions, SYSEVENT and LOGIN_USER, to record characteristics at the time of login.

Run the aq.sql script to create all elements and then test the code by spawning a second SQL*Plus session to watch or dequeue the login messages.

6.2.1.3 Trapping system errors

The SERVERERROR event will prove to be a very handy mechanism. You can define it at the database level, which means that any error raised in any schema will be interceptable through the trigger. You can also define a trigger for this event at the schema level, limiting the scope of firing of the trigger.

Suppose that you simply want to keep track of errors raised in a particular application running on your instance. When an error occurs, you write a message to a database pipe. Another session (running asynchronously to the application users) can then wake up and dump the contents of the pipe and examine the errors.

To facilitate that process, I have created and included on the disk a package called watch. Stored in watch.pkg on the companion disk, this package allows you to watch actions and then direct a message constructed for that action to either the screen via DBMS_OUTPUT or to a pipe via DBMS_PIPE.

Using the watch package, I first create a utility procedure that I will call in my SERVERERROR triggers:

/* Filename on companion disk: serverr.trg */ CREATE OR REPLACE PROCEDURE pipe_error ( context IN VARCHAR2, msg IN VARCHAR2) IS BEGIN /* Send the information to a pipe. */ watch.topipe; /* Retrieve all system event attributes. */ watch.action (context ' trap_error', msg); END; /

I can then define a trigger at the database level that displays all of the attributes available from within this trigger:

CREATE OR REPLACE TRIGGER trap_error AFTER SERVERERROR ON DATABASE BEGIN pipe_error ('DATABASE', sysevent '-' instance_num '-' database_name '-' SQLCODE '-' server_error (1) '-' login_user ); END; /

I will also define a SERVERERROR trigger for the SCOTT schema, so we can explore the way multiple triggers of the same type fire:

CREATE OR REPLACE TRIGGER scott_trap_error AFTER SERVERERROR ON SCOTT.SCHEMA BEGIN pipe_error (login_user, sysevent '-' instance_num '-' database_name '-' SQLCODE '-' server_error (1) '-' login_user ); END; /

To test these triggers, I created the following script:

/* Filename on companion disk: serverr.tst */ DECLARE exc EXCEPTION; PRAGMA EXCEPTION_INIT (exc, -&1); BEGIN RAISE exc; END; /

Now I will connect as SCOTT and run the script emulating a date-related error:

SQL> @serverr.tst 1855 * ERROR at line 1: ORA-01855: AM/A.M. or PM/P.M. required

If the triggers fired, the watch pipe should contain some information. I can dump the contents of the pipe with a call to watch.show:

SQL> exec watch.show Contents of WATCH Trace: ***WATCHing at: June 1, 1999 12:54:14 Context: SCOTT trap_error Message: SERVERERROR-1-ORACLE-0-1855-SCOTT ***WATCHing at: June 1, 1999 12:54:14 Context: DATABASE trap_error Message: SERVERERROR-1-ORACLE-0-1855-SCOTT

We learn a few things from this execution:

• The schema-level trigger fires before the database-level trigger.

• By the time the SERVERERROR trigger fires, the SQLCODE function does not return the error that was raised. Instead, it returns 0. You must rely on the SERVER_ERROR function to retrieve the error at the top of the error stack.

Now I will connect to the DEMO account in my Oracle 8.1.5 instance and run this script for a different error:

SQL> connect demo/demo. SQL> @serverr.tst 1652 * ERROR at line 1: ORA-01652: unable to extend temp segment by nnn in tablespace

When I take a look at my pipe contents, I have only one entry:

SQL> exec watch.show Contents of WATCH Trace: ***WATCHing at: June 1, 1999 13:01:38 Context: DATABASE trap_error Message: SERVERERROR-1-ORACLE-0-1652-DEMO

And that is because I did not create a SERVERERROR trigger in the DEMO schema; only the database-level trigger is fired.

6.2.1.4 Checking for specific errors

One other useful technique in this type of trigger is to check for a specific error and then take special action in that instance. There are two ways to do this:

• Use the trigger WHEN clause to restrict the execution of the trigger logic for a specific error.

• Use the IS_SERVERERROR function to determine if a specific error has been raised anywhere within the error call stack and then take action.

Let's see how each approach would work. First, suppose that I want to qualify my SERVERERROR trigger at the database level to fire only when the ORA-02292 error occurs ("integrity constraint ( constant name ) violated - child record found").

I can create my trigger as follows:

/* Filename on companion disk: serverr2.trg */ CREATE OR REPLACE TRIGGER scott_trap_parent_key AFTER SERVERERROR ON DATABASE WHEN (SYS.SERVER_ERROR(1) = 2292) BEGIN pipe_error ( 'DATABASE trap parent key', 'Invalid attempt to delete primary key by ' LOGIN_USER); END; /

My call to SYS.SERVER_ERROR(1) retrieves the error at the top of the error stack. There are two things to note in this procedure:

• If I do not qualify the call to SERVER_ERROR with its owner, SYS, I get the following error:

AFTER SERVERERROR ON DATABASE * ERROR at line 2: ORA-00942: table or view does not exist

• I must specify the error number as a positive, not negative, integer value. Otherwise, no match will be detected .

Once the trigger is defined, I test it by trying to delete a row from the dept table whose parent key is referenced by an employee:

SQL> DELETE FROM dept WHERE deptno=10; * ERROR at line 1: ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found

I can now see what information was sent to my pipe within the trigger as follows:

SQL> exec watch.show Contents of WATCH Trace: ***WATCHing at: June 2, 1999 16:27:09 Context: DATABASE trap parent key trap_error Message: Invalid attempt to delete primary key by SCOTT

Here I will demonstrate the IS_SERVERERROR approach with a modified version of the database-level trigger:

/* Filename on companion disk: serverr.trg */ CREATE OR REPLACE TRIGGER trap_error AFTER SERVERERROR ON DATABASE BEGIN /* Same tracking as before. */ pipe_error ('DATABASE', ...); IF IS_SERVERERROR (1652) -- POSITIVE NUMBER REQUIRED! THEN /* Add a file to the tablespace... just a dummy entry for the book. */ pipe_error ('DATABASE', 'Add to tablespace'); END IF; END; /

And now if the ORA-01652 error is raised anywhere in the error stack from the SCOTT schema, I see three entries in the error pipe:

SQL> DECLARE 2 exc EXCEPTION; 3 PRAGMA EXCEPTION_INIT (exc, -1652); 4 BEGIN 5 RAISE exc; 6 EXCEPTION 7 WHEN OTHERS THEN 8 RAISE VALUE_ERROR; 9 END; 10 / * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-01652: unable to extend temp segment by nnn in tablespace SQL> exec watch.show Contents of WATCH Trace: ***WATCHing at: June 3, 1999 09:47:05 Context: SCOTT trap_error Message: SERVERERROR-1-ORACLE-0-6502-SCOTT ***WATCHing at: June 3, 1999 09:47:05 Context: DATABASE trap_error Message: SERVERERROR-1-ORACLE-0-6502-SCOTT ***WATCHing at: June 3, 1999 09:47:05 Context: DATABASE trap_error Message: Add to tablespace