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.
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.
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;
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;
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:
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.
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.