There are six database system event triggers. The six database system event triggers are outlined below, along with a description and the event attributes that are set for each event.

Database Trigger	BEFORE/AFTER Execution	Description	Attribute Event
LOGOFF	BEFORE	Executed when a user logs off, at the start of the logoff process	ora_sysevent ora_login_user ora_instance_num ora_database_name
LOGON	AFTER	Executed when a user logs into the database, after a successful login of the user	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_client_ip_address
STARTUP	AFTER	Executed when the database is opened; starts a separate transaction and commits after this trigger is complete	ora_sysevent ora_login_user ora_instance_num ora_database_name
SHUTDOWN	BEFORE	Executed when the instance is shutdown; prior to the shutdown of the instance process; not always executed on abnormal shutdown; starts a separate transaction and commits after this trigger is complete	ora_sysevent ora_login_user ora_instance_num ora_database_name
SERVERERROR	AFTER	Executes when an Oracle error occurs (can check for a specific error number to only execute for (errno=eno)); does not execute for certain errors (1034, 1403, 1422, 1423, 4030); starts a separate transaction and commits after this trigger is complete	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_server_error ora_is_servererror space_error_info
SUSPEND	AFTER	Executed whenever a server error causes a transaction to be suspended (example: out-of-space error)	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_server_error ora_is_servererror space_error_info

The startup and shutdown triggers can only be created at the database level. The other four database system events can be created at the database or schema levels. The STARTUP trigger returns a success, even if the trigger fails.

The SERVERERROR trigger does not execute when the following Oracle errors are returned:

· ORA-01403: data not found

· ORA-01422: exact fetch returns more than requested number of rows

· ORA-01423: error encountered while checking for extra rows in exact fetch

· ORA-01034: ORACLE not available

· ORA-04030: out of process memory

For these triggers, Oracle opens an autonomous transaction scope, fires the trigger, and commits any separate transaction.

[@more@]

Database Triggers and event attributes--Database System Events

相關文章