Oracle-Compatible Triggers Overview & Syntax Reference - PolarDB

Creates a trigger on a table or view in PolarDB for Oracle.

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.

When using CREATE TRIGGER, the trigger name must differ from any existing trigger defined on the same table. New triggers are created in the same schema as the table on which the triggering event is defined.

Triggers created with Oracle-compatible syntax run as a SECURITY DEFINER function.

Parameters

Parameter	Description
`name`	The name of the trigger.
`BEFORE \| AFTER`	Determines whether the trigger fires before or after the triggering event.
`INSERT \| UPDATE \| DELETE`	The triggering event. Combine multiple events with `OR`.
`table`	The table or view on which the triggering event occurs.
`REFERENCING { OLD AS old \| NEW AS new }`	Aliases for old and new row values. See REFERENCING clause for constraints.
`FOR EACH ROW`	If specified, a row-level trigger fires for each affected row. If omitted, a statement-level trigger fires once per SQL statement.
`condition`	A Boolean expression. The trigger fires only when `condition` evaluates to `TRUE`. With `FOR EACH ROW`, reference old and new values as `OLD.column_name` and `NEW.column_name`.
`PRAGMA AUTONOMOUS_TRANSACTION`	Sets the trigger as an autonomous transaction.
`declaration`	A variable, type, REF CURSOR, or subprogram declaration. Subprogram declarations must follow all other variable, type, and REF CURSOR declarations.
`statement`	An SPL program statement. A `DECLARE–BEGIN–END` block is itself a valid SPL statement, so the trigger body can contain nested blocks.
`exception`	The name of an exception condition, such as `NO_DATA_FOUND` or `OTHERS`.

REFERENCING clause

The REFERENCING clause lets you reference old and new row values in a row-level trigger.

OLD can only be aliased to an identifier named old or its lowercase equivalent: REFERENCING OLD AS old, REFERENCING OLD AS OLD, or REFERENCING OLD AS "old".
NEW can only be aliased to an identifier named new or its lowercase equivalent: REFERENCING NEW AS new, REFERENCING NEW AS NEW, or REFERENCING NEW AS "new".
Both phrases can be combined: for example, REFERENCING NEW AS New OLD AS Old.

This clause is incompatible with Oracle databases because you cannot use identifiers other than old and new.

WHEN clause constraints

The following constraints apply to the WHEN clause:
INSERT triggers cannot reference OLD.
DELETE triggers cannot reference NEW.
Triggers that use INSTEAD OF cannot include a WHEN clause.
A WHEN clause cannot contain subqueries.

Examples

Statement-level trigger

The following trigger fires once after any INSERT, UPDATE, or DELETE on the emp table, and logs the action and the current user.

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;

Row-level trigger

The following trigger fires before each row is inserted, updated, or deleted in the emp table, and prints the affected employee number and salary details.

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;