Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page