Types of Triggers
Designing Triggers
5-2 Oracle Database 2 Day Developer's Guide
triggered_action
;
END;
A trigger has four main parts:
A trigger name, which must be unique with respect to other triggers in the same
schema. Trigger names do not need to be unique with respect to other schema
objects (tables, views, and procedures); however, Oracle recommends that you
adopt a consistent naming convention to avoid confusion.
A triggering statement is the event that initiates the firing of the trigger. These
events include DML statements (
INSERT
,
UPDATE
, and
DELETE
) on tables and
views, DDL statements (
CREATE
,
ALTER
, and
DROP
) on schema objects, system
errors, startup and shutdown of the database, and miscellaneous system actions.
Triggering statements are subject to trigger restrictions.
A trigger restriction is the limitation that is placed on the trigger. This means that
the database performs the triggered action only if the restriction evaluates to
TRUE
.
A triggered action is the body of the trigger, or the sequence of steps that are
executed when both the appropriate statement fires the trigger and the restriction
(if any) evaluates to
TRUE
.
Types of Triggers
There are five different types of Oracle Database triggers.
Statement triggers
are associated with a DML statement, such as
DELETE
,
INSERT
, or
UPDATE
, on a specified table or view.
Note that statement triggers fire once for each DML statement. For example, an
UPDATE
statement trigger will execute only once, regardless of the number of
affected rows in the table.
You can have several different triggers associated with a specific DML statement;
starting with Oracle Database Release 11g R1, you can specify the order in which
they are executed by using the
FOLLOWS
and
PRECEDES
clauses of the
CREATE
TRIGGER
statement.
Row triggers
are fired for each row that is affected by an
INSERT
,
UPDATE
, or
DELETE
statement on a table.
Row triggers work in the same manner as statement triggers, but with two
additional specifications. Row triggers use a
FOR EACH ROW
clause in the
triggering statement. They also allow you to reference the values of the rows, and
event set them in the body of the trigger. This is particularly useful for inserting
default values, or for overriding invalid values.
INSTEAD OF
triggers on views run instead of the issuing statement. If an
INSERT
statement is used on a view, an
INSTEAD OF
trigger enables you to exercise fine
control of what actually happens: insertion of data into the base table or another
table, logging an insertion request without inserting data, and so on.
Also, Oracle Database may not be able to process an insert issued against a view,
as in the case of derived columns; you can create a trigger that determines the
See Also:
Oracle Database PL/SQL Language Reference for general information
about triggers
