O'Reilly logo

Oracle PL/SQL Language Pocket Reference by Chip Dawes, Steven Feuerstein, Bill Pribyl

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required