Oracle PL/SQL for DBAs

Database Event Triggers

Database event triggers fire whenever database-wide events occur. There are five 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 session begins

LOGOFF

Fires when an Oracle session terminates normally

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

It would require some amazingly perceptive code to implement these triggers: “Wait, I think someone ...