background image
<< Timing Triggers | Creating a Row Trigger >>
<< Timing Triggers | Creating a Row Trigger >>

Creating and Using Triggers

Creating and Using Triggers
5-4 Oracle Database 2 Day Developer's Guide
Avoid triggers that duplicate existing Oracle Database offerings; for example, do
not design triggers that reject bad data that can be eliminated through declarative
integrity constraints.
Ensure that you use the
BEFORE
and
AFTER
clauses correctly to efficiently
implement business rules. A
BEFORE EACH ROW
trigger can change the
:NEW
values.
Limit the size of triggers, as they cannot exceed 32Kb. If a trigger requires many
lines of code, consider moving the business logic to a stored procedure that is
invoked from the trigger.
Ensure that the triggers you create apply to the database and the business logic
that is correct for the entire enterprise, regardless of specific users or client
applications. If special rules apply only to some users and client applications and
not to others, encapsulate that business logic within the application.
You cannot use
COMMIT
,
ROLLBACK
, or
SAVEPOINT
inside a trigger. Because DDL
statements have an implicit
COMMIT
, they are also not allowed in triggers, with the
exception of
CREATE
,
ALTER
,
DROP TABLE
, and
ALTER...COMPILE
for system
triggers.
Only committed system triggers are fired.
Creating and Using Triggers
This sections shows how to create and use various types of triggers.
This section has the following topics:
Creating a Statement Trigger
Creating a Row Trigger
Creating an INSTEAD OF Trigger
Creating LOGON and LOGOFF Triggers
Modifying Triggers
Disabling and Enabling Triggers
Compiling Triggers
Dropping Triggers
Creating a Statement Trigger
Statement triggers relate to a particular statement, such as
INSERT
,
UPDATE
, or
DELETE
. You can use a statement trigger for logging such operations as they are
performed on a particular table.
Example 5­1
shows how to create a log table.
Example 5­1 Creating a Log Table for the EVALUATIONS Table
The table
evaluations_log
stores entries with each
INSERT
,
UPDATE
or
DELETE
on
the
evaluations
table.
See Also:
Oracle Database SQL Language Reference for information about
creating triggers