O'Reilly logo

Oracle PL/SQL Programming, Third Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required