Provided by: postgresql-client-8.4_8.4.11-1_amd64 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 can 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).

       In addition, triggers may be defined  to  fire  for  a  TRUNCATE,  though  only  FOR  EACH
       STATEMENT.

       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 cannot create SELECT triggers. Rules and views  are
       more appropriate in such cases.

       Refer to in 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, DELETE, or TRUNCATE; 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 can 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
              might be different from normal function arguments.

NOTES

       To create a trigger on a table, the user must have the TRIGGER privilege on the table. The
       user must also have EXECUTE privilege on the trigger function.

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

       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.

EXAMPLES

       in 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 to be more convenient.

       SQL  specifies  that  BEFORE  DELETE  triggers on cascaded deletes fire after the cascaded
       DELETE completes.  The PostgreSQL behavior is for BEFORE DELETE to always fire before  the
       delete  action,  even  a  cascading one. This is considered more consistent. There is also
       unpredictable behavior when BEFORE triggers modify rows that are later to be  modified  by
       referential  actions.  This can lead to constraint violations or stored data that does not
       honor the referential constraint.

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

       The ability to fire triggers for TRUNCATE is a PostgreSQL extension of the SQL standard.

SEE ALSO

       CREATE  FUNCTION  [create_function(7)],  ALTER  TRIGGER  [alter_trigger(7)],  DROP TRIGGER
       [drop_trigger(7)]