trigger.sgml

PostgreSQL7.4.6 for Linux

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/trigger.sgml,v 1.31.2.1 2003/11/15 19:46:36 tgl Exp $
-->

 <chapter id="triggers">
  <title>Triggers</title>

  <indexterm zone="triggers">
   <primary>trigger</primary>
  </indexterm>

  <para>
   This chapter describes how to write trigger functions.  Trigger
   functions can be written in C or in some of the available procedural
   languages.  It is not currently possible to write a SQL-language
   trigger function.
  </para>

  <sect1 id="trigger-definition">
   <title>Overview of Trigger Behavior</title>

   <para>
    A trigger can be defined to execute before or after an
    <command>INSERT</command>, <command>UPDATE</command>, or
    <command>DELETE</command> operation, either once per modified row,
    or once per <acronym>SQL</acronym> statement.
    If a trigger event occurs, the trigger's function is called
    at the appropriate time to handle the event.
   </para>

   <para>
    The trigger function must be defined before the trigger itself can be
    created.  The trigger function must be declared as a 
    function taking no arguments and returning type <literal>trigger</>.
    (The trigger function receives its input through a specially-passed
    <structname>TriggerData</> structure, not in the form of ordinary function
    arguments.)
   </para>

   <para>
    Once a suitable trigger function has been created, the trigger is
    established with
    <xref linkend="sql-createtrigger" endterm="sql-createtrigger-title">.
    The same trigger function can be used for multiple triggers.
   </para>

   <para>
    Trigger functions return a table row (a value of type
    <structname>HeapTuple</>) to the calling executor.
    A trigger fired before an operation has the following choices:

    <itemizedlist>
     <listitem>
      <para>
       It can return a <symbol>NULL</> pointer to skip the operation
       for the current row (and so the row will not be
       inserted/updated/deleted).
      </para>
     </listitem>

     <listitem>
      <para>
       For <command>INSERT</command> and <command>UPDATE</command>
       triggers only, the returned row becomes the row that will
       be inserted or will replace the row being updated.  This
       allows the trigger function to modify the row being inserted or
       updated.
      </para>
     </listitem>
    </itemizedlist>

    A before trigger that does not intend to cause either of these
    behaviors must be careful to return as its result the same row that was
    passed in (that is, the NEW row for <command>INSERT</command> and
    <command>UPDATE</command> triggers, the OLD row for
    <command>DELETE</command> triggers).
   </para>

   <para>
    The return
    value is ignored for triggers fired after an operation, and so
    they may as well return <symbol>NULL</>.
   </para>

   <para>
    If more than one trigger is defined for the same event on the same
    relation, the triggers will be fired in alphabetical order by trigger
    name.  In the case of before triggers, the possibly-modified row
    returned by each trigger becomes the input to the next trigger.
    If any before trigger returns a <symbol>NULL</> pointer, the
    operation is abandoned and subsequent triggers are not fired.
   </para>

   <para>
    If a trigger function executes SQL commands then these
    commands may fire triggers again. This is known as cascading
    triggers.  There is no direct limitation on the number of cascade
    levels.  It is possible for cascades to cause a recursive invocation
    of the same trigger; for example, an <command>INSERT</command>
    trigger might execute a command that inserts an additional row
    into the same table, causing the <command>INSERT</command> trigger
    to be fired again.  It is the trigger programmer's responsibility
    to avoid infinite recursion in such scenarios.
   </para>

   <para>
    When a trigger is being defined, arguments can be specified for
    it.<indexterm><primary>trigger</><secondary>arguments for trigger
    functions</></indexterm> The purpose of including arguments in the
    trigger definition is to allow different triggers with similar
    requirements to call the same function.  As an example, there
    could be a generalized trigger function that takes as its
    arguments two column names and puts the current user in one and
    the current time stamp in the other.  Properly written, this
    trigger function would be independent of the specific table it is
    triggering on.  So the same function could be used for
    <command>INSERT</command> events on any table with suitable
    columns, to automatically track creation of records in a
    transaction table for example. It could also be used to track
    last-update events if defined as an <command>UPDATE</command>
    trigger.
   </para>

  </sect1>

  <sect1 id="trigger-datachanges">
   <title>Visibility of Data Changes</title>

   <para>
    If you execute SQL commands in your trigger function, and these
    commands access the table that the trigger is for, then
    you need to be aware of the data visibility rules, because they determine
    whether these SQL commands will see the data change that the trigger
    is fired for.  Briefly:

    <itemizedlist>
     <listitem>
      <para>
       The data change (insertion, update, or deletion) causing the trigger
       to fire is naturally
       <emphasis>not</emphasis> visible to SQL commands executed in a
       before trigger, because it hasn't happened yet.
      </para>
     </listitem>

     <listitem>
      <para>
       However, SQL commands executed in a before trigger
       <emphasis>will</emphasis> see the effects of data changes
       for rows previously processed in the same outer command.  This
       requires caution, since the ordering of these change events
       is not in general predictable; a SQL command that affects
       multiple rows may visit the rows in any order.
      </para>
     </listitem>

     <listitem>
      <para>
       When an after trigger is fired, all data changes made by the outer
       command are already complete, and are visible to executed SQL commands.
      </para>
     </listitem>
    </itemizedlist>
   </para>

   <para>
    Further information about data visibility rules can be found in
    <xref linkend="spi-visibility">.  The example in <xref
    linkend="trigger-example"> contains a demonstration of these rules.
   </para>
  </sect1>

  <sect1 id="trigger-interface">
   <title>Writing Trigger Functions in C</title>

   <indexterm zone="trigger-interface">
    <primary>trigger</primary>
    <secondary>in C</secondary>
   </indexterm>

   <para>
    This section describes the low-level details of the interface to a
    trigger function.  This information is only needed when writing a
    trigger function in C.  If you are using a higher-level
    language then these details are handled for you.  The documentation
    of each procedural language explains how to write a trigger in that
    language.
   </para>

   <para>
    Trigger functions must use the <quote>version 1</> function manager
    interface.
   </para>

   <para>
    When a function is called by the trigger manager, it is not passed
    any normal arguments, but it is passed a <quote>context</>
    pointer pointing to a <structname>TriggerData</> structure.  C
    functions can check whether they were called from the trigger
    manager or not by executing the macro
