Oracle PL/SQL Language Pocket Reference, 4th Edition

Triggers

Triggers are programs that execute in response to changes in table data or certain database events. A predefined set of events can be “hooked” with a trigger, enabling you to integrate your own processing with that of the database. A triggering event fires or executes the trigger.

There are three types of triggering events:

DML events

Fire when an INSERT, UPDATE, or DELETE statement executes

DDL events

Fire when a CREATE, ALTER, or DROP statement executes

Database events

Fire when one of the predefined database-level events occurs

Complete lists of these events are included in later sections.

Creating Triggers

The syntax for creating a trigger on a DML event is:

CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF | FOR } trigger_event
   ON {table_or_view_reference |
     NESTED TABLE nested_table_column OF view}
     [REFERENCING [OLD AS old] [NEW AS new]
       [PARENT AS parent]]
[FOR EACH ROW ]
[FOLLOWS other_trigger] [DISABLE]
[COMPOUND TRIGGER]
[WHEN trigger_condition]
trigger_body;

The syntax for creating a trigger on a DDL or database event is:

CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE | AFTER } trigger_event
   ON [ DATABASE | schema ]
 [FOLLOWS other_trigger][DISABLE]
[WHEN trigger_condition]
trigger_body;

Trigger events are listed in the following table:

Trigger event	Description
INSERT	Fires whenever a row is added to the table_ or_view_reference.
UPDATE	Fires whenever an UPDATE changes the table_or_view_reference. UPDATE triggers can additionally specify an OF clause to restrict ...