14
Working With System Events

System events, like LOGON and SHUTDOWN, provide a mechanism for tracking system changes. With Oracle, this tracking can be combined with database event notification. Database event notification 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:
Chapter 13, "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

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.

Table 14-1 System Defined Event Attributes

Attribute	Type	Description	Example
sysevent	VARCHAR2(20)	System event firing the trigger: Event name is same as that in the syntax.	INSERT INTO event_table (sys.sysevent);
instance_num	NUMBER	Instance number.	IF (instance_num = 1) THEN INSERT INTO event_table ('1'); END IF;
database_name	VARCHAR2(50)	Database name.	DECLARE db_name VARCHAR2(50); BEGIN db_name := database_name; END;
server_error	NUMBER	Given a position(1 for top of stack), it returns the error number at that position on error stack	INSERT INTO event_table ('top stack error ' \|\| sys.server_error(1));
is_servererror	BOOLEAN	Returns `TRUE` if given error is on error stack, `FALSE` otherwise.	IF (is_servererror(error_number)) THEN INSERT INTO event_table ('Server error!!'); END IF;
login_user	VARCHAR2(30)	Login user name.	SELECT sys.login_user FROM dual;
dictionary_obj_ type	VARCHAR(20)	Type of the dictionary object on which the DDL operation occurred.	INSERT INTO event_table ('This object is a ' \|\| sys.dictionary_obj_ type);
dictionary_obj_ name	VARCHAR(30)	Name of the dictionary object on which the DDL operation occurred.	INSERT INTO event_table ('Changed object is ' \|\| sys.dictionary_obj_ name');
dictionary_obj_ owner	VARCHAR(30)	Owner of the dictionary object on which the DDL operation occurred.	INSERT INTO event_table ('object owner is' \|\| sys.dictionary_ obj.owner');
des_encrypted_ password	VARCHAR(2)	The DES encrypted password of the user being created or altered.	IF (dictionary_obj_type = 'USER') THEN INSERT INTO event_table (sys.des_encypted_password); END IF;

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 14-2 contains a list of resource manager events.

Table 14-2 Resource Manager Events

Event Description Conditions Restrictions Transaction Attributes

Event	Description	Conditions	Restrictions	Transaction	Attributes
STARTUP	This event is fired when the database is open.	None allowed	No database operations allowed Return status ignored.	Starts a separate transaction and commits it after firing the triggers.	sysevent login_user instance_num database_name
SHUTDOWN	This event is fired 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.	sysevent login_user instance_num database_name
SERVERERROR	This event is fired when the error `eno` occurs. If no condition is given, then this event fires when any error occurs.	`ERRNO` = `eno`	Depends on the error Return status ignored.	Starts a separate transaction and commits it after firing the triggers.	sysevent login_user instance_num database_name server_error is_servererror

STARTUP

This event is fired when the database is open.

None allowed

No database operations allowed

Return status ignored.

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

sysevent
login_user
instance_num 
database_name

SHUTDOWN

This event is fired 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.

sysevent
login_user 
instance_num 
database_name

SERVERERROR

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

ERRNO = eno

Depends on the error

Return status ignored.

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

sysevent
login_user
instance_num 
database_name 
server_error 
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;

Table 14-3 contains a list of client events.

Table 14-3 Client Events

Event	Description	Conditions	Restrictions	Transaction	Attributes
LOGON	These events are fired after a successful logon of a user.	Simple conditions on `USERID`() and `USERNAME`()	None Return status ignored.	Starts a separate transaction and commits it after firing the triggers.	sysevent login_user instance_num database_name
LOGOFF	These events are fired at the start of a user logoff	Simple conditions on `USERID`() and `USERNAME`()	None Return status ignored.	Fires the triggers in the existing user transaction.	sysevent login_user instance_num database_name
BEFORE CREATE AFTER CREATE	These events are fired when a catalog object is created.	Simple conditions on the type and name of the object; functions like `USERID`() and `USERNAME`()	Cannot drop the object being created.	Fires the triggers in the existing user transaction.	sysevent login_user instance_num database_name dictionary_obj_type dictionary_obj_name dictionary_obj_owner
BEFORE ALTER AFTER ALTER	These events are fired when a catalog object is altered.	Simple conditions on the type and name of the object; functions like `USERID`() and `USERNAME`()	Cannot drop the object being altered.	Fire the triggers in the existing user transaction.	sysevent login_user instance_num database_name dictionary_obj_type dictionary_obj_name dictionary_obj_owner
DROP BEFORE DROP AFTER DROP	These events are fired when a catalog object is dropped.	Simple conditions on the type and name of the object; functions like `USERID`() and `USERNAME`()	Cannot alter the object being dropped.	Fire the triggers in the existing user transaction.	sysevent login_user instance_num database_name dictionary_obj_type dictionary_obj_name dictionary_obj_owner

14Working With System Events

Event Attribute Functions

Usage Notes

Table 14-1 System Defined Event Attributes

List of Events

Resource Manager Events

Table 14-2 Resource Manager Events

Client Events

Table 14-3 Client Events

14
Working With System Events