Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 5.3 Creating Triggers

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

5.3 Creating Triggers

The CREATE TRIGGER command defines and names a trigger that will be stored in the database.

Name

CREATE TRIGGER -- define a new trigger

Synopsis

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

Description

CREATE TRIGGER defines a new trigger. CREATE OR REPLACE TRIGGER will either create a new trigger, or replace an existing definition.

The name of the new trigger must not match any existing trigger defined on the same table unless the intent is to update the definition of an existing trigger, in which case use CREATE OR REPLACE TRIGGER.

The trigger is created in the same schema as the table on which the triggering event is defined.

Parameters

name

The name of the trigger 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 on which the triggering event occurs.

condition

      condition is a Boolean expression that determines if the trigger will actually be executed; if condition evaluates to TRUE, the trigger will fire.

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

      If the trigger includes the INSTEAD OF keywords, it may not include a WHEN clause.

      WHEN clauses cannot contain subqueries.

FOR EACH ROW

Determines whether the trigger should be fired once for every row affected by the triggering event, or just 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.

declaration

A variable, type, or REF CURSOR declaration.

statement

An SPL program statement. Note that a DECLARE - BEGIN - END block is considered an SPL statement unto itself. Thus, the trigger body may contain nested blocks.

exception

An exception condition name such as NO_DATA_FOUND, OTHERS, etc.

Previous PageTable Of ContentsNext Page