Create a trigger - PolarDB - Alibaba Cloud Documentation Center

Syntax

Define a new trigger.

CREATE TRIGGER

Synopsis

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 REPLACETRIGGER creates a trigger or replaces an existing definition.

If you are using the CREATE TRIGGER statement to create a trigger, the name of the new trigger must not match 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.

If you are updating the definition of an existing trigger, use the CREATEOR REPLACE TRIGGER statement.

When you use the syntax compatible with Oracle databases to create a trigger, the trigger runs as a SECURITY DEFINER function.

Parameters


Parameter	Description
name	The name of the trigger that you want to create.
BEFORE \| AFTER	Determines whether the trigger is fired before or after the triggering event.
INSERT \| UPDATE \| DELETE	Defines the triggering event.
table	The name of the table or view on which the triggering event occurs.
condition	A Boolean expression that determines if the trigger will actually be executed. If condition evaluates to TRUE, the trigger is fired. If the trigger definition includes the FOR EACH ROW keywords, the WHEN clause can reference columns of the old and/or new row values after you write OLD.column_name or NEW.column_name, respectively. INSERT triggers cannot reference OLD, and DELETE triggers cannot reference NEW. If a trigger contains the keywords INSTEAD OF, it may not contain the WHEN clause. WHEN clauses cannot contain subqueries.
REFERENCING { OLD AS old \| NEW AS new } ...	The REFERENCING clause to reference old rows and new rows, but restricted in that old may only be replaced by an identifier named 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". Also, new may only be replaced by an identifier named new or any equivalent that is saved in all lowercase, for example, REFERENCING NEW AS new, REFERENCING NEW AS NEW, or REFERENCING NEW AS "new". Either one or both phrases OLD AS old and NEW AS new may be specified in the REFERENCING clause, for example, REFERENCING NEW AS New OLD AS Old. This clause is not compatible with Oracle databases in that identifiers other than old or new may not be used.
FOR EACH ROW	Determines whether the trigger should be fired once for every row affected by the triggering event or only once per SQL statement. If it is specified, the trigger is fired once for every affected row (row-level trigger). Otherwise, the trigger is a statement-level trigger.
PRAGMA AUTONOMOUS_TRANSACTION	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	A Structured Process Language (SPL) program statement. Note that a DECLARE - BEGIN - END block is considered an SPL statement. Therefore, the trigger body may contain nested blocks.
exception	The name of an exception condition, such as NO_DATA_FOUND and OTHERS.