Seth Woolley's Man Viewer

create_trigger(7) - CREATE TRIGGER - define a new trigger - man 7 create_trigger

([section] manual, -k keyword, -K [section] search, -f whatis)
man plain no title

CREATE TRIGGER(7)                SQL Commands                CREATE TRIGGER(7)



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  func
       when certain events occur.

       The trigger can be specified to fire either before 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(7,n), 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 opera-
       tion, regardless of how many rows it modifies (in(1,8) particular, an opera-
       tion  that modifies zero rows will still result in(1,8) 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(1,8) alphabetical order by name.

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

       Refer to the chapter called ``Triggers'' in(1,8) 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.

       func   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(3,n) 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  acces-
              sible within the function; it may be different from normal func-
              tion arguments.

NOTES
       To create a trigger on a table, the user must have the  TRIGGER  privi-
       lege on the table.

       In  PostgreSQL versions before 7.3, it was necessary to declare trigger
       functions as returning the placeholder type opaque, rather  than  trig-
       ger. To support loading of old dump files, CREATE TRIGGER will accept(2,8) 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  chapter  called  ``Triggers'' in(1,8) the documentation contains a com-
       plete example.

COMPATIBILITY
       The CREATE TRIGGER statement in(1,8) PostgreSQL implements a subset  of  the
       SQL99  standard.  (There are no provisions for triggers in(1,8) SQL92.)  The
       following functionality is missing:

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

        SQL99  allows  you to define aliases for the ``old'' and ``new'' rows
         or tables for use in(1,8) 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(1,8)  any  number of user-defined languages, access(2,5) to the
         data is handled in(1,8) a language-specific way.

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


       SQL99 specifies that multiple triggers should be fired in(1,8)  time-of-cre-
       ation  order.  PostgreSQL uses name order, which was judged more conve-
       nient 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)]




SQL - Language Statements         2003-11-02                 CREATE TRIGGER(7)

References for this manual (incoming links)