Timing Triggers
Designing Triggers
Using Triggers 5-3
values correctly. For example, if view used a column definition
last_name ||
', ' || first_name
, then you may write an
INSTEAD OF
trigger that
updates the characters before the comma character into the
last_name
column,
and the characters after the comma character into the
first_name
column.
User event triggers
may be used on DDL statements such as
CREATE
,
ALTER
, or
DROP
, on user
LOGON
and
LOGOFF
, and on specific DML actions (analysis and
statistics, auditing, granting and revoking privilege, and so on).
LOGON
triggers,
which fire when a user connects to the database, are commonly used to set the
environment for the user, and to execute functions that are associated with secure
application roles.
System event triggers
apply to database startup, database shutdown, or server
error events. These events are not associated with specific tables, views, or rows.
Timing Triggers
Triggers can use
BEFORE
or
AFTER
clauses in the triggering statement.
BEFORE
and
AFTER
specify that the trigger should execute either before or after the event that fires
the trigger. For statement and row triggers, a
BEFORE
trigger can enhance security and
enable business rules prior to making changes to the database, while the
AFTER
trigger
is ideal for logging actions.
INSTEAD OF
triggers do not use
BEFORE
or
AFTER
options. By default, they use the
same semantics as
AFTER
row-level triggers.
System and user event triggers can use
BEFORE
and
AFTER
clauses, with obvious
exceptions: only
AFTER
is valid for
STARTUP
,
SUSPEND
, and
LOGON
, and only
BEFORE
is valid for
SHUTDOWN
and
LOGOFF
.
Guidelines and Restrictions for Trigger Design
You should consider the following guidelines and restrictions when planning triggers
for your application:
Although triggers are useful for customizing a database, use them only when
necessary. Excessive use of triggers can result in complex interdependencies,
which can be difficult to maintain in a large application.
Ensure that when an action is performed, all related and dependent actions are
performed.
Avoid recursive triggers because they can quickly exhaust system memory.
Be aware of cascading triggers, as they may have unintended effects and
performance implications.
See Also:
Oracle Database SQL Language Reference for details of the
CREATE
TRIGGER
statement
See Also:
Oracle Database SQL Language Reference
