Oracle PL/SQL Programming, 5th Edition

Database Event Triggers

Database event triggers fire whenever database-wide events occur. There are six database event triggers:

STARTUP

Fires when the database is opened.

SHUTDOWN

Fires when the database is shut down normally.

SERVERERROR

Fires when an Oracle error is raised.

LOGON

Fires when an Oracle database session begins.

LOGOFF

Fires when an Oracle database session terminates normally.

DB_ROLE_CHANGE

Fires when a standby database is changed to be the primary database or vice versa.

As any DBA will immediately see, these triggers offer stunning possibilities for automated administration and very granular control.

Creating a Database Event Trigger

The syntax used to create these triggers is quite similar to that used for DDL triggers:

1    CREATE [OR REPLACE] TRIGGER trigger_name
2    {BEFORE | AFTER} {database_event} ON {DATABASE | SCHEMA}
3    DECLARE
4    Variable declarations
5    BEGIN
6    ...some code...
7    END;

There are restrictions regarding what events can be combined with what BEFORE and AFTER attributes. Some situations just don’t make sense:

No BEFORE STARTUP triggers

Even if such triggers could be created, when would they fire? Attempts to create triggers of this type will be met by this straightforward error message:

ORA-30500: database open triggers and server error triggers cannot have 
BEFORE type

No AFTER SHUTDOWN triggers

Again, when would they fire? Attempts to create such triggers are deflected with this message:

ORA-30501: instance shutdown triggers cannot have AFTER type

No BEFORE LOGON triggers ...