Creating triggers v16

Suggest edits

The CREATE TRIGGER command defines and names a trigger that's stored in the database. You can create a simple trigger or a compound trigger.

Creating a simple trigger

CREATE TRIGGER — Define a simple trigger.

CREATE [ OR REPLACE ] TRIGGER <name>
  { BEFORE | AFTER | INSTEAD OF }
  { INSERT | UPDATE | DELETE | TRUNCATE }
      [ OR { INSERT | UPDATE | DELETE | TRUNCATE } ] [, ...]
    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

Creating a compound trigger

CREATE TRIGGER — Define a compound trigger.

CREATE [ OR REPLACE ] TRIGGER <name>
  FOR { INSERT | UPDATE | DELETE | TRUNCATE }
        [ OR { INSERT | UPDATE | DELETE | TRUNCATE } ] [, ...]
           ON <table>
       [ REFERENCING { OLD AS <old> | NEW AS <new>. } ...]
       [ WHEN <condition> ]
       COMPOUND TRIGGER
       [ <private_declaration>; ] ...
       [ <procedure_or_function_definition> ] ...
       <compound_trigger_definition>
         END

Where private_declaration is an identifier of a private variable that can be accessed by any procedure or function. There can be zero, one, or more private variables. private_declaration can be any of the following:

  • Variable declaration
  • Record declaration
  • Collection declaration
  • REF CURSOR and cursor variable declaration
  • TYPE definitions for records, collections, and REF CURSOR
  • Exception
  • Object variable declaration

Where procedure_or_function_definition :=

procedure_definition | function_definition

Where procedure_definition :=

PROCEDURE proc_name[ argument_list ]
  [ options_list ]
  { IS | AS }
    procedure_body
  END [ proc_name ] ;

Where procedure_body :=

[ <declaration>; ] [, ...]
BEGIN
  <statement>; [...]
[ EXCEPTION
   { WHEN <exception> [OR <exception>] [...]] THEN <statement>; }
   [...]
]

Where function_definition :=

FUNCTION func_name [ argument_list ]
  RETURN rettype [ DETERMINISTIC ]
  [ options_list ]
  { IS | AS }
    function_body
  END [ func_name ] ;

Where function_body :=

[ <declaration>; ] [, ...]
BEGIN
  <statement>; [...]
[ EXCEPTION
  { WHEN <exception> [ OR <exception> ] [...] THEN <statement>; }
  [...]
]

Where compound_trigger_definition is:

{ compound_trigger_event } { IS | AS }
  compound_trigger_body
END [ compound_trigger_event ] [ ... ]

Where compound_trigger_event:=

[ BEFORE STATEMENT | BEFORE EACH ROW | AFTER EACH ROW | AFTER STATEMENT  | INSTEAD OF EACH ROW ]

Where compound_trigger_body:=

[ <declaration>; ] [, ...]
BEGIN
  <statement>; [...]
[ EXCEPTION
   { WHEN <exception> [OR <exception>] [...] THEN <statement>; }
   [...]
]

Description

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

If you're using the CREATE TRIGGER keywords 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're updating the definition of an existing trigger, use the CREATE OR REPLACE TRIGGER keywords.

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

Parameters

name

The name of the trigger to create.

BEFORE | AFTER

Determines whether the trigger is fired before or after the triggering event.

INSTEAD OF

Trigger that modifies an updatable view. The trigger executes to update the underlying tables appropriately. The INSTEAD OF trigger executes for each row of the view that's updated or modified.

INSERT | UPDATE | DELETE | TRUNCATE

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 actually executes. If condition evaluates to TRUE, the trigger fires.

  • If the simple trigger definition includes the FOR EACH ROW keywords, the WHEN clause can refer to columns of the old or new row values by writing OLD.column_name or NEW.column_name respectively. INSERT triggers can't refer to OLD, and DELETE triggers can't refer to NEW.

  • If the compound trigger definition includes a statement-level trigger having a WHEN clause, then the trigger executes without evaluating the expression in the WHEN clause. Similarly, if a compound trigger definition includes a row-level trigger having a WHEN clause, then the trigger executes if the expression evaluates to TRUE.

  • If the trigger includes the INSTEAD OF keywords, it can't include a WHEN clause. A WHEN clause can't contain subqueries.

REFERENCING { OLD AS old | NEW AS new } ...

REFERENCING clause to reference old rows and new rows but restricted in that old can be replaced only by an identifier named old or any equivalent that's saved in all lowercase. Examples include REFERENCING OLD AS old, REFERENCING OLD AS OLD, or REFERENCING OLD AS "old". Also, new can be replaced only by an identifier named new or any equivalent that's saved in all lowercase. Examples include REFERENCING NEW AS new, REFERENCING NEW AS NEW, or REFERENCING NEW AS "new".

You can specify one or both phrases OLD AS old and NEW AS new in the REFERENCING clause, such as REFERENCING NEW AS New OLD AS Old. This clause isn't compatible with Oracle databases in that you can't use identifiers other than old or new.

FOR EACH ROW

Determines whether to fire the trigger once for every row affected by the triggering event or once per SQL statement. If specified, the trigger is fired once for every affected row (row-level trigger). Otherwise the trigger is a statement-level trigger.

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, you must declare them after all other variable, type, and REF CURSOR declarations.

statement

An SPL program statement. A DECLARE - BEGIN - END block is considered an SPL statement. Thus, the trigger body can contain nested blocks.

exception

An exception condition name such as NO_DATA_FOUND.

← Prev

Types of triggers

Next →

Trigger variables