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:
Complete lists of these events are included in later sections. 1.13.1 Creating TriggersThe 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:
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 PredicatesWhen 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 EventsThe 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:
1.13.4 DDL EventsThe 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 EventsThe 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. |