path: root/doc/src/sgml/trigger.sgml

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/trigger.sgml,v 1.27 2003/03/25 16:15:38 petere Exp $
-->

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

  <para>
   <productname>PostgreSQL</productname> has various server-side
   function interfaces. Server-side functions can be written in
   <acronym>SQL</acronym>, C, or any defined procedural
   language. Trigger functions can be written in C and most procedural
   languages, but not in <acronym>SQL</acronym>. Both per-row and
   per-statement triggers are supported. A trigger procedure can
   execute BEFORE or AFTER a <command>INSERT</command>,
   <command>DELETE</command> or <command>UPDATE</command>, either once
   per modified row, or once per <acronym>SQL</acronym> statement.
  </para>

  <sect1 id="trigger-definition">
   <title>Trigger Definition</title>

   <para>
    If a trigger event occurs, the trigger manager (called by the
    Executor) sets up a <structname>TriggerData</> information
    structure (described below) and calls the trigger function 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 <structname>TriggerData</>
    structure, not in the form of ordinary function arguments.)
    If the function is written in C, it must use the <quote>version 1</>
    function manager interface.
   </para>

   <para>
    The syntax for creating triggers is described in <xref linkend="reference">.
   </para>

   <para>
    Trigger functions return a <structname>HeapTuple</> to the calling
    executor.  The return value is ignored for triggers fired AFTER an
    operation, but it allows BEFORE triggers to:

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

     <listitem>
      <para>
       For <command>INSERT</command> and <command>UPDATE</command>
       triggers only, the returned tuple becomes the tuple which will
       be inserted or will replace the tuple 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 the same NEW tuple it is passed.
   </para>

   <para>
    Note that there is no initialization performed by the
    <command>CREATE TRIGGER</command> handler.  This may be changed in
    the future.
   </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
    name.  In the case of BEFORE triggers, the possibly-modified tuple
    returned by each trigger becomes the input to the next trigger.
    If any BEFORE trigger returns <symbol>NULL</>, the operation is
    abandoned and subsequent triggers are not fired.
   </para>

   <para>
    If a trigger function executes SQL-queries (using SPI) then these
    queries 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 recursive invocation
    of the same trigger --- for example, an <command>INSERT</command>
    trigger might execute a query that inserts an additional tuple
    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 defined, a number of arguments can be
	specified. 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 field 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
	fields, 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-manager">
   <title>Interaction with the Trigger Manager</title>

   <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 function
    language then these details are handled for you.
   </para>

    <note>
     <para>
      The interface described here applies for
      <productname>PostgreSQL</productname> 7.1 and later.
      Earlier versions passed the <structname>TriggerData</> pointer in a global
      variable <varname>CurrentTriggerData</>.
     </para>
    </note>

   <para>
    When a function is called by the trigger manager, it is not passed
    any normal parameters, 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
    <literal>CALLED_AS_TRIGGER(fcinfo)</literal>, 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> if this is a trigger event.
       </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>TRIGGER_FIRED_BEFORE(tg_event)</term>
	  <listitem>
	   <para>
	    returns TRUE if trigger fired BEFORE.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>TRIGGER_FIRED_AFTER(tg_event)</term>
	  <listitem>
	   <para>
	    Returns TRUE if trigger fired AFTER.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>TRIGGER_FIRED_FOR_ROW(event)</term>
	  <listitem>
	   <para>
	    Returns TRUE if trigger fired for a ROW-level event.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>TRIGGER_FIRED_FOR_STATEMENT(event)</term>
	  <listitem>
	   <para>
	    Returns TRUE if trigger fired for STATEMENT-level event.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>TRIGGER_FIRED_BY_INSERT(event)</term>
	  <listitem>
	   <para>
	    Returns TRUE if trigger fired by <command>INSERT</command>.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>TRIGGER_FIRED_BY_DELETE(event)</term>
	  <listitem>
	   <para>
	    Returns TRUE if trigger fired by <command>DELETE</command>.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>TRIGGER_FIRED_BY_UPDATE(event)</term>
	  <listitem>
	   <para>
	    Returns TRUE if trigger fired by <command>UPDATE</command>.
	   </para>
	  </listitem>
	 </varlistentry>
	</variablelist>
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><structfield>tg_relation</></term>
      <listitem>
       <para>
	is a pointer to structure describing the triggered
	relation. Look at <filename>utils/rel.h</> for details about
	this structure.  The most interesting things are
	<literal>tg_relation->rd_att</> (descriptor of the relation
	tuples) and <literal>tg_relation->rd_rel->relname</>
	(relation's name. This is not <type>char*</>, but
	<type>NameData</>.  Use
	<literal>SPI_getrelname(tg_relation)</> to get <type>char*</> if you
	need a copy of the name).
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><structfield>tg_trigtuple</></term>
      <listitem>
       <para>
	is a pointer to the tuple for which the trigger is fired. This is
	the tuple being inserted (if <command>INSERT</command>), deleted
	(if <command>DELETE</command>) or updated (if
	<command>UPDATE</command>).  If this trigger was fired for an
	<command>INSERT</command> or <command>DELETE</command> then this
	is what you should return to the Executor if you don't want to
	replace the tuple with a different one (in the case of
	<command>INSERT</command>) or skip the operation (in the case of
	<command>DELETE</command>).
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><structfield>tg_newtuple</></term>
      <listitem>
       <para>
	is a pointer to the new version of tuple if
	<command>UPDATE</command> and <symbol>NULL</> if this is for an
	<command>INSERT</command> or a <command>DELETE</command>. This is
	what you are to return to Executor if <command>UPDATE</command>
	and you don't want to replace this tuple with another one or skip
	the operation.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><structfield>tg_trigger</></term>
      <listitem>
       <para>
	is pointer to structure <structname>Trigger</> defined in <filename>utils/rel.h</>:

<programlisting>
typedef struct Trigger
{
    Oid         tgoid;
    char       *tgname;
    Oid         tgfoid;
    int16       tgtype;
    bool        tgenabled;
    bool        tgisconstraint;
    Oid         tgconstrrelid;
    bool        tgdeferrable;
    bool        tginitdeferred;
    int16       tgnargs;
    int16       tgattr[FUNC_MAX_ARGS];
    char      **tgargs;
} Trigger;
</programlisting>

       where <structfield>tgname</> is the trigger's name,
       <structfield>tgnargs</> is number of arguments in
       <structfield>tgargs</>, <structfield>tgargs</> is an array of
       pointers to the arguments specified in the <command>CREATE
       TRIGGER</command> statement. Other members are for internal use
       only.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </sect1>

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

   <para>
    <productname>PostgreSQL</productname> data changes visibility rule: during a query execution, data
    changes made by the query itself (via SQL-function, SPI-function, triggers)
    are invisible to the query scan.  For example, in query

<programlisting>
INSERT INTO a SELECT * FROM a;
</programlisting>

    tuples inserted are invisible for SELECT scan.  In effect, this
    duplicates the database table within itself (subject to unique index
    rules, of course) without recursing.
   </para>

   <para>
    But keep in mind this notice about visibility in the SPI documentation:

    <blockquote>
     <para>
Changes made by query Q are visible by queries that are started after
query Q, no matter whether they are started inside Q (during the
execution of Q) or after Q is done.
     </para>
    </blockquote>
   </para>

   <para>
    This is true for triggers as well so, though a tuple being inserted
    (<structfield>tg_trigtuple</>) is not visible to queries in a BEFORE trigger, this tuple
    (just inserted) is visible to queries in an AFTER trigger, and to queries
    in BEFORE/AFTER triggers fired after this!
   </para>
  </sect1>

  <sect1 id="trigger-examples">
   <title>Examples</title>

   <para>
    There are more complex examples in
    <filename>src/test/regress/regress.c</filename> and
    in <filename>contrib/spi</filename>.
   </para>

   <para>
    Here is a very simple example of trigger usage.  Function
    <function>trigf</> reports the number of tuples in the triggered
    relation <literal>ttest</> and skips the operation if the query
    attempts to insert a null value into x (i.e - it acts as a
    <literal>NOT NULL</literal> constraint but doesn't abort the
    transaction).

<programlisting>
#include "executor/spi.h"       /* this is what you need to work with SPI */
#include "commands/trigger.h"   /* -"- and triggers */

