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 funcname ( arguments )

DESCRIPTION
  CREATE TRIGGER creates a new trigger. The trigger will be associated with
  the specified table and will execute the specified function funcname when
  certain events occur.

  The trigger can be specified to fire either before the operation is
  attempted on a row (before constraints are checked and the INSERT, UPDATE,
  or DELETE is attempted) or after the operation has completed (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 row, or change the row 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.

  A trigger that is marked FOR EACH ROW is called once for every row that the
  operation modifies. For example, a DELETE that affects 10 rows will cause
  any ON DELETE triggers on the target relation to be called 10 separate
  times, once for each deleted row. In contrast, a trigger that is marked FOR
  EACH STATEMENT only executes once for any given operation, regardless of
  how many rows it modifies (in particular, an operation that modifies zero
  rows will still result in the execution of any applicable FOR EACH
  STATEMENT triggers).

  If multiple triggers of the same kind are defined for the same event, they
  will be fired in alphabetical order by name.

  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 documentation for more information about triggers.

PARAMETERS
  name The name to give the new trigger. This must be distinct from the name
       of any other trigger for the same table.

  BEFORE

  AFTER
       Determines whether the function is called before or after the event.

  event
       One of INSERT, UPDATE, or DELETE; this specifies the event that will
       fire the trigger. Multiple events can be specified using OR.

  table
       The name (optionally schema-qualified) of the table the trigger is
       for.

  FOR EACH ROW

  FOR EACH STATEMENT
       This specifies whether the trigger procedure should be fired once for
       every row affected by the trigger event, or just once per SQL
       statement. If neither is specified, FOR EACH STATEMENT is the default.

  funcname
       A user-supplied function that is declared as taking no arguments and
       returning type trigger, which is executed when the trigger fires.

  arguments
       An optional comma-separated list of arguments to be provided to the
       function when the trigger is executed. The arguments are literal
       string constants. Simple names and numeric constants may be written
       here, too, but they will all be converted to strings. Please check the
       description of the implementation language of the trigger function
       about how the trigger arguments are accessible within the function; it
       may be different from normal function arguments.

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

  In PostgreSQL versions before 7.3, it was necessary to declare trigger
  functions as returning the placeholder type opaque, rather than trigger. To
  support loading of old dump files, CREATE TRIGGER will accept a function
  declared as returning opaque, but it will issue a notice and change the
  function's declared return type to trigger.

  Use DROP TRIGGER [drop_trigger(5)] to remove a trigger.

EXAMPLES
  the documentation contains a complete example.

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

  · SQL allows triggers to fire on updates to specific columns (e.g., AFTER
    UPDATE OF col1, col2).

  · SQL 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.

  · PostgreSQL only allows the execution of a user-defined function for the
    triggered action. The standard allows the execution of a number of other
    SQL commands, such as CREATE TABLE as the triggered action. This
    limitation is not hard to work around by creating a user-defined function
    that executes the desired commands.

  SQL specifies that multiple triggers should be fired in time-of-creation
  order. PostgreSQL uses name order, which was judged more convenient to work
  with.

  The ability to specify multiple actions for a single trigger using OR is a
  PostgreSQL extension of the SQL standard.

SEE ALSO
  CREATE FUNCTION [create_function(5)], ALTER TRIGGER [alter_trigger(l)],
  DROP TRIGGER [drop_trigger(l)]

Index for Section TRIGGER

Alphabetical listing for C

Top of page