| |
 |
|
Oracle Tips by Burleson |
Administration of Triggers
in Oracle
Database triggers are PL/SQL procedures
stored in the database and associated with specific actions on a
database level. Beginning with Oracle8, a new type of trigger called
an INSTEAD OF trigger could be created exclusively for views. Under
Oracle8i and Oracle, the concept of database event triggers has
been added. A database event trigger triggers on DDL or database
events such as STARTUP, SHUTDOWN, LOGIN, LOGOFF, or server errors.
Tip
Never depend on the order in which triggers
fire. This is not guaranteed to always be identical.
Creation of Database Triggers
Database triggers are created using the
CREATE TRIGGER command, and are ENABLED upon creation. There were
three basic types of triggers in Oracle7: BEFORE, AFTER, and FOR
EACH ROW. Under Oracle8, a fourth was added: the INSTEAD OF trigger
for views. Since the FOR EACH ROW clause can be combined with the
other two non-view triggers, this gives four types of triggers:
BEFORE and AFTER statement triggers and BEFORE and AFTER row
triggers. In addition, each of the four types can be tied to the
three basic actions, DELETE, INSERT, and UPDATE, resulting in 12
possible triggers per table.
A view can only have an INSTEAD OF trigger
(under Oracle8 only). The Oracle8i DDL and database event triggers
are also created using the CREATE TRIGGER command. DDL events that
can cause a trigger to fire are CREATE, ALTER, or DROP on clusters,
tables, views, procedures, functions, packages, roles, sequences,
synonyms, indexes, tablespaces, triggers, types, and users. The
database event triggers are LOGON, LOGOFF, SERVERERROR, STARTUP, and
SHUTDOWN, with the LOGON, STARTUP, and SERVERERROR coming AFTER
event triggers. The LOGOFF and SHUTDOWN are BEFORE event triggers.
You can find the exact syntax for the CREATE TRIGGER command in the
SQL reference at the techent.oracle.com Web site.
Database triggers are complex, so if you do
not save the creation script, it will be very difficult to readily
recall the exact command used in many cases. The script in
TRIG_RCT.SQL, available from the Wiley Web site, can be used to
retrieve trigger definitions for the database. Prior to the release
of Oracle version 7.3, triggers were compiled at runtime. Since 7.3,
they are stored in the database as Pcode. This provides significant
performance benefits over earlier versions since the overhead of
reparsing the trigger for each firing is eliminated. This allows
larger and more complex triggers to be created without fear of
performance degradation caused by reparsing large sections of code.
In versions 8, 8i and 9i triggers can be pinned into memory using
the DBMS_SHARED_POOL.KEEP(‘trigger_name’, ‘T’) procedure call.
Conditional Predicates
New to Oracle8 and continued in Oracle8i and
Oracle is the concept of a conditional predicate for a trigger
that tells the trigger why it is being firedThese conditional
predicates are of the form:
INSERTING. Evaluates to TRUE if an insert
operation fired the trigger.
DELETING. Evaluates to TRUE if a delete
operation fired the trigger.
UPDATING. Evaluates to TRUE if an update
operation fired the trigger.
UPDATING(column). Evaluates to TRUE if
the operation is an update and the specified column is updated.
See Code Depot
www.oracle-script.com |
