src/sgml/trigger.sgml

<!-- doc/src/sgml/trigger.sgml -->

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

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

  <para>
   This chapter provides general information about writing trigger functions.
   Trigger functions can be written in most of the available procedural
   languages, including
   <application>PL/pgSQL</application> (<xref linkend="plpgsql"/>),
   <application>PL/Tcl</application> (<xref linkend="pltcl"/>),
   <application>PL/Perl</application> (<xref linkend="plperl"/>), and
   <application>PL/Python</application> (<xref linkend="plpython"/>).
   After reading this chapter, you should consult the chapter for
   your favorite procedural language to find out the language-specific
   details of writing a trigger in it.
  </para>

  <para>
   It is also possible to write a trigger function in C, although
   most people find it easier to use one of the procedural languages.
   It is not currently possible to write a trigger function in the
   plain SQL function language.
  </para>

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

   <para>
    A trigger is a specification that the database should automatically
    execute a particular function whenever a certain type of operation is
    performed.  Triggers can be attached to tables (partitioned or not),
    views, and foreign tables.
  </para>

  <para>
    On tables and foreign tables, triggers can be defined to execute either
    before or after any <command>INSERT</command>, <command>UPDATE</command>,
    or <command>DELETE</command> operation, either once per modified row,
    or once per <acronym>SQL</acronym> statement.
    <command>UPDATE</command> triggers can moreover be set to fire only if
    certain columns are mentioned in the <literal>SET</literal> clause of
    the <command>UPDATE</command> statement.  Triggers can also fire
    for <command>TRUNCATE</command> statements.  If a trigger event occurs,
    the trigger's function is called at the appropriate time to handle the
    event.
   </para>

   <para>
    On views, triggers can be defined to execute instead of
    <command>INSERT</command>, <command>UPDATE</command>, or
    <command>DELETE</command> operations.
    Such <literal>INSTEAD OF</literal> triggers
    are fired once for each row that needs to be modified in the view.
    It is the responsibility of the
    trigger's function to perform the necessary modifications to the view's
    underlying base table(s) and, where appropriate, return the modified
    row as it will appear in the view.  Triggers on views can also be defined
    to execute once per <acronym>SQL</acronym> statement, before or after
    <command>INSERT</command>, <command>UPDATE</command>, or
    <command>DELETE</command> operations.
    However, such triggers are fired only if there is also
    an <literal>INSTEAD OF</literal> trigger on the view.  Otherwise,
    any statement targeting the view must be rewritten into a statement
    affecting its underlying base table(s), and then the triggers
    that will be fired are the ones attached to the base table(s).
   </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</literal>.
    (The trigger function receives its input through a specially-passed
    <structname>TriggerData</structname> 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"/>.
    The same trigger function can be used for multiple triggers.
   </para>

   <para>
    <productname>PostgreSQL</productname> offers both <firstterm>per-row</firstterm>
    triggers and <firstterm>per-statement</firstterm> triggers.  With a per-row
    trigger, the trigger function
    is invoked once for each row that is affected by the statement
    that fired the trigger. In contrast, a per-statement trigger is
    invoked only once when an appropriate statement is executed,
    regardless of the number of rows affected by that statement. In
    particular, a statement that affects zero rows will still result
    in the execution of any applicable per-statement triggers. These
    two types of triggers are sometimes called <firstterm>row-level</firstterm>
    triggers and <firstterm>statement-level</firstterm> triggers,
    respectively. Triggers on <command>TRUNCATE</command> may only be
    defined at statement level, not per-row.
   </para>

   <para>
    Triggers are also classified according to whether they fire
    <firstterm>before</firstterm>, <firstterm>after</firstterm>, or
    <firstterm>instead of</firstterm> the operation. These are referred to
    as <literal>BEFORE</literal> triggers, <literal>AFTER</literal> triggers, and
    <literal>INSTEAD OF</literal> triggers respectively.
    Statement-level <literal>BEFORE</literal> triggers naturally fire before the
    statement starts to do anything, while statement-level <literal>AFTER</literal>
    triggers fire at the very end of the statement.  These types of
    triggers may be defined on tables, views, or foreign tables.  Row-level
    <literal>BEFORE</literal> triggers fire immediately before a particular row is
    operated on, while row-level <literal>AFTER</literal> triggers fire at the end of
    the statement (but before any statement-level <literal>AFTER</literal> triggers).
    These types of triggers may only be defined on tables and
    foreign tables, not views.
    <literal>INSTEAD OF</literal> triggers may only be
    defined on views, and only at row level; they fire immediately as each
    row in the view is identified as needing to be operated on.
   </para>

   <para>
    A statement that targets a parent table in an inheritance or partitioning
    hierarchy does not cause the statement-level triggers of affected child
    tables to be fired; only the parent table's statement-level triggers are
    fired.  However, row-level triggers of any affected child tables will be
    fired.
   </para>

   <para>
    If an <command>INSERT</command> contains an <literal>ON CONFLICT
    DO UPDATE</literal> clause, it is possible that the effects of
    row-level <literal>BEFORE</literal> <command>INSERT</command> triggers and
    row-level <literal>BEFORE</literal> <command>UPDATE</command> triggers can
    both be applied in a way that is apparent from the final state of
    the updated row, if an <varname>EXCLUDED</varname> column is referenced.
    There need not be an <varname>EXCLUDED</varname> column reference for
    both sets of row-level <literal>BEFORE</literal> triggers to execute,
    though.  The
    possibility of surprising outcomes should be considered when there
    are both <literal>BEFORE</literal> <command>INSERT</command> and
    <literal>BEFORE</literal> <command>UPDATE</command> row-level triggers
    that change a row being inserted/updated (this can be
    problematic even if the modifications are more or less equivalent, if
    they're not also idempotent).  Note that statement-level
    <command>UPDATE</command> triggers are executed when <literal>ON
    CONFLICT DO UPDATE</literal> is specified, regardless of whether or not
    any rows were affected by the <command>UPDATE</command> (and
    regardless of whether the alternative <command>UPDATE</command>
    path was ever taken).  An <command>INSERT</command> with an
    <literal>ON CONFLICT DO UPDATE</literal> clause will execute
    statement-level <literal>BEFORE</literal> <command>INSERT</command>
    triggers first, then statement-level <literal>BEFORE</literal>
    <command>UPDATE</command> triggers, followed by statement-level
    <literal>AFTER</literal> <command>UPDATE</command> triggers and finally
    statement-level <literal>AFTER</literal> <command>INSERT</command>
    triggers.
   </para>

   <para>
    If an <command>UPDATE</command> on a partitioned table causes a row to move
    to another partition, it will be performed as a <command>DELETE</command>
    from the original partition followed by an <command>INSERT</command> into
    the new partition. In this case, all row-level <literal>BEFORE</literal>
    <command>UPDATE</command> triggers and all row-level
    <literal>BEFORE</literal> <command>DELETE</command> triggers are fired on
    the original partition. Then all row-level <literal>BEFORE</literal>
    <command>INSERT</command> triggers are fired on the destination partition.
    The possibility of surprising outcomes should be considered when all these
    triggers affect the row being moved. As far as <literal>AFTER ROW</literal>
    triggers are concerned, <literal>AFTER</literal> <command>DELETE</command>
    and <literal>AFTER</literal> <command>INSERT</command> triggers are
    applied; but <literal>AFTER</literal> <command>UPDATE</command> triggers
    are not applied because the <command>UPDATE</command> has been converted to
    a <command>DELETE</command> and an <command>INSERT</command>. As far as
    statement-level triggers are concerned, none of the
    <command>DELETE</command> or <command>INSERT</command> triggers are fired,
    even if row movement occurs; only the <command>UPDATE</command> triggers
    defined on the target table used in the <command>UPDATE</command> statement
    will be fired.
   </para>

   <para>
    Trigger functions invoked by per-statement triggers should always
    return <symbol>NULL</symbol>. Trigger functions invoked by per-row
    triggers can return a table row (a value of
    type <structname>HeapTuple</structname>) to the calling executor,
    if they choose.  A row-level trigger fired before an operation has
    the following choices:

    <itemizedlist>
     <listitem>
      <para>
       It can return <symbol>NULL</symbol> to skip the operation for the
       current row. This instructs the executor to not perform the
       row-level operation that invoked the trigger (the insertion,
       modification, or deletion of a particular table row).
      </para>
     </listitem>

     <listitem>
      <para>
       For row-level <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 row-level <literal>BEFORE</literal> 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 <varname>NEW</varname> row
    for <command>INSERT</command> and <command>UPDATE</command>
    triggers, the <varname>OLD</varname> row for
    <command>DELETE</command> triggers).
   </para>

   <para>
    A row-level <literal>INSTEAD OF</literal> trigger should either return
    <symbol>NULL</symbol> to indicate that it did not modify any data from
    the view's underlying base tables, or it should return the view
    row that was passed in (the <varname>NEW</varname> row
    for <command>INSERT</command> and <command>UPDATE</command>
    operations, or the <varname>OLD</varname> row for
    <command>DELETE</command> operations). A nonnull return value is
    used to signal that the trigger performed the necessary data
    modifications in the view.  This will cause the count of the number
    of rows affected by the command to be incremented. For
    <command>INSERT</command> and <command>UPDATE</command> operations only, the trigger
    may modify the <varname>NEW</varname> row before returning it.  This will
    change the data returned by
    <command>INSERT RETURNING</command> or <command>UPDATE RETURNING</command>,
    and is useful when the view will not show exactly the same data
    that was provided.
   </para>

   <para>
    The return value is ignored for row-level triggers fired after an
    operation, and so they can return <symbol>NULL</symbol>.
   </para>

   <para>
    Some considerations apply for generated
    columns.<indexterm><primary>generated column</primary><secondary>in
    triggers</secondary></indexterm>  Stored generated columns are computed after
    <literal>BEFORE</literal> triggers and before <literal>AFTER</literal>
    triggers.  Therefore, the generated value can be inspected in
    <literal>AFTER</literal> triggers.  In <literal>BEFORE</literal> triggers,
    the <literal>OLD</literal> row contains the old generated value, as one
    would expect, but the <literal>NEW</literal> row does not yet contain the
    new generated value and should not be accessed.  In the C language
    interface, the content of the column is undefined at this point; a
    higher-level programming language should prevent access to a stored
    generated column in the <literal>NEW</literal> row in a
    <literal>BEFORE</literal> trigger.  Changes to the value of a generated
    column in a <literal>BEFORE</literal> trigger are ignored and will be
    overwritten.
   </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 <literal>BEFORE</literal> and
    <literal>INSTEAD OF</literal> triggers, the possibly-modified row returned by
    each trigger becomes the input to the next trigger.  If any
    <literal>BEFORE</literal> or <literal>INSTEAD OF</literal> trigger returns
    <symbol>NULL</symbol>, the operation is abandoned for that row and subsequent
    triggers are not fired (for that row).
   </para>

   <para>
    A trigger definition can also specify a Boolean <literal>WHEN</literal>
    condition, which will be tested to see whether the trigger should
    be fired.  In row-level triggers the <literal>WHEN</literal> condition can
    examine the old and/or new values of columns of the row.  (Statement-level
    triggers can also have <literal>WHEN</literal> conditions, although the feature
    is not so useful for them.)  In a <literal>BEFORE</literal> trigger, the
    <literal>WHEN</literal>
    condition is evaluated just before the function is or would be executed,
    so using <literal>WHEN</literal> is not materially different from testing the
    same condition at the beginning of the trigger function.  However, in
    an <literal>AFTER</literal> trigger, the <literal>WHEN</literal> condition is evaluated
    just after the row update occurs, and it determines whether an event is
    queued to fire the trigger at the end of statement.  So when an
    <literal>AFTER</literal> trigger's
    <literal>WHEN</literal> condition does not return true, it is not necessary
    to queue an event nor to re-fetch the row at end of statement.  This
    can result in significant speedups in statements that modify many
    rows, if the trigger only needs to be fired for a few of the rows.
    <literal>INSTEAD OF</literal> triggers do not support
    <literal>WHEN</literal> conditions.
   </para>

   <para>
    Typically, row-level <literal>BEFORE</literal> triggers are used for checking or
    modifying the data that will be inserted or updated.  For example,
    a <literal>BEFORE</literal> trigger might be used to insert the current time into a
    <type>timestamp</type> column, or to check that two elements of the row are
    consistent. Row-level <literal>AFTER</literal> triggers are most sensibly
    used to propagate the updates to other tables, or make consistency
    checks against other tables.  The reason for this division of labor is
    that an <literal>AFTER</literal> trigger can be certain it is seeing the final
    value of the row, while a <literal>BEFORE</literal> trigger cannot; there might
    be other <literal>BEFORE</literal> triggers firing after it.  If you have no
    specific reason to make a trigger <literal>BEFORE</literal> or
    <literal>AFTER</literal>, the <literal>BEFORE</literal> case is more efficient, since
    the information about
    the operation doesn't have to be saved until end of statement.
   </para>

   <para>
    If a trigger function executes SQL commands then these
    commands might 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>
    <indexterm>
     <primary>trigger</primary>
     <secondary>arguments for trigger functions</secondary>
    </indexterm>
    When a trigger is being defined, arguments can be specified for
    it. 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>

   <para>
    Each programming language that supports triggers has its own method
    for making the trigger input data available to the trigger function.
    This input data includes the type of trigger event (e.g.,
    <command>INSERT</command> or <command>UPDATE</command>) as well as any
    arguments that were listed in <command>CREATE TRIGGER</command>.
    For a row-level trigger, the input data also includes the
    <varname>NEW</varname> row for <command>INSERT</command> and
    <command>UPDATE</command> triggers, and/or the <varname>OLD</varname> row
    for <command>UPDATE</command> and <command>DELETE</command> triggers.
   </para>

   <para>
    By default, statement-level triggers do not have any way to examine the
    individual row(s) modified by the statement.  But an <literal>AFTER
    STATEMENT</literal> trigger can request that <firstterm>transition tables</firstterm>
    be created to make the sets of affected rows available to the trigger.
    <literal>AFTER ROW</literal> triggers can also request transition tables, so
    that they can see the total changes in the table as well as the change in
    the individual row they are currently being fired for.  The method for
    examining the transition tables again depends on the programming language
    that is being used, but the typical approach is to make the transition
    tables act like read-only temporary tables that can be accessed by SQL
    commands issued within the trigger function.
   </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>
       Statement-level triggers follow simple visibility rules: none of
       the changes made by a statement are visible to statement-level
       <literal>BEFORE</literal> triggers, whereas all
       modifications are visible to statement-level <literal>AFTER</literal>
       triggers.
      </para>
     </listitem>

     <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 row-level <literal>BEFORE</literal> trigger,
       because it hasn't happened yet.
      </para>
     </listitem>

     <listitem>
      <para>
       However, SQL commands executed in a row-level <literal>BEFORE</literal>
       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 can visit the rows in any order.
      </para>
     </listitem>

     <listitem>
      <para>
       Similarly, a row-level <literal>INSTEAD OF</literal> trigger will see the
       effects of data changes made by previous firings of <literal>INSTEAD
       OF</literal> triggers in the same outer command.
      </para>
     </listitem>

     <listitem>
      <para>
       When a row-level <literal>AFTER</literal> trigger is fired, all data
       changes made
       by the outer command are already complete, and are visible to
       the invoked trigger function.
      </para>
     </listitem>
    </itemizedlist>
   </para>

   <para>
    If your trigger function is written in any of the standard procedural
    languages, then the above statements apply only if the function is
    declared <literal>VOLATILE</literal>.  Functions that are declared
    <literal>STABLE</literal> or <literal>IMMUTABLE</literal> will not see changes made by
    the calling command in any case.
   </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>

   <indexterm>
    <primary>transition tables</primary>
    <secondary>referencing from C trigger</secondary>
   </indexterm>

   <para>
    This section describes the low-level details of the interface to a
    trigger function.  This information is only needed when writing
    trigger functions in C.  If you are using a higher-level language then
    these details are handled for you.  In most cases you should consider
    using a procedural language before writing your triggers in C.  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</quote> 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</quote>
    pointer pointing to a <structname>TriggerData</structname> 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)-&gt;context != NULL &amp;&amp; IsA((fcinfo)-&gt;context, TriggerData))
