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 opera-
       tion, regardless of how many rows it modifies (in particular, an opera-
       tion  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 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 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 standard. The following functionality is currently missing:

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

       o 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., CRE-
	 ATE 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.

       o 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-cre-
       ation 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 contraint 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.

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



SQL - Language Statements	  2010-12-14		      CREATE TRIGGER()