create_trigger man page on Mandriva

Man page or keyword search:  
man Server   17060 pages
apropos Keyword Search (all sections)
Output format
Mandriva logo
[printable version]

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‐
       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 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 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).

       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	speci‐
	      fied 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	acces‐
	      sible  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	privi‐
       lege 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	 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.

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.,  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.

       · 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 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)]

SQL - Language Statements	  2010-10-01		     CREATE TRIGGER(7)
[top]

List of man pages available for Mandriva

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Tweet
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
....................................................................
Vote for polarhome
Free Shell Accounts :: the biggest list on the net