DDL/Client Events

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@]

Database Triggers and event attributes--DDL/Client Events

相關文章