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

1.13.1 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	Fires whenever a row is deleted from the table_or_view_reference. Does not fire on a TRUNCATE of the table.
ALTER	Fires whenever an ALTER statement changes a database object. In this context, objects are things like tables or packages (found in ALL_OBJECTS). Can apply to a single schema or the entire database.
DROP	Fires whenever a DROP statement removes an object from 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.
SERVERERROR	Fires whenever a server error message is logged. Only AFTER triggers are allowed in this context.
LOGON	Fires whenever a session is created (a user connects to the database). Only AFTER triggers are allowed in this context.
LOGOFF	Fires whenever a session is terminated (a user disconnects from the database). Only BEFORE triggers are allowed in this context.
STARTUP	Fires when the database is opened. Only AFTER triggers are allowed in this context.
SHUTDOWN	Fires when the database is closed. Only BEFORE triggers are allowed in this context.

Triggers can fire BEFORE or AFTER the triggering event. AFTER data triggers are slightly more efficient than BEFORE triggers.

The REFERENCING clause is allowed only for the data events INSERT, UPDATE, and DELETE. It lets you give a non-default name to the old and new pseudo-records. These pseudo-records give the program visibility to the pre- and post-change values in row-level triggers. These records are defined like %ROWTYPE records, except that columns of type LONG or LONG RAW cannot be referenced. They are prefixed with a colon in the trigger body, and referenced with dot notation. Unlike other records, these fields can only be assigned individually—aggregate assignment is not allowed. All old fields are NULL within INSERT triggers, and all new fields are NULL within DELETE triggers.

FOR EACH ROW defines the trigger to be a row-level trigger. Row-level triggers fire once for each row affected. The default is a statement-level trigger, which fires only once for each triggering statement.

The WHEN trigger_condition specifies the conditions that must be met for the trigger to fire. Stored functions and object methods are not allowed in the trigger condition.

The trigger body is a standard PL/SQL block. For example:

CREATE OR REPLACE TRIGGER add_tstamp
   BEFORE INSERT ON emp 
   REFERENCING NEW as new_row 
   FOR EACH ROW
   BEGIN
      -- Automatically timestamp the entry.
      SELECT CURRENT_TIMESTAMP 
      INTO :new_row.entry_timestamp
      FROM dual;
END add_tstamp;

Triggers are enabled on creation, and can be disabled (so they do not fire) with an ALTER statement, issued with the following syntax:

ALTER TRIGGER trigger_name { ENABLE | DISABLE };

ALTER TABLE table_name { ENABLE | DISABLE } ALL 
   TRIGGERS;

1.13.2 Trigger Predicates

When using a single trigger for multiple events, use the trigger predicates INSERTING, UPDATING, and DELETING in the trigger condition to identify the triggering event, as shown in this example:

CREATE OR REPLACE TRIGGER emp_log_t
   AFTER INSERT OR UPDATE OR DELETE ON emp
   FOR EACH ROW
DECLARE
   dmltype  CHAR(1);
BEGIN
   IF INSERTING THEN
      dmltype := 'I';
      INSERT INTO emp_log (emp_no, who, operation)
         VALUES (:new.empno, USER, dmltype);
   ELSIF UPDATING  THEN
      dmltype := 'U';
      INSERT INTO emp_log (emp_no, who, operation)
         VALUES (:new.empno, USER, dmltype);
   END IF;
END;

1.13.3 DML Events

The DML events include INSERT, UPDATE, and DELETE statements on a table or view. Triggers on these events can be statement-level triggers (table only) or row-level triggers and can fire BEFORE or AFTER the triggering event. BEFORE triggers can modify the data in affected rows, but perform an additional logical read. AFTER triggers do not perform this additional logical read, and therefore perform slightly better, but are not able to change the :new values. AFTER triggers are thus better suited for data validation functionality. Triggers cannot be created on SYS-owned objects. The order in which these triggers fire, if present, is as follows:

BEFORE statement-level trigger

For each row affected by the statement:

BEFORE row-level trigger

The triggering statement

AFTER row-level trigger

AFTER statement-level trigger

1.13.4 DDL Events

The DDL events are CREATE, ALTER, and DROP. These triggers fire whenever the respective DDL statement is executed. DDL triggers can apply to either a single schema or the entire database.

1.13.5 Database Events

The database events are SERVERERROR, LOGON, LOGOFF, STARTUP, and SHUTDOWN. Only BEFORE triggers are allowed for LOGOFF and SHUTDOWN events. Only AFTER triggers are allowed for LOGON, STARTUP, and SERVERERROR events. A SHUTDOWN trigger will fire on a SHUTDOWN NORMAL and a SHUTDOWN IMMEDIATE, but not on a SHUTDOWN ABORT.