Oracle PL/SQL Language Pocket Reference, Second Edition

Triggers

Triggers are programs that execute in response to changes in table data or certain database events. There is a predefined set of events that 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 } 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 ][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 ]
 [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 firing to updates OF certain columns.
DELETE