CREATE TRIGGER statement (PL/SQL)

The CREATE TRIGGER statement defines a PL/SQL trigger in the database.

Syntax

trigger-event

Notes:

¹ OLD and NEW can only be specified once each.
² A trigger event must not be specified more than once for the same operation. For example, INSERT OR DELETE is allowed, but INSERT OR INSERT is not allowed.

Description

OR REPLACE

Specifies to replace the definition for the trigger if one exists at the current server. The existing definition is effectively dropped before the new definition is replaced in the catalog. This option is ignored if a definition for the trigger does not exist at the current server.

trigger-name

Names the trigger. The name, including the implicit or explicit schema name, must not identify a trigger already described in the catalog (SQLSTATE 42710). If a two-part name is specified, the schema name cannot begin with 'SYS' (SQLSTATE 42939).

BEFORE

Specifies that the associated triggered action is to be applied before any changes caused by the actual update of the subject table are applied to the database.

AFTER

Specifies that the associated triggered action is to be applied after the changes caused by the actual update of the subject table are applied to the database.

INSTEAD OF

Specifies that the associated triggered action replaces the action against the subject view.

trigger-event

Specifies that the triggered action associated with the trigger is to be executed whenever one of the events is applied to the subject table. Any combination of the events can be specified, but each event (INSERT, DELETE, and UPDATE) can only be specified once (SQLSTATE 42613).

INSERT

Specifies that the triggered action associated with the trigger is to be executed whenever an INSERT operation is applied to the subject table.

DELETE

Specifies that the triggered action associated with the trigger is to be executed whenever a DELETE operation is applied to the subject table.

UPDATE

Specifies that the triggered action associated with the trigger is to be executed whenever an UPDATE operation is applied to the subject table, subject to the columns specified or implied.

If the optional column-name list is not specified, every column of the table is implied. Therefore, omission of the column-name list implies that the trigger will be activated by the update of any column of the table.

OF column-name,...: Each column-name specified must be a column of the base table (SQLSTATE 42703). If the trigger is a BEFORE trigger, the column-name specified cannot be a generated column other than the identity column (SQLSTATE 42989). No column-name can appear more than once in the column-name list (SQLSTATE 42711). The trigger will only be activated by the update of a column that is identified in the column-name list. This clause cannot be specified for an INSTEAD OF trigger (SQLSTATE 42613).

ON table-name

Designates the subject table of the BEFORE trigger or AFTER trigger definition. The name must specify a base table or an alias that resolves to a base table (SQLSTATE 42704 or 42809). The name must not specify a catalog table (SQLSTATE 42832), a materialized query table (SQLSTATE 42997), a created temporary table, a declared temporary table (SQLSTATE 42995), or a nickname (SQLSTATE 42809).

REFERENCING

Specifies the correlation names for the transition variables. Correlation names identify a specific row in the set of rows affected by the triggering SQL operation. Each row affected by the triggering SQL operation is available to the triggered action by qualifying columns with correlation-names specified as follows.

OLD AS correlation-name: Specifies a correlation name that identifies the row state prior to the triggering SQL operation. If the trigger event is INSERT, the values in the row are null values.
NEW AS correlation-name: Specifies a correlation name that identifies the row state as modified by the triggering SQL operation and by any SET statement in a BEFORE trigger that has already executed. If the trigger event is DELETE, the values in the row are null values.

If the REFERENCING clause is not invoked, then trigger variables NEW and OLD can optionally be used without explicitly defining them.

FOR EACH ROW

Specifies that the triggered action is to be applied once for each row of the subject table that is affected by the triggering SQL operation.

FOR EACH STATEMENT

Specifies that the triggered action is to be applied only once for the whole statement.

WHEN

(search-condition): Specifies a condition that is true, false, or unknown. The search-condition provides a capability to determine whether or not a certain triggered action should be executed. The associated action is performed only if the specified search condition evaluates as true.

declaration

Specifies a variable declaration.

statement or handler-statement

Specifies a PL/SQL program statement. The trigger body can contain nested blocks.

condition

Specifies an exception condition name, such as NO_DATA_FOUND.

Example

The following example shows a before row-level trigger that calculates the commission of every new employee belonging to department 30 before a record for that employee is inserted into the EMP table. It also records any salary increases that exceed 50% in an exception table:

CREATE TABLE emp (
    name            VARCHAR2(10),
    deptno          NUMBER,
    sal             NUMBER,
    comm            NUMBER
)
/

CREATE TABLE exception (
    name            VARCHAR2(10),
    old_sal         NUMBER,
    new_sal         NUMBER
)
/

CREATE OR REPLACE TRIGGER emp_comm_trig
    BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW
BEGIN
    IF (:NEW.deptno = 30 and INSERTING) THEN
        :NEW.comm := :NEW.sal * .4;
    END IF;

    IF (UPDATING and (:NEW.sal - :OLD.sal) > :OLD.sal * .5) THEN
       INSERT INTO exception VALUES (:NEW.name, :OLD.sal, :NEW.sal);
    END IF;
END
/