Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

4.6 Triggers

You use
constraints to automatically enforce data integrity rules whenever a
user tries to write or modify a row in a table. There are times when
you want the same kind of enforcement of your own database or
application-specific logic. Oracle includes
triggers to give you this capability.

Although you can write triggers to perform the work of a constraint, Oracle has optimized the operation of constraints, so it's best to always use a constraint instead of a trigger if possible.

A trigger is a block of code that is fired whenever a particular type
of database event occurs to a table.
There are three types of events that
can cause a trigger to fire:

A database UPDATE

A database INSERT

A database DELETE

You can, for instance, define a trigger to write a customized audit
record whenever a user changes a row.

Triggers are defined at the
row level. You can specify that a trigger is to be fired either for
each row or for the SQL statement that fires the trigger event. As
with the previous discussion of constraints, a single SQL statement
can affect many rows, so the specification of the trigger can have a
significant effect on the operation of the trigger and the
performance of the database.

There are three times when a trigger can fire:

Before the execution of the triggering event

After the execution of the triggering event

Instead of the triggering event

Combining the first two timing options with the row and statement
versions of a trigger gives you four possible trigger
implementations: before a statement, before a row, after a statement,
and after a row.

INSTEAD OF triggers were introduced
with Oracle8. The INSTEAD OF trigger has a specific purpose: to
implement data-manipulation operations on views that
don't normally permit them, such as a view that
references columns in more than one base table for updates. You
should be careful when using INSTEAD OF triggers because of the many
potential problems associated with modifying the data in the
underlying base tables of a view. There are many restrictions on when
you can use INSTEAD OF triggers. Refer to your Oracle documentation
for a detailed description of the forbidden scenarios.

You can specify a
trigger
restriction for any trigger. A trigger restriction is a
Boolean expression that circumvents the execution of the trigger if
it evaluates to FALSE.

Triggers are defined and stored
separately from the tables that use them. Because they contain logic,
they must be written in a language with capabilities beyond those of
SQL, which is designed to access data.
Oracle8 and later versions allow you to
write triggers in
PL/SQL,
the procedural language that has been a part of Oracle since Version
6. Oracle8i and beyond
also support Java as a procedural language, so you can create Java
triggers with those versions.

You can write a trigger directly in PL/SQL or Java, or a trigger can
call an existing stored procedure written in either language.

Triggers are fired as a result of a SQL statement that modifies a row
in a particular table. It's possible for the actions
of the trigger to modify the data in the table or to cause changes in
other tables that fire their own triggers. The end result of this may
be data that ends up being changed in a way that Oracle thinks is
logically illegal. These situations can cause Oracle to return
runtime errors referring to mutating
tables, which are tables modified by other triggers, or
constraining tables, which are tables modified
by other constraints. Oracle8i eliminated some
of the errors caused by activating constraints with triggers.

Oracle8i also introduced a very useful set of
system event triggers (sometimes called database-level
event triggers), and user event triggers (sometimes called
schema-level event triggers). You can now place
a trigger on system events such as database startup and shutdown and
on user events such as logging on and logging off.