| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/create_trigger.sgml,v 1.45 2006/10/23 18:10:32 petere Exp $ |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="SQL-CREATETRIGGER"> |
| <refmeta> |
| <refentrytitle id="SQL-CREATETRIGGER-TITLE">CREATE TRIGGER</refentrytitle> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>CREATE TRIGGER</refname> |
| <refpurpose>define a new trigger (user-defined triggers are not fully supported in Greenplum Database)</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-createtrigger"> |
| <primary>CREATE TRIGGER</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTER } { <replaceable class="PARAMETER">event</replaceable> [ OR ... ] } |
| ON <replaceable class="PARAMETER">table</replaceable> [ FOR [ EACH ] { ROW | STATEMENT } ] |
| EXECUTE PROCEDURE <replaceable class="PARAMETER">funcname</replaceable> ( <replaceable class="PARAMETER">arguments</replaceable> ) |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1> |
| <title>Description</title> |
| |
| <para> |
| <command>CREATE TRIGGER</command> creates a new trigger. The |
| trigger will be associated with the specified table and will |
| execute the specified function <replaceable |
| class="parameter">funcname</replaceable> when certain events occur. |
| </para> |
| |
| <para> |
| The trigger can be specified to fire either before the |
| operation is attempted on a row (before constraints are checked and |
| the <command>INSERT</command>, <command>UPDATE</command>, or |
| <command>DELETE</command> is attempted) or after the operation has |
| completed (after constraints are checked and the |
| <command>INSERT</command>, <command>UPDATE</command>, or |
| <command>DELETE</command> 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 |
| <command>INSERT</command> and <command>UPDATE</command> operations |
| only). If the trigger fires after the event, all changes, including |
| the last insertion, update, or deletion, are <quote>visible</quote> |
| to the trigger. |
| </para> |
| |
| <para> |
| A trigger that is marked <literal>FOR EACH ROW</literal> is called |
| once for every row that the operation modifies. For example, a |
| <command>DELETE</command> that affects 10 rows will cause any |
| <literal>ON DELETE</literal> triggers on the target relation to be |
| called 10 separate times, once for each deleted row. In contrast, a |
| trigger that is marked <literal>FOR EACH STATEMENT</literal> 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 <literal>FOR |
| EACH STATEMENT</literal> triggers). |
| </para> |
| |
| <para> |
| If multiple triggers of the same kind are defined for the same event, |
| they will be fired in alphabetical order by name. |
| </para> |
| |
| <para> |
| <command>SELECT</command> does not modify any rows so you can not |
| create <command>SELECT</command> triggers. Rules and views are more |
| appropriate in such cases. |
| </para> |
| |
| <para> |
| Refer to <xref linkend="triggers"> for more information about triggers. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Parameters</title> |
| |
| <variablelist> |
| <varlistentry> |
| <term><replaceable class="parameter">name</replaceable></term> |
| <listitem> |
| <para> |
| The name to give the new trigger. This must be distinct from |
| the name of any other trigger for the same table. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>BEFORE</literal></term> |
| <term><literal>AFTER</literal></term> |
| <listitem> |
| <para> |
| Determines whether the function is called before or after the |
| event. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">event</replaceable></term> |
| <listitem> |
| <para> |
| One of <command>INSERT</command>, <command>UPDATE</command>, or |
| <command>DELETE</command>; this specifies the event that will |
| fire the trigger. Multiple events can be specified using |
| <literal>OR</literal>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">table</replaceable></term> |
| <listitem> |
| <para> |
| The name (optionally schema-qualified) of the table the trigger |
| is for. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>FOR EACH ROW</literal></term> |
| <term><literal>FOR EACH STATEMENT</literal></term> |
| |
| <listitem> |
| <para> |
| 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, <literal>FOR EACH |
| STATEMENT</literal> is the default. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">funcname</replaceable></term> |
| <listitem> |
| <para> |
| A user-supplied function that is declared as taking no arguments |
| and returning type <literal>trigger</>, which is executed when |
| the trigger fires. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">arguments</replaceable></term> |
| <listitem> |
| <para> |
| 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 accessible within the function; it may be different from |
| normal function arguments. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| </refsect1> |
| |
| <refsect1 id="SQL-CREATETRIGGER-notes"> |
| <title>Notes</title> |
| |
| <para> |
| To create a trigger on a table, the user must have the |
| <literal>TRIGGER</literal> privilege on the table. |
| </para> |
| |
| <para> |
| In <productname>PostgreSQL</productname> versions before 7.3, it was |
| necessary to declare trigger functions as returning the placeholder |
| type <type>opaque</>, rather than <type>trigger</>. To support loading |
| of old dump files, <command>CREATE TRIGGER</> will accept a function |
| declared as returning <type>opaque</>, but it will issue a notice and |
| change the function's declared return type to <type>trigger</>. |
| </para> |
| |
| <para> |
| Use <xref linkend="sql-droptrigger" |
| endterm="sql-droptrigger-title"> to remove a trigger. |
| </para> |
| </refsect1> |
| |
| <refsect1 id="R1-SQL-CREATETRIGGER-2"> |
| <title>Examples</title> |
| |
| <para> |
| <xref linkend="trigger-example"> contains a complete example. |
| </para> |
| </refsect1> |
| |
| <refsect1 id="SQL-CREATETRIGGER-compatibility"> |
| <title>Compatibility</title> |
| |
| <para> |
| The <command>CREATE TRIGGER</command> statement in |
| <productname>PostgreSQL</productname> implements a subset of the |
| <acronym>SQL</> standard. The following functionality is currently missing: |
| |
| <itemizedlist> |
| <listitem> |
| <para> |
| SQL allows triggers to fire on updates to specific columns |
| (e.g., <literal>AFTER UPDATE OF col1, col2</literal>). |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| SQL allows you to define aliases for the <quote>old</quote> |
| and <quote>new</quote> rows or tables for use in the definition |
| of the triggered action (e.g., <literal>CREATE TRIGGER ... ON |
| tablename REFERENCING OLD ROW AS somename NEW ROW AS othername |
| ...</literal>). Since <productname>PostgreSQL</productname> |
| allows trigger procedures to be written in any number of |
| user-defined languages, access to the data is handled in a |
| language-specific way. |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| <productname>PostgreSQL</productname> 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 |
| <command>CREATE TABLE</command> as the triggered action. This |
| limitation is not hard to work around by creating a user-defined |
| function that executes the desired commands. |
| </para> |
| </listitem> |
| |
| </itemizedlist> |
| </para> |
| |
| <para> |
| SQL specifies that multiple triggers should be fired in |
| time-of-creation order. <productname>PostgreSQL</productname> uses |
| name order, which was judged to be more convenient. |
| </para> |
| |
| <para> |
| SQL specifies that <literal>BEFORE DELETE</literal> triggers on cascaded |
| deletes fire <emphasis>after</> the cascaded <literal>DELETE</> completes. |
| The <productname>PostgreSQL</productname> behavior is for <literal>BEFORE |
| DELETE</literal> to always fire before the delete action, even a cascading |
| one. This is considered more consistent. There is also unpredictable |
| behavior when <literal>BEFORE</literal> 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. |
| </para> |
| |
| <para> |
| The ability to specify multiple actions for a single trigger using |
| <literal>OR</literal> is a <productname>PostgreSQL</> extension of |
| the SQL standard. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>See Also</title> |
| |
| <simplelist type="inline"> |
| <member><xref linkend="sql-createfunction" endterm="sql-createfunction-title"></member> |
| <member><xref linkend="sql-altertrigger" endterm="sql-altertrigger-title"></member> |
| <member><xref linkend="sql-droptrigger" endterm="sql-droptrigger-title"></member> |
| </simplelist> |
| </refsect1> |
| </refentry> |