Database event triggers fire whenever database-wide events occur. There are five database event triggers:
Fires when the database is opened.
Fires when the database is shut down normally.
Fires when an Oracle error is raised.
Fires when an Oracle session begins.
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.
The syntax used to create these triggers is quite similar to that used for DDL triggers:
1 CREATE [OR REPLACE] TRIGGERtrigger name
2 {BEFORE | AFTER} {database event
} ON {DATABASE | SCHEMA} 3 DECLARE 4Variable 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:
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
Again, when would they fire? Attempts to create such triggers are deflected with this message:
ORA-30501: instance shutdown triggers cannot have AFTER type
It would require some amazingly perceptive code to implement these triggers: “Wait, I think someone ...
No credit card required