Man Linux: Main Page and Category List


       CREATE TRIGGER - define a new trigger


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


       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

       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.


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


       AFTER  Determines whether the function is called before  or  after  the

       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.


              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.

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

              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.


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

       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.


       in the documentation contains a complete example.


       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

       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.


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