Oracle PL/SQL Language Pocket Reference

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.

Creating Triggers

The syntax for creating a trigger is:

CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE | AFTER | INSTEAD OF trigger_event 
   ON 
   [ NESTED TABLE nested_table_column OF view ]    
      | table_or_view_reference | DATABASE
   [referencing_clause]
[FOR EACH ROW [WHEN trigger_condition]]
trigger_body;

INSTEAD OF triggers are valid on only Oracle8 views. Oracle8i must create a trigger on a nested table column.

Trigger events are defined in the following table.

Trigger Event	Description
INSERT	Fires whenever a row is added to the table_reference.
UPDATE	Fires whenever an UPDATE changes the table_reference. UPDATE triggers can additionally specify an OF clause to restrict firing to updates OF certain columns. See the following examples.
DELETE	Fires whenever a row is deleted from the table_reference. Does not fire on TRUNCATE of the table.
CREATE (Oracle8i)	Fires whenever a CREATE statement adds a new object to the database. In this context, objects are things like tables or packages (found in ALL_OBJECTS). Can apply to a single schema or the entire database.
ALTER (Oracle8i)	Fires whenever an ALTER statement changes a database object. In this context, objects are things like ...