1.13.1 Creating Triggers

The syntax for creating a trigger is:

BEFORE | AFTER | INSTEAD OF trigger_event 
   ON 
   [ NESTED TABLE nested_table_column OF view ]    
      | table_or_view_reference | DATABASE 
trigger_body;

INSTEAD OF triggers are valid on only Oracle8 views. Oracle8 i 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 (Oracle8 i)	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 (Oracle8 i)	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 single schema or the entire database.
DROP (Oracle8 i)	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 (Oracle8 i)	Fires whenever a server error message is logged. Only AFTER triggers are allowed in this context.
LOGON (Oracle8 i)	Fires whenever a session is created (a user connects to the database). Only AFTER triggers are allowed in this context.
LOGOFF (Oracle8 i)	Fires whenever a session is terminated (a user disconnects from the database). Only BEFORE triggers are allowed in this context.
STARTUP (Oracle8 i)	Fires when the database is opened. Only AFTER triggers are allowed in this context.
SHUTDOWN (Oracle8 i)	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 only allowed 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_uid
   BEFORE INSERT ON emp 
   REFERENCING NEW as new_row 
   FOR EACH ROW
   BEGIN
      -- Automatically timestamp the entry.
      SELECT SYSDATE INTO :new_row.entry_date
         FROM dual;
END add_uid;

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

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:

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, or DELETE statements on a table or view. Triggers on these events can be statement- (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. Triggers cannot be created on SYS-owned objects. The order in which these triggers fire, if present, is as follows:

1. BEFORE statement-level trigger

2. For each row affected by the statement:

   1. BEFORE row-level trigger

   2. The triggering statement

   3. AFTER row-level trigger

3. AFTER statement-level trigger

1.13.4 DDL Events (Oracle8i)

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 (Oracle8i)

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.