Database
Triggers and event attributes
Introduction
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 |
Database
Trigger |
BEFORE/AFTER
Execution |
Description |
Attribute Events |
ALTER |
BEFORE/AFTER |
Executed when object 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) |
DROP |
BEFORE/AFTER |
Executed when 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 |
ANALYZE |
BEFORE/AFTER |
Executed when the analyze command is executed |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner |
ASSOCIATE STATISTICS |
BEFORE/AFTER |
Executed when the associate statistics command is executed |
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 |
AUDIT/NOAUDIT |
BEFORE/AFTER |
Executed when the audit or noaudit command is executed |
ora_sysevent ora_login_user ora_instance_num ora_database_name |
COMMENT |
BEFORE/AFTER |
Executed when the comment command is executed |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner |
CREATE |
BEFORE/AFTER |
Executed when an 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) |
DDL | BEFORE/AFTER | Executed when SQL DDL commands are executed (not executed when and ALTER/CREATE DATABASE, CREATE CONTROLFILE, or DDL issued through the PL/SQL procedure interface) | ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner |
DISASSOCIATE STATISTICS |
BEFORE/AFTER |
Executed when the disassociate statistics command is executed |
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 |
GRANT |
BEFORE/AFTER |
Executed when the grant command is executed |
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 |
RENAME |
BEFORE/AFTER |
Executed when the rename command is executed |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_owner ora_dict_obj_type |
REVOKE |
BEFORE/AFTER |
Executed when the revoke command is executed |
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 |
TRUNCATE |
BEFORE/AFTER |
Execute when a table 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 |
Attribute Event | Data Type | Description |
ora_client_ip_address | VARCHAR2 | Provides the IP address of the client machine when using TCP/IP |
ora_database_name | VARCHAR2(50) | Provides the database name |
ora_des_encrypted_password | VARCHAR2 | Provides the DES encrypted password of the user being created or altered |
ora_dict_obj_name | VARCHAR(30) | Provides the object name of the object being manipulated |
ora_dict_obj_name_list (name_list OUT ora_name_list_t) |
BINARY_INTEGER | Provides a list of object names being manipulated |
ora_dict_obj_owner | VARCHAR(30) | Provides the owner of the object being manipulated |
ora_dict_obj_owner_list(owner_list OUT ora_name_list_t) | BINARY_INTEGER | Provides the owners of the objects being manipulated |
ora_dict_obj_type | VARCHAR(20) | Provides the type of object being manipulated |
ora_grantee( user_list OUT ora_name_list_t) |
BINARY_INTEGER | Provides the number of grantees |
ora_instance_num | NUMBER | Provides the instance number. |
ora_is_alter_column( column_name IN VARCHAR2) |
BOOLEAN | Provides a return value of TRUE if the specified column is altered |
ora_is_creating_nested_table | BOOLEAN | Provides a return value of TRUE if the current event is creating a nested table |
ora_is_drop_column( column_name IN VARCHAR2) |
BOOLEAN | Provides a return value of TRUE if the specified column is dropped |
ora_is_servererror | BOOLEAN | Provides a return value of TRUE is the error specified is on the error stack |
ora_login_user | VARCHAR2(30) | Provides the login schema |
ora_partition_pos | BINARY_INTEGER | Provides the position in a CREATE TABLE command where the partition clause can be inserted when using the INSTEAD OF trigger |
ora_privilege_list( privilege_list OUT ora_name_list_t) |
BINARY_INTEGER | Provides the list of privileges being granted or revoked |
ora_revokee (user_list OUT ora_name_list_t) |
BINARY_INTEGER | Provides a list of the revokees of the revoke command |
ora_server_error | NUMBER | Provides the error on the error stack for the position specified in the stack (1 meaning the top of the stack) |
ora_server_error_depth | BINARY_INTEGER | Provides the total number of errors on the error stack |
ora_server_error_msg (position in binary_integer) |
VARCHAR2 | Provides the error on the error stack for the position specified in the stack (1 meaning the top of the stack) |
ora_server_error_num_params (position in binary_integer) |
BINARY_INTEGER | Provides the number of strings that have been substituted into the error message on the error stack for the position specified in the stack (1 meaning the top of the stack) |
ora_server_error_param (position in binary_integer, param in binary_integer) |
VARCHAR2 | Provides the matching substitution value in the error message for the parameter number specified in conjunction with the position specified on the stack ( 1 meaning the top of the stack) |
ora_sql_txt (sql_text out ora_name_list_t) |
BINARY_INTEGER | Provides the SQL statement of the statement that caused the trigger to execute (if the statement is lengthy, it will separate it into multiple PL/SQL table elements); the value returned specifies the number of elements |
ora_sysevent | VARCHAR2(20) | Provides the system or client event that caused the trigger to execute |
ora_with_grant_option | BOOLEAN | Provides a return value of TRUE if the privileges are granted with the grant option |
space_error_info( error_number OUT NUMBER, error_type OUT VARCHAR2, object_owner OUT VARCHAR2, table_space_name OUT VARCHAR2, object_name OUT VARCHAR2, sub_object_name OUT VARCHAR2) |
BOOLEAN |
Provides a return value of true if the error is related
to an out-of-space error and provides the object information of the
object with the error |