extern Datum trigf(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(trigf);

Datum
trigf(PG_FUNCTION_ARGS)
{
    TriggerData *trigdata = (TriggerData *) fcinfo->context;
    TupleDesc   tupdesc;
    HeapTuple   rettuple;
    char       *when;
    bool        checknull = false;
    bool        isnull;
    int         ret, i;

    /* Make sure trigdata is pointing at what I expect */
    if (!CALLED_AS_TRIGGER(fcinfo))
        elog(ERROR, "trigf: not fired by trigger manager");

    /* tuple to return to Executor */
    if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
        rettuple = trigdata->tg_newtuple;
    else
        rettuple = trigdata->tg_trigtuple;

    /* check for null values */
    if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)
        && TRIGGER_FIRED_BEFORE(trigdata->tg_event))
        checknull = true;

    if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))
        when = "before";
    else
        when = "after ";

    tupdesc = trigdata->tg_relation->rd_att;

    /* Connect to SPI manager */
    if ((ret = SPI_connect()) < 0)
        elog(INFO, "trigf (fired %s): SPI_connect returned %d", when, ret);

    /* Get number of tuples in relation */
    ret = SPI_exec("SELECT count(*) FROM ttest", 0);

    if (ret < 0)
        elog(NOTICE, "trigf (fired %s): SPI_exec returned %d", when, ret);

    /* count(*) returns int8 as of PG 7.2, so be careful to convert */
    i = (int) DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0],
                                          SPI_tuptable->tupdesc,
                                          1,
                                          &amp;isnull));

    elog (NOTICE, "trigf (fired %s): there are %d tuples in ttest", when, i);

    SPI_finish();

    if (checknull)
    {
        (void) SPI_getbinval(rettuple, tupdesc, 1, &amp;isnull);
        if (isnull)
            rettuple = NULL;
    }

    return PointerGetDatum(rettuple);
}
</programlisting>
   </para>

   <para>
    Now, compile and create the trigger function:

<programlisting>
CREATE FUNCTION trigf () RETURNS TRIGGER AS 
'...path_to_so' LANGUAGE C;

CREATE TABLE ttest (x int4);
</programlisting>

<programlisting>
vac=> CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest 
FOR EACH ROW EXECUTE PROCEDURE trigf();
CREATE
vac=> CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest 
FOR EACH ROW EXECUTE PROCEDURE trigf();
CREATE
vac=> INSERT INTO ttest VALUES (NULL);
WARNING:  trigf (fired before): there are 0 tuples in ttest
INSERT 0 0

-- Insertion skipped and AFTER trigger is not fired

vac=> SELECT * FROM ttest;
 x
---
(0 rows)

vac=> INSERT INTO ttest VALUES (1);
INFO:  trigf (fired before): there are 0 tuples in ttest
INFO:  trigf (fired after ): there are 1 tuples in ttest
                                       ^^^^^^^^
                             remember what we said about visibility.
INSERT 167793 1
vac=> SELECT * FROM ttest;
 x
---
 1
(1 row)

vac=> INSERT INTO ttest SELECT x * 2 FROM ttest;
INFO:  trigf (fired before): there are 1 tuples in ttest
INFO:  trigf (fired after ): there are 2 tuples in ttest
                                       ^^^^^^^^
                             remember what we said about visibility.
INSERT 167794 1
vac=> SELECT * FROM ttest;
 x
---
 1
 2
(2 rows)

vac=> UPDATE ttest SET x = NULL WHERE x = 2;
INFO:  trigf (fired before): there are 2 tuples in ttest
UPDATE 0
vac=> UPDATE ttest SET x = 4 WHERE x = 2;
INFO:  trigf (fired before): there are 2 tuples in ttest
INFO:  trigf (fired after ): there are 2 tuples in ttest
UPDATE 1
vac=> SELECT * FROM ttest;
 x
---
 1
 4
(2 rows)

vac=> DELETE FROM ttest;
INFO:  trigf (fired before): there are 2 tuples in ttest
INFO:  trigf (fired after ): there are 1 tuples in ttest
INFO:  trigf (fired before): there are 1 tuples in ttest
INFO:  trigf (fired after ): there are 0 tuples in ttest
                                       ^^^^^^^^
                             remember what we said about visibility.
DELETE 2
vac=> SELECT * FROM ttest;
 x
---
(0 rows)
</programlisting>

   </para>
  </sect1>
 </chapter>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->