</programlisting>
    If this returns true, then it is safe to cast
    <literal>fcinfo-&gt;context</literal> to type <literal>TriggerData
    *</literal> and make use of the pointed-to
    <structname>TriggerData</structname> structure.  The function must
    <emphasis>not</emphasis> alter the <structname>TriggerData</structname>
    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;
    TupleTableSlot  *tg_trigslot;
    TupleTableSlot  *tg_newslot;
    Tuplestorestate *tg_oldtable;
    Tuplestorestate *tg_newtable;
    const Bitmapset *tg_updatedcols;
} TriggerData;
</programlisting>

    where the members are defined as follows:

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

     <varlistentry>
      <term><structfield>tg_event</structfield></term>
      <listitem>
       <para>
        Describes the event for which the function is called. You can 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_INSTEAD(tg_event)</literal></term>
          <listitem>
           <para>
            Returns true if the trigger fired instead of 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>
          </listitem>
         </varlistentry>

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

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

     <varlistentry>
      <term><structfield>tg_trigtuple</structfield></term>
      <listitem>
       <para>
        A pointer to the row for which the trigger was fired. This is
        the row being inserted, updated, or deleted.  If this trigger
        was fired for an <command>INSERT</command> or
        <command>DELETE</command> then this is what you should return
        from the function if you don't want to replace the row with
        a different one (in the case of <command>INSERT</command>) or
        skip the operation.  For triggers on foreign tables, values of system
        columns herein are unspecified.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><structfield>tg_newtuple</structfield></term>
      <listitem>
       <para>
        A pointer to the new version of the row, if the trigger was
        fired for an <command>UPDATE</command>, and <symbol>NULL</symbol> if
        it is for an <command>INSERT</command> or a
        <command>DELETE</command>. This is what you have to return
        from the function if the event is an <command>UPDATE</command>
        and you don't want to replace this row by a different one or
        skip the operation.  For triggers on foreign tables, values of system
        columns herein are unspecified.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><structfield>tg_trigger</structfield></term>
      <listitem>
       <para>
        A pointer to a structure of type <structname>Trigger</structname>,
        defined in <filename>utils/reltrigger.h</filename>:

