Monday, September 1, 2008

Trigger events

DML_event_clause

DELETE

Specify DELETE if you want the database to fire the trigger whenever a DELETE statement removes a row from the table or removes an element from a nested table.


INSERT

Specify INSERT if you want the database to fire the trigger whenever an INSERT statement adds a row to a table or adds an element to a nested table.


UPDATE

Specify UPDATE if you want the database to fire the trigger whenever an UPDATE statement changes a value in one of the columns specified after OF. If you omit OF, then the database fires the trigger whenever an UPDATE statement changes a value in any column of the table or nested table.

For an UPDATE trigger, you can specify object type, varray, and REF columns after OF to indicate that the trigger should be fired whenever an UPDATE statement changes a value in one of the columns. However, you cannot change the values of these columns in the body of the trigger itself.

ddl_event

ALTER

Specify ALTER to fire the trigger whenever an ALTER statement modifies a database object in the data dictionary.

ANALYZE

Specify ANALYZE to fire the trigger whenever the database collects or deletes statistics or validates the structure of a database object.


ASSOCIATE STATISTICS

Specify ASSOCIATE STATISTICS to fire the trigger whenever the database associates a statistics type with a database object.


AUDIT

Specify AUDIT to fire the trigger whenever the database tracks the occurrence of a SQL statement or tracks operations on a schema object.


COMMENT

Specify COMMENT to fire the trigger whenever a comment on a database object is added to the data dictionary.


CREATE

Specify CREATE to fire the trigger whenever a CREATE statement adds a new database object to the data dictionary.


DISASSOCIATE STATISTICS

Specify DISASSOCIATE STATISTICS to fire the trigger whenever the database disassociates a statistics type from a database object.


DROP

Specify DROP to fire the trigger whenever a DROP statement removes a database object from the data dictionary.


GRANT

Specify GRANT to fire the trigger whenever a user grants system privileges or roles or object privileges to another user or to a role.


NOAUDIT

Specify NOAUDIT to fire the trigger whenever a NOAUDIT statement instructs the database to stop tracking a SQL statement or operations on a schema object.


RENAME

Specify RENAME to fire the trigger whenever a RENAME statement changes the name of a database object.


REVOKE

Specify REVOKE to fire the trigger whenever a REVOKE statement removes system privileges or roles or object privileges from a user or role.


TRUNCATE

Specify TRUNCATE to fire the trigger whenever a TRUNCATE statement removes the rows from a table or cluster and resets its storage characteristics.


DDL

Specify DDL to fire the trigger whenever any of the preceding DDL statements is issued.

database_event

SERVERERROR

Specify SERVERERROR to fire the trigger whenever a server error message is logged.

The following errors do not cause a SERVERERROR trigger to fire:

  • ORA-01403: no data 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 when trying to allocate string bytes (string, string)


LOGON

Specify LOGON to fire the trigger whenever a client application logs onto the database.


LOGOFF

Specify LOGOFF to fire the trigger whenever a client application logs off the database.


STARTUP

Specify STARTUP to fire the trigger whenever the database is opened.


SHUTDOWN

Specify SHUTDOWN to fire the trigger whenever an instance of the database is shut down.


SUSPEND

Specify SUSPEND to fire the trigger whenever a server error causes a transaction to be suspended.

DB_ROLE_CHANGE

In a Data Guard configuration, specify DB_ROLE_CHANGE to fire the trigger whenever a role change occurs from standby to primary or from primary to standby.