trigger.sgml

来自「PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统」· SGML 代码 · 共 735 行 · 第 1/2 页

<!--
$PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.45 2005/11/04 23:14:02 petere Exp $
-->

 <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 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.
    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>
    <productname>PostgreSQL</productname> offers both <firstterm>per-row</>
    triggers and <firstterm>per-statement</> 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</>
    triggers and <firstterm>statement-level</> triggers,
    respectively.
   </para>

   <para>
    Triggers are also classified as <firstterm>before</> triggers and
    <firstterm>after</> triggers.
    Statement-level before triggers naturally fire before the
    statement starts to do anything, while statement-level after
    triggers fire at the very end of the statement.  Row-level before
    triggers fire immediately before a particular row is operated on,
    while row-level after triggers fire at the end of the statement
    (but before any statement-level after triggers).
   </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</> 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 or
       modification 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 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 <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>
    The return value is ignored for row-level 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
    <symbol>NULL</>, the operation is abandoned for that row and subsequent
    triggers are not fired.
   </para>

   <para>
    Typically, row before triggers are used for checking or
    modifying the data that will be inserted or updated.  For example,
    a before 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 after 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 after trigger can be certain it is seeing the final value of the
    row, while a before trigger cannot; there might be other before triggers
    firing after it.  If you have no specific reason to make a trigger before
    or after, the before 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 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>

   <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</>.
    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.
    Statement-level triggers do not currently have any way to examine the
    individual row(s) modified by the statement.
   </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
       triggers that are invoked before the statement, whereas all
       modifications are visible to statement-level after 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 before trigger, because
       it hasn't happened yet.
      </para>
     </listitem>

     <listitem>
      <para>
       However, SQL commands executed in a row-level 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 a row-level after 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>
    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
    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</> 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)-&gt;context != NULL && IsA((fcinfo)-&gt;context, TriggerData))
</programlisting>
    If this returns true, then it is safe to cast
    <literal>fcinfo-&gt;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;
    Buffer        tg_trigtuplebuf;
    Buffer        tg_newtuplebuf;
} 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>