System events, like LOGON and SHUTDOWN, provide a mechanism for tracking system changes. Oracle lets you combine this tracking with database event notification, which provides a simple and elegant method of delivering asynchrononous messaging to an application.

This chapter includes descriptions of the various events on which triggers can be created. It also provides the list of event attribute functions. Topics include the following:

To use the information in this chapter, you need to understand Chapter 15, "Using Triggers". You access the event information inside a trigger body.

You can set up a flexible system for handling events using the Event panels in Oracle Enterprise Manager. This technique lets you detect more conditions than the system events in this chapter, and lets you set up actions such as invoking shell scripts.

Event Attribute Functions

When a trigger is fired, you can retrieve certain attributes about the event that fired the trigger. Each attribute is retrieved by a function call.

Notes:

To make these attributes available, you must first run the CATPROC.SQL script.

The trigger dictionary object maintains metadata about events that will be published and their corresponding attributes.

In earlier releases, these functions were accessed through the SYS package. We recommend you use these public synonyms whose names begin with ora_.

Table 16-1 System Defined Event Attributes

Attribute

Type

Description

Example

ora_client_ip_address

VARCHAR2

Returns the IP address of the client in a LOGON event, when the underlying protocol is TCP/IP

if (ora_sysevent = 'LOGON')
then addr :=
ora_client_ip_address;
end if;

Returns the SQL text of the triggering statement in the OUT parameter. If the statement is long, it is broken up into multiple PL/SQL table elements. The function return value specifies how many elements are in the PL/SQL table.

List of Database Events

System Events

System events are related to entire instances or schemas, not individual tables or rows. Triggers created on startup and shutdown events must be associated with the database instance. Triggers created on error and suspend events can be associated with either the database instance or a particular schema.

Client Events

The LOGON and LOGOFF events allow simple conditions on UID( ) and USER( ). All other events allow simple conditions on the type and name of the object, as well as functions like UID( ) and USER( ).

The LOGON event starts a separate transaction and commits it after firing the triggers. All other events fire the triggers in the existing user transaction.

The LOGON and LOGOFF events can operate on any objects. For all other events, the corresponding trigger cannot perform any DDL operations, such as DROP and ALTER, on the object that caused the event to be generated.

The DDL allowed inside these triggers is altering, creating, or dropping a table, creating a trigger, and compile operations.

If an event trigger becomes the target of a DDL operation (such as CREATE TRIGGER), it cannot be fired later during the same transaction