Creates a trigger.

Syntax

CREATE [ OR REPLACE ] TRIGGER name
  { BEFORE | AFTER | INSTEAD OF }
  { INSERT | UPDATE | DELETE }
      [ OR { INSERT | UPDATE | DELETE } ] [, ...]
    ON table
  [ REFERENCING { OLD AS old | NEW AS new } ...]
  [ FOR EACH ROW ]
  [ WHEN condition ]
  [ DECLARE
      [ PRAGMA AUTONOMOUS_TRANSACTION; ]
      declaration; [, ...] ]
    BEGIN
      statement; [, ...]
  [ EXCEPTION
    { WHEN exception [ OR exception ] [...] THEN
        statement; [, ...] } [, ...]
  ]
    END

Description

CREATE TRIGGER creates a trigger. CREATE OR REPLACE TRIGGER either creates a new trigger or replaces an existing definition.

If you use the CREATE TRIGGER keywords to create a new trigger, the name of the new trigger must be different from an existing trigger that is defined on the same table. New triggers are created in the same schema as the table on which the triggering event is defined.

To update the definition of an existing trigger, you can use the CREATE OR REPLACE TRIGGER keywords.

If you use syntax that is compatible with Oracle to create a trigger, the trigger runs as a SECURITY DEFINER function.

Parameters

Parameter Description
name The name of the trigger to be created.
BEFORE | AFTER Specifies whether the trigger is executed before or after the triggering event.
INSERT | UPDATE | DELETE The triggering event.
table The name of the table or view on which the triggering event occurs.
condition condition is a Boolean expression that determines whether the trigger is executed. If condition evaluates to TRUE, the trigger is executed.
  • If the trigger definition includes the FOR EACH ROW keywords, the WHEN clause can reference the columns of the old or new row values by writing OLD.column_name or NEW.column_name respectively. INSERT triggers cannot reference OLD. DELETE triggers cannot reference NEW.
  • If the trigger includes the INSTEAD OF keywords, it may not include a WHEN clause. A WHEN clause cannot contain subqueries.
REFERENCING { OLD AS old | NEW AS new } ... The REFERENCING clause that is used to reference old rows and new rows. The old value can be replaced only by an identifier named old or an equivalent that is saved in lowercase. For example, the statement can be REFERENCING OLD AS old, REFERENCING OLD AS OLD, or REFERENCING OLD AS "old". In addition, the new value can be replaced only by an identifier named new or an equivalent that is saved in lowercase. For example, the statement can be REFERENCING NEW AS new, REFERENCING NEW AS NEW, or REFERENCING NEW AS "new".

You can specify one or both of the following phrases in the REFERENCING clause: OLD AS old and NEW AS new. For example, you can specify REFERENCING NEW AS New OLD AS Old.

Note This clause is incompatible with Oracle databases because you cannot use identifiers other than old and new.
FOR EACH ROW Specifies whether the trigger is executed for each row that is affected by the triggering event or only once by each SQL statement. If specified, a row-level trigger is executed for each affected row. Otherwise, a statement-level trigger is executed.
PRAGMA AUTONOMOUS_TRANSACTION PRAGMA AUTONOMOUS_TRANSACTION is the directive that sets the trigger as an autonomous transaction.
declaration A variable, type, REF CURSOR, or subprogram declaration. If subprogram declarations are included, they must be declared after all other variable, type, and REF CURSOR declarations.
statement An SPL program statement. Note that a DECLARE - BEGIN - END block is considered an SPL statement. Therefore, the trigger body can contain nested blocks.
exception The name of an exception condition, such as NO_DATA_FOUND and OTHERS.

Examples

The following statement-level trigger is executed after the trigger statement (INSERT, UPDATE, or DELETE on table emp) is executed. 

CREATE OR REPLACE TRIGGER user_audit_trig
    AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
    v_action        VARCHAR2(24);
BEGIN
    IF INSERTING THEN
        v_action := ' added employee(s) on ';
    ELSIF UPDATING THEN
        v_action := ' updated employee(s) on ';
    ELSIF DELETING THEN
        v_action := ' deleted employee(s) on ';
    END IF;
    DBMS_OUTPUT.PUT_LINE('User ' || USER || v_action ||
        TO_CHAR(SYSDATE,'YYYY-MM-DD'));
END;

The following row-level trigger is executed before each row is inserted, updated, or deleted in the emp table. 

CREATE OR REPLACE TRIGGER emp_sal_trig
    BEFORE DELETE OR INSERT OR UPDATE ON emp
    FOR EACH ROW
DECLARE
    sal_diff       NUMBER;
BEGIN
    IF INSERTING THEN
        DBMS_OUTPUT.PUT_LINE('Inserting employee ' || :NEW.empno);
        DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal);
    END IF;
    IF UPDATING THEN
        sal_diff := :NEW.sal - :OLD.sal;
        DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno);
        DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal);
        DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal);
        DBMS_OUTPUT.PUT_LINE('..Raise     : ' || sal_diff);
    END IF;
    IF DELETING THEN
        DBMS_OUTPUT.PUT_LINE('Deleting employee ' || :OLD.empno);
        DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal);
    END IF;
END;