create_trigger

CREATE TRIGGER()		 SQL Commands		     CREATE TRIGGER()



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-
       name 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  particu-
       lar,  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 [XRef to TRIGGERS] 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	argu-
	      ments  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 argu-
	      ments 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 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
       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
       [XRef to TRIGGER-EXAMPLE] contains a complete example.

COMPATIBILITY
       The CREATE TRIGGER statement in PostgreSQL implements a subset of  the
       SQL99  standard. (There are no provisions for triggers in 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 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.  SQL99	 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.


       SQL99 specifies that multiple triggers should be fired in 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_trig-
       ger(l)], DROP TRIGGER [drop_trigger(l)]



SQL - Language Statements	  2008-01-03		     CREATE TRIGGER()