<programlisting>
CALLED_AS_TRIGGER(fcinfo)
</programlisting>
    which expands to
<programlisting>
((fcinfo)->context != NULL && IsA((fcinfo)->context, TriggerData))
</programlisting>
    If this returns true, then it is safe to cast
    <literal>fcinfo->context</> to type <literal>TriggerData
    *</literal> and make use of the pointed-to
    <structname>TriggerData</> structure.  The function must
    <emphasis>not</emphasis> alter the <structname>TriggerData</>
    structure or any of the data it points to.
   </para>

   <para>
    <structname>struct TriggerData</structname> is defined in
    <filename>commands/trigger.h</filename>:

<programlisting>
typedef struct TriggerData
{
    NodeTag       type;
    TriggerEvent  tg_event;
    Relation      tg_relation;
    HeapTuple     tg_trigtuple;
    HeapTuple     tg_newtuple;
    Trigger      *tg_trigger;
} TriggerData;
</programlisting>

    where the members are defined as follows:

    <variablelist>
     <varlistentry>
      <term><structfield>type</></term>
      <listitem>
       <para>
        Always <literal>T_TriggerData</literal>.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><structfield>tg_event</></term>
      <listitem>
       <para>
	Describes the event for which the function is called. You may use the
	following macros to examine <literal>tg_event</literal>:

	<variablelist>
	 <varlistentry>
	  <term><literal>TRIGGER_FIRED_BEFORE(tg_event)</literal></term>
	  <listitem>
	   <para>
	    Returns true if the trigger fired before the operation.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term><literal>TRIGGER_FIRED_AFTER(tg_event)</literal></term>
	  <listitem>
	   <para>
	    Returns true if the trigger fired after the operation.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term><literal>TRIGGER_FIRED_FOR_ROW(tg_event)</literal></term>
	  <listitem>
	   <para>
	    Returns true if the trigger fired for a row-level event.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term><literal>TRIGGER_FIRED_FOR_STATEMENT(tg_event)</literal></term>
	  <listitem>
	   <para>
	    Returns true if the trigger fired for a statement-level event.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term><literal>TRIGGER_FIRED_BY_INSERT(tg_event)</literal></term>
	  <listitem>
	   <para>
	    Returns true if the trigger was fired by an <command>INSERT</command> command.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term><literal>TRIGGER_FIRED_BY_UPDATE(tg_event)</literal></term>
	  <listitem>
	   <para>
	    Returns true if the trigger was fired by an <command>UPDATE</command> command.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term><literal>TRIGGER_FIRED_BY_DELETE(tg_event)</literal></term>
	  <listitem>
	   <para>
	    Returns true if the trigger was fired by a <command>DELETE</command> command.
	   </para>