<programlisting>
typedef struct Trigger
{
    Oid         tgoid;
    char       *tgname;
    Oid         tgfoid;
    int16       tgtype;
    char        tgenabled;
    bool        tgisinternal;
    Oid         tgconstrrelid;
    Oid         tgconstrindid;
    Oid         tgconstraint;
    bool        tgdeferrable;
    bool        tginitdeferred;
    int16       tgnargs;
    int16       tgnattr;
    int16      *tgattr;
    char      **tgargs;
    char       *tgqual;
    char       *tgoldtable;
    char       *tgnewtable;
} Trigger;
</programlisting>

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

     <varlistentry>
      <term><structfield>tg_trigslot</structfield></term>
      <listitem>
       <para>
        The slot containing <structfield>tg_trigtuple</structfield>,
        or a <symbol>NULL</symbol> pointer if there is no such tuple.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><structfield>tg_newslot</structfield></term>
      <listitem>
       <para>
        The slot containing <structfield>tg_newtuple</structfield>,
        or a <symbol>NULL</symbol> pointer if there is no such tuple.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><structfield>tg_oldtable</structfield></term>
      <listitem>
       <para>
        A pointer to a structure of type <structname>Tuplestorestate</structname>
        containing zero or more rows in the format specified by
        <structfield>tg_relation</structfield>, or a <symbol>NULL</symbol> pointer
        if there is no <literal>OLD TABLE</literal> transition relation.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><structfield>tg_newtable</structfield></term>
      <listitem>
       <para>
        A pointer to a structure of type <structname>Tuplestorestate</structname>
        containing zero or more rows in the format specified by
        <structfield>tg_relation</structfield>, or a <symbol>NULL</symbol> pointer
        if there is no <literal>NEW TABLE</literal> transition relation.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><structfield>tg_updatedcols</structfield></term>
      <listitem>
       <para>
        For <literal>UPDATE</literal> triggers, a bitmap set indicating the
        columns that were updated by the triggering command.  Generic trigger
        functions can use this to optimize actions by not having to deal with
        columns that were not changed.
       </para>

       <para>
        As an example, to determine whether a column with attribute number
        <varname>attnum</varname> (1-based) is a member of this bitmap set,
        call <literal>bms_is_member(attnum -
        FirstLowInvalidHeapAttributeNumber,
        trigdata->tg_updatedcols))</literal>.
       </para>

       <para>
        For triggers other than <literal>UPDATE</literal> triggers, this will
        be <symbol>NULL</symbol>.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>

   <para>
    To allow queries issued through SPI to reference transition tables, see
    <xref linkend="spi-spi-register-trigger-data"/>.
   </para>

   <para>
    A trigger function must return either a
    <structname>HeapTuple</structname> pointer or a <symbol>NULL</symbol> pointer
    (<emphasis>not</emphasis> an SQL null value, that is, do not set <parameter>isNull</parameter> true).
    Be careful to return either
    <structfield>tg_trigtuple</structfield> or <structfield>tg_newtuple</structfield>,
    as appropriate, if you don't want to modify the row being operated on.
   </para>
  </sect1>

  <sect1 id="trigger-example">
   <title>A Complete Trigger Example</title>

   <para>
    Here is a very simple example of a trigger function written in C.
    (Examples of triggers written in procedural languages can be found
    in the documentation of the procedural languages.)
   </para>

   <para>
    The function <function>trigf</function> reports the number of rows in the
    table <structname>ttest</structname> and skips the actual operation if the
    command attempts to insert a null value into the column
    <structfield>x</structfield>. (So the trigger acts as a not-null constraint but
    doesn't abort the transaction.)
   </para>

   <para>
    First, the table definition:
<programlisting>
CREATE TABLE ttest (
    x integer
);
</programlisting>
   </para>

   <para>
    This is the source code of the trigger function:
<programlisting><![CDATA[
#include "postgres.h"
#include "fmgr.h"
#include "executor/spi.h"       /* this is what you need to work with SPI */
#include "commands/trigger.h"   /* ... triggers ... */
#include "utils/rel.h"          /* ... and relations */

PG_MODULE_MAGIC;

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 it's called as a trigger at all */
    if (!CALLED_AS_TRIGGER(fcinfo))
        elog(ERROR, "trigf: not called 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(ERROR, "trigf (fired %s): SPI_connect returned %d", when, ret);

    /* get number of rows in table */
    ret = SPI_exec("SELECT count(*) FROM ttest", 0);

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

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

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

    SPI_finish();

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

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

   <para>
    After you have compiled the source code (see <xref
    linkend="dfunc"/>), declare the function and the triggers:
<programlisting>
CREATE FUNCTION trigf() RETURNS trigger
    AS '<replaceable>filename</replaceable>'
    LANGUAGE C;

CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest
    FOR EACH ROW EXECUTE FUNCTION trigf();

CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest
    FOR EACH ROW EXECUTE FUNCTION trigf();
</programlisting>
   </para>

   <para>
    Now you can test the operation of the trigger:
<screen>
=&gt; INSERT INTO ttest VALUES (NULL);
INFO:  trigf (fired before): there are 0 rows in ttest
INSERT 0 0

-- Insertion skipped and AFTER trigger is not fired

=&gt; SELECT * FROM ttest;
 x
---
(0 rows)

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

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

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

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

   </para>

   <para>
    There are more complex examples in
    <filename>src/test/regress/regress.c</filename> and
    in <xref linkend="contrib-spi"/>.
   </para>
  </sect1>
 </chapter>