Oracle PL/SQL Language Pocket Reference, 4th Edition
by Steven Feuerstein, Bill Pribyl, Chip Dawes
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] TRIGGERtrigger_name{ BEFORE | AFTER | INSTEAD OF | FOR }trigger_eventON {table_or_view_reference| NESTED TABLEnested_table_columnOFview} [REFERENCING [OLD ASold] [NEW ASnew] [PARENT ASparent]] [FOR EACH ROW ] [FOLLOWSother_trigger] [DISABLE] [COMPOUND TRIGGER] [WHENtrigger_condition]trigger_body;
The syntax for creating a trigger on a DDL or database event is:
CREATE [OR REPLACE] TRIGGERtrigger_name{ BEFORE | AFTER }trigger_eventON [ DATABASE |schema] [FOLLOWS other_trigger][DISABLE] [WHENtrigger_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 ... |