Oracle8i Application Developer's Guide - Fundamentals
Release 2 (8.1.6)

Part Number A76939-01

Library

Product

Contents

Index

Go to previous page Go to next page

13
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.

See Also:

To use the information in this chapter, you need to understand Chapter 12, "Using Triggers"

Event Attribute Functions

You can obtain certain event-specific attributes when a trigger is fired. These attributes can be used as standalone functions.

Usage Notes

List of Events

Resource Manager Events

Resource manager events are related to instance startup and shutdown.Triggers created on resource manager events must be associated with the database object.

Table 13-2 contains a list of resource manager events.

Table 13-2 Resource Manager Events
Event  When Fired?  Conditions  Restrictions  Transaction  Attribute Functions 
STARTUP
 

When the database is opened. 

None allowed 

No database operations allowed

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

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_
servererror
 

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 13-3 contains a list of client events.

Table 13-3 Client Events
Event  When Fired?  Attribute Functions 
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 LOGOFF
 

At the start of a user logoff 

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
 
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 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 an object is created. 

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
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 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
 
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-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index