Define a trigger with the CREATE TRIGGER statement - PolarDB

Use CREATE TRIGGER to define a trigger and store it in the database. Triggers fire automatically on INSERT, UPDATE, or DELETE events so you can enforce business rules, audit changes, or keep derived data in sync without application-level code.

Prerequisites

Before you begin, make sure you have the CREATE TRIGGER privilege on the target table.

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 defines a new trigger. CREATE OR REPLACE TRIGGER creates a trigger or replaces an existing definition.

When creating a new trigger, the name must not match any existing trigger defined on the same table. The new trigger is created in the same schema as the table on which the triggering event is defined. To update an existing trigger's definition, use CREATE OR REPLACE TRIGGER.

Trigger timing and behavior

The trigger timing determines when the trigger fires relative to constraint checking and what the trigger body can do:

`BEFORE`: The trigger fires before constraints are checked and before the DML operation is attempted. The trigger body can modify the row being inserted or updated, or cancel the operation entirely.
`AFTER`: The trigger fires after constraints are checked and after the DML operation completes. All changes — including changes made by other triggers — are visible to the trigger body.
`INSTEAD OF`: Only valid on views. The trigger fires in place of the DML operation. Use INSTEAD OF to make non-updatable views support INSERT, UPDATE, and DELETE.

Row-level vs. statement-level triggers

Row-level (FOR EACH ROW): fires once per affected row. A DELETE that removes 10 rows calls the trigger 10 times.
Statement-level (no FOR EACH ROW): fires once per SQL statement, regardless of how many rows are affected — including zero rows.

Security

When you create a trigger using Oracle-compatible syntax, the trigger runs as a SECURITY DEFINER function, meaning it executes with the privileges of the trigger's owner, not the user who caused the trigger to fire.

The REFERENCING clause is not fully compatible with Oracle Database. The identifiers after OLD AS and NEW AS must be old and new or any equivalent that is saved in all lowercase (for example, REFERENCING OLD AS old, REFERENCING OLD AS OLD, or REFERENCING OLD AS "old"). Identifiers other than old or new are not supported.

Parameters

Parameter	Description
`name`	The name of the trigger to create.
`BEFORE \| AFTER \| INSTEAD OF`	The timing of the trigger relative to the triggering event.
`INSERT \| UPDATE \| DELETE`	The DML event that fires the trigger. Combine multiple events with `OR`.
`table`	The name of the table or view on which the triggering event occurs.
`condition`	A Boolean expression that gates trigger execution. The trigger fires only when `condition` evaluates to `TRUE`. In a `FOR EACH ROW` trigger, the `WHEN` clause can reference old and new row values using `OLD.column_name` and `NEW.column_name`. `INSERT` triggers cannot reference `OLD`; `DELETE` triggers cannot reference `NEW`. `INSTEAD OF` triggers cannot use a `WHEN` clause. `WHEN` clauses cannot contain subqueries.
`REFERENCING { OLD AS old \| NEW AS new } ...`	Aliases for the old and new row values. The alias after `OLD AS` must be `old` or any equivalent that is saved in all lowercase (for example, `REFERENCING OLD AS old`, `REFERENCING OLD AS OLD`, or `REFERENCING OLD AS "old"`). Likewise, the alias after `NEW AS` must be `new` or any equivalent saved in all lowercase. Either one or both phrases may appear in the clause — for example, `REFERENCING NEW AS new OLD AS old`. Aliases other than `old` and `new` are not supported.
`FOR EACH ROW`	Makes the trigger row-level: the trigger fires once for each row affected by the triggering event. Without this clause, the trigger is statement-level and fires once per SQL statement.
`PRAGMA AUTONOMOUS_TRANSACTION`	Sets the trigger as an autonomous transaction, allowing it to commit or roll back independently of the transaction that caused it to fire.
`declaration`	A variable, type, `REF CURSOR`, or subprogram declaration. Subprogram declarations must appear after all other variable, type, and `REF CURSOR` declarations.
`statement`	A Structured Process Language (SPL) statement. A `DECLARE...BEGIN...END` block is itself an SPL statement, so the trigger body can contain nested blocks.
`exception`	The name of an exception condition to handle, such as `NO_DATA_FOUND` or `OTHERS`.

Examples

Row-level BEFORE INSERT trigger

This trigger assigns a timestamp to created_at before each row is inserted into orders.

CREATE OR REPLACE TRIGGER set_created_at
  BEFORE INSERT ON orders
  FOR EACH ROW
DECLARE
BEGIN
  :NEW.created_at := SYSDATE;
END;

Row-level AFTER UPDATE trigger with a WHEN condition

This trigger logs a row to salary_audit only when the salary column actually changes.

CREATE OR REPLACE TRIGGER log_salary_change
  AFTER UPDATE ON employees
  FOR EACH ROW
  WHEN (OLD.salary != NEW.salary)
DECLARE
BEGIN
  INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_at)
  VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);
END;

Statement-level AFTER DELETE trigger

This trigger records a single audit entry whenever a DELETE statement runs against orders, regardless of how many rows are deleted.

CREATE OR REPLACE TRIGGER audit_order_delete
  AFTER DELETE ON orders
DECLARE
BEGIN
  INSERT INTO audit_log (action, action_time)
  VALUES ('DELETE from orders', SYSDATE);
END;

INSTEAD OF trigger on a view

This trigger intercepts INSERT statements on the active_employees view and redirects them to the underlying employees table.

CREATE OR REPLACE TRIGGER insert_active_employee
  INSTEAD OF INSERT ON active_employees
  FOR EACH ROW
DECLARE
BEGIN
  INSERT INTO employees (emp_id, name, status)
  VALUES (:NEW.emp_id, :NEW.name, 'ACTIVE');
END;