There are 14 DDL/client event triggers and these can be created at the database level and will execute for all schemas, or these can be created at the schema level and will execute only for the schema it is created for. When a trigger is created at the schema level, the trigger is created in the schema specified and executes only for that schema.
This provides a great deal of flexibility depending on your environment and what you want to monitor or respond to. The 14 DDL/client 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 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 |
The new triggers are ideal for DBAs to build mechanisms based on certain events. When the database is started, the objects that need to be pinned can now be moved from the startup SQL script to the STARTUP trigger. When the database is shut down, statistics scripts can be executed to log information into monitoring tables with the SHUTDOWN trigger. Error trapping can be enhanced with the SERVERERROR trigger. Capturing user connect time can be handled through the LOGON and LOGOFF triggers. An object audit trail can be created through the CREATE, ALTER, and DROP triggers.
[@more@]