Provided by: postgresql-client-8.0_8.0.7-2build1_i386 bug

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(7)] to remove a trigger.

EXAMPLES

       the documentation contains a complete example.

COMPATIBILITY

       The CREATE TRIGGER statement in PostgreSQL implements a subset  of  the
       SQL:1999  standard.  (There  are no provisions for triggers in SQL-92.)
       The following functionality is missing:

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

       · SQL:1999  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.  SQL:1999 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 user-defined
         function that executes the desired commands.

       SQL:1999 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(7)], ALTER TRIGGER [alter_trigger(l)],
       DROP TRIGGER [drop_trigger(l)]