Index for Section TRIGGER

Alphabetical listing for C

Bottom of page

CREATE
NAME
  CREATE TRIGGER - define a new trigger

SYNOPSIS
  CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }
      ON table FOR EACH { ROW | STATEMENT }
      EXECUTE PROCEDURE func ( arguments )

  INPUTS

  name The name to give the new trigger.

  table
       The name of an existing table.

  event
       One of INSERT, DELETE or UPDATE.

  func A user-supplied function.

  OUTPUTS

  CREATE
       This message is returned if the trigger is successfully created.

DESCRIPTION
  CREATE TRIGGER will enter a new trigger into the current data base. The
  trigger will be associated with the relation table and will execute the
  specified function func.

  The trigger can be specified to fire either before BEFORE the operation is
  attempted on a tuple (before constraints are checked and the INSERT, UPDATE
  or DELETE is attempted) or AFTER the operation has been attempted (e.g.,
  after constraints are checked and the INSERT, UPDATE or DELETE has
  completed). If the trigger fires before the event, the trigger may skip the
  operation for the current tuple, or change the tuple being inserted (for
  INSERT and UPDATE operations only). If the trigger fires after the event,
  all changes, including the last insertion, update, or deletion, are
  ``visible'' to the trigger.

  SELECT does not modify any rows so you can not create SELECT triggers.
  Rules and views are more appropriate in such cases.

  Refer to the chapters on SPI and Triggers in the PostgreSQL Programmer's
  Guide for more information.

NOTES
  To create a trigger on a table, the user must have the TRIGGER privilege on
  the table.

  As of the current release, STATEMENT triggers are not implemented.

  Refer to the drop_trigger(5) command for information on how to remove
  triggers.

EXAMPLES
  Check if the specified distributor code exists in the distributors table
  before appending or updating a row in the table films:

  CREATE TRIGGER if_dist_exists
      BEFORE INSERT OR UPDATE ON films FOR EACH ROW
      EXECUTE PROCEDURE check_primary_key ('did', 'distributors', 'did');

  Before cancelling a distributor or updating its code, remove every
  reference to the table films:

  CREATE TRIGGER if_film_exists
      BEFORE DELETE OR UPDATE ON distributors FOR EACH ROW
      EXECUTE PROCEDURE check_foreign_key (1, 'CASCADE', 'did', 'films', 'did');

  The second example can also be done by using a foreign key, constraint as
  in:

  CREATE TABLE distributors (
      did      DECIMAL(3),
      name     VARCHAR(40),
      CONSTRAINT if_film_exists
      FOREIGN KEY(did) REFERENCES films
      ON UPDATE CASCADE ON DELETE CASCADE
  );

COMPATIBILITY
  SQL92
       There is no CREATE TRIGGER statement in SQL92.

  SQL99
       The CREATE TRIGGER statement in PostgreSQL implements a subset of the
       SQL99 standard. The following functionality is missing:

       o+ SQL99 allows triggers to fire on updates to specific columns (e.g.,
	 AFTER UPDATE OF col1, col2).

       o+ SQL99 allows you to define aliases for the ``old'' and ``new'' rows
	 or tables for use in the definition of the triggered action (e.g.,
	 CREATE TRIGGER ... ON tablename REFERENCING OLD ROW AS somename NEW
	 ROW AS othername ...). Since PostgreSQL allows trigger procedures to
	 be written in any number of user-defined languages, access to the
	 data is handled in a language-specific way.

       o+ PostgreSQL only has row-level triggers, no statement-level triggers.

       o+ PostgreSQL only allows the execution of a stored procedure for the
	 triggered action.  SQL99 allows the execution of a number of other
	 SQL commands, such as CREATE TABLE as triggered action.  This
	 limitation is not hard to work around by creating a stored procedure
	 that executes these commands.

SEE ALSO
  create_function(5), drop_trigger(l), PostgreSQL Programmer's Guide

Index for Section TRIGGER

Alphabetical listing for C

Top of page