Skip Headers

Oracle9i Application Developer's Guide - Fundamentals
Release 2 (9.2)

Part Number A96590-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

16
Working With System Events

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:

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:

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.

Table 16-2 contains a list of system manager events.

Table 16-2 System Manager Events  
Event When Fired? Conditions Restrictions Transaction Attribute Functions
STARTUP

When the database is opened.

None allowed

No database operations allowed in the trigger.

Return status ignored.

Starts a separate transaction and commits it after firing the triggers.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
SHUTDOWN

Just before the server starts the shutdown of an instance.

This lets the cartridge shutdown completely. For abnormal instance shutdown, this event may not be fired.

None allowed

No database operations allowed in the trigger.

Return status ignored.

Starts a separate transaction and commits it after firing the triggers.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
SERVERERROR

When the error eno occurs. If no condition is given, then this event fires when any error occurs.

Does not apply to ORA-1034, ORA-1403, ORA-1422, ORA-1423, and ORA-4030 conditions, because they are not true errors or are too serious to continue processing.

ERRNO = eno

Depends on the error.

Return status ignored.

Starts a separate transaction and commits it after firing the triggers.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_server_error
ora_is_servererro
r
space_error_info

Client Events

Client events are the events related to user logon/logoff, DML, and DDL operations. For example:

CREATE OR REPLACE TRIGGER On_Logon  
  AFTER LOGON  
  ON The_user.Schema  
BEGIN  
  Do_Something;  
END;  

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

Table 16-3 contains a list of client events.

Table 16-3 Client Events  
Event When Fired? Attribute Functions
BEFORE ALTER

AFTER ALTER

When a catalog object is altered.

ora_sysevent 
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type 
ora_dict_obj_name
ora_dict_obj_owner
ora_des_encrypted_password 
(for ALTER USER events)
ora_is_alter_column, 
ora_is_drop_column (for ALTER 
TABLE events)
BEFORE DROP

AFTER DROP

When a catalog object is dropped.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type
ora_dict_obj_name
ora_dict_obj_owner
BEFORE ANALYZE

AFTER ANALYZE

When an analyze statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
BEFORE ASSOCIATE STATISTICS

AFTER ASSOCIATE STATISTICS

When an associate statistics statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_dict_obj_name_list
ora_dict_obj_owner_list
BEFORE AUDIT
AFTER AUDIT

BEFORE NOAUDIT
AFTER NOAUDIT

When an audit or noaudit statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
BEFORE COMMENT

AFTER COMMENT

When an object is commented

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
BEFORE CREATE 

AFTER CREATE

When a catalog object is created.

ora_sysevent 
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type 
ora_dict_obj_name
ora_dict_obj_owner
ora_is_creating_nested_table 
(for CREATE TABLE events)
BEFORE DDL

AFTER DDL

When most SQL DDL statements are issued. Not fired for ALTER DATABASE, CREATE CONTROLFILE, CREATE DATABASE, and DDL issued through the PL/SQL procedure interface, such as creating an advanced queue.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
BEFORE DISASSOCIATE 
STATISTICS

AFTER DISASSOCIATE STATISTICS

When a disassociate statistics statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_dict_obj_name_list
ora_dict_obj_owner_list
BEFORE GRANT

AFTER GRANT

When a grant statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_grantee
ora_with_grant_option
ora_privileges
BEFORE LOGOFF

At the start of a user logoff

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
AFTER LOGON

After a successful logon of a user.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_client_ip_address
BEFORE RENAME

AFTER RENAME

When a rename statement is issued.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_owner
ora_dict_obj_type
BEFORE REVOKE

AFTER REVOKE

When a revoke statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_revokee
ora_privileges
AFTER SUSPEND

After a SQL statement is suspended because of an out-of-space condition. The trigger should correct the condition so the statement can be resumed.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_server_error
ora_is_servererror
space_error_info
BEFORE TRUNCATE

AFTER TRUNCATE

When an object is truncated

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner

Go to previous page Go to next page
Oracle
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback