doc/src/sgml/trigger.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3  <chapter id="triggers">
   4   <title>Triggers</title>
   5
   6   <indexterm zone="triggers">
   7    <primary>trigger</primary>
   8   </indexterm>
   9
  10   <para>
  11    This chapter provides general information about writing trigger functions.
  12    Trigger functions can be written in most of the available procedural
  13    languages, including
  14    <application>PL/pgSQL</application> (<xref linkend="plpgsql">),
  15    <application>PL/Tcl</application> (<xref linkend="pltcl">),
  16    <application>PL/Perl</application> (<xref linkend="plperl">), and
  17    <application>PL/Python</application> (<xref linkend="plpython">).
  18    After reading this chapter, you should consult the chapter for
  19    your favorite procedural language to find out the language-specific
  20    details of writing a trigger in it.
  21   </para>
  22
  23   <para>
  24    It is also possible to write a trigger function in C, although
  25    most people find it easier to use one of the procedural languages.
  26    It is not currently possible to write a trigger function in the
  27    plain SQL function language.
  28   </para>
  29
  30   <sect1 id="trigger-definition">
  31    <title>Overview of Trigger Behavior</title>
  32
  33    <para>
  34     A trigger is a specification that the database should automatically
  35     execute a particular function whenever a certain type of operation is
  36     performed.  Triggers can be defined to execute either before or after any
  37     <command>INSERT</command>, <command>UPDATE</command>, or
  38     <command>DELETE</command> operation, either once per modified row,
  39     or once per <acronym>SQL</acronym> statement.  Triggers can also fire
  40     for <command>TRUNCATE</command> statements.  If a trigger event occurs,
  41     the trigger's function is called at the appropriate time to handle the
  42     event.
  43    </para>
  44
  45    <para>
  46     The trigger function must be defined before the trigger itself can be
  47     created.  The trigger function must be declared as a
  48     function taking no arguments and returning type <literal>trigger</>.
  49     (The trigger function receives its input through a specially-passed
  50     <structname>TriggerData</> structure, not in the form of ordinary function
  51     arguments.)
  52    </para>
  53
  54    <para>
  55     Once a suitable trigger function has been created, the trigger is
  56     established with
  57     <xref linkend="sql-createtrigger" endterm="sql-createtrigger-title">.
  58     The same trigger function can be used for multiple triggers.
  59    </para>
  60
  61    <para>
  62     <productname>PostgreSQL</productname> offers both <firstterm>per-row</>
  63     triggers and <firstterm>per-statement</> triggers.  With a per-row
  64     trigger, the trigger function
  65     is invoked once for each row that is affected by the statement
  66     that fired the trigger. In contrast, a per-statement trigger is
  67     invoked only once when an appropriate statement is executed,
  68     regardless of the number of rows affected by that statement. In
  69     particular, a statement that affects zero rows will still result
  70     in the execution of any applicable per-statement triggers. These
  71     two types of triggers are sometimes called <firstterm>row-level</>
  72     triggers and <firstterm>statement-level</> triggers,
  73     respectively. Triggers on <command>TRUNCATE</command> may only be
  74     defined at statement-level.
  75    </para>
  76
  77    <para>
  78     Triggers are also classified as <firstterm>before</> triggers and
  79     <firstterm>after</> triggers.
  80     Statement-level before triggers naturally fire before the
  81     statement starts to do anything, while statement-level after
  82     triggers fire at the very end of the statement.  Row-level before
  83     triggers fire immediately before a particular row is operated on,
  84     while row-level after triggers fire at the end of the statement
  85     (but before any statement-level after triggers).
  86    </para>
  87
  88    <para>
  89     Trigger functions invoked by per-statement triggers should always
  90     return <symbol>NULL</symbol>. Trigger functions invoked by per-row
  91     triggers can return a table row (a value of
  92     type <structname>HeapTuple</structname>) to the calling executor,
  93     if they choose.  A row-level trigger fired before an operation has
  94     the following choices:
  95
  96     <itemizedlist>
  97      <listitem>
  98       <para>
  99        It can return <symbol>NULL</> to skip the operation for the
 100        current row. This instructs the executor to not perform the
 101        row-level operation that invoked the trigger (the insertion or
 102        modification of a particular table row).
 103       </para>
 104      </listitem>
 105
 106      <listitem>
 107       <para>
 108        For row-level <command>INSERT</command>
 109        and <command>UPDATE</command> triggers only, the returned row
 110        becomes the row that will be inserted or will replace the row
 111        being updated.  This allows the trigger function to modify the
 112        row being inserted or updated.
 113       </para>
 114      </listitem>
 115     </itemizedlist>
 116
 117     A row-level before trigger that does not intend to cause either of
 118     these behaviors must be careful to return as its result the same
 119     row that was passed in (that is, the <varname>NEW</varname> row
 120     for <command>INSERT</command> and <command>UPDATE</command>
 121     triggers, the <varname>OLD</varname> row for
 122     <command>DELETE</command> triggers).
 123    </para>
 124
 125    <para>
 126     The return value is ignored for row-level triggers fired after an
 127     operation, and so they can return <symbol>NULL</>.
 128    </para>
 129
 130    <para>
 131     If more than one trigger is defined for the same event on the same
 132     relation, the triggers will be fired in alphabetical order by
 133     trigger name.  In the case of before triggers, the
 134     possibly-modified row returned by each trigger becomes the input
 135     to the next trigger.  If any before trigger returns
 136     <symbol>NULL</>, the operation is abandoned for that row and subsequent
 137     triggers are not fired.
 138    </para>
 139
 140    <para>
 141     Typically, row before triggers are used for checking or
 142     modifying the data that will be inserted or updated.  For example,
 143     a before trigger might be used to insert the current time into a
 144     <type>timestamp</type> column, or to check that two elements of the row are
 145     consistent. Row after triggers are most sensibly
 146     used to propagate the updates to other tables, or make consistency
 147     checks against other tables.  The reason for this division of labor is
 148     that an after trigger can be certain it is seeing the final value of the
 149     row, while a before trigger cannot; there might be other before triggers
 150     firing after it.  If you have no specific reason to make a trigger before
 151     or after, the before case is more efficient, since the information about
 152     the operation doesn't have to be saved until end of statement.
 153    </para>
 154
 155    <para>
 156     If a trigger function executes SQL commands then these
 157     commands might fire triggers again. This is known as cascading
 158     triggers.  There is no direct limitation on the number of cascade
 159     levels.  It is possible for cascades to cause a recursive invocation
 160     of the same trigger; for example, an <command>INSERT</command>
 161     trigger might execute a command that inserts an additional row
 162     into the same table, causing the <command>INSERT</command> trigger
 163     to be fired again.  It is the trigger programmer's responsibility
 164     to avoid infinite recursion in such scenarios.
 165    </para>
 166
 167    <para>
 168     When a trigger is being defined, arguments can be specified for
 169     it.<indexterm><primary>trigger</><secondary>arguments for trigger
 170     functions</></indexterm> The purpose of including arguments in the
 171     trigger definition is to allow different triggers with similar
 172     requirements to call the same function.  As an example, there
 173     could be a generalized trigger function that takes as its
 174     arguments two column names and puts the current user in one and
 175     the current time stamp in the other.  Properly written, this
 176     trigger function would be independent of the specific table it is
 177     triggering on.  So the same function could be used for
 178     <command>INSERT</command> events on any table with suitable
 179     columns, to automatically track creation of records in a
 180     transaction table for example. It could also be used to track
 181     last-update events if defined as an <command>UPDATE</command>
 182     trigger.
 183    </para>
 184
 185    <para>
 186     Each programming language that supports triggers has its own method
 187     for making the trigger input data available to the trigger function.
 188     This input data includes the type of trigger event (e.g.,
 189     <command>INSERT</command> or <command>UPDATE</command>) as well as any
 190     arguments that were listed in <command>CREATE TRIGGER</>.
 191     For a row-level trigger, the input data also includes the
 192     <varname>NEW</varname> row for <command>INSERT</command> and
 193     <command>UPDATE</command> triggers, and/or the <varname>OLD</varname> row
 194     for <command>UPDATE</command> and <command>DELETE</command> triggers.
 195     Statement-level triggers do not currently have any way to examine the
 196     individual row(s) modified by the statement.
 197    </para>
 198
 199   </sect1>
 200
 201   <sect1 id="trigger-datachanges">
 202    <title>Visibility of Data Changes</title>
 203
 204    <para>
 205     If you execute SQL commands in your trigger function, and these
 206     commands access the table that the trigger is for, then
 207     you need to be aware of the data visibility rules, because they determine
 208     whether these SQL commands will see the data change that the trigger
 209     is fired for.  Briefly:
 210
 211     <itemizedlist>
 212
 213      <listitem>
 214       <para>
 215        Statement-level triggers follow simple visibility rules: none of
 216        the changes made by a statement are visible to statement-level
 217        triggers that are invoked before the statement, whereas all
 218        modifications are visible to statement-level after triggers.
 219       </para>
 220      </listitem>
 221
 222      <listitem>
 223       <para>
 224        The data change (insertion, update, or deletion) causing the
 225        trigger to fire is naturally <emphasis>not</emphasis> visible
 226        to SQL commands executed in a row-level before trigger, because
 227        it hasn't happened yet.
 228       </para>
 229      </listitem>
 230
 231      <listitem>
 232       <para>
 233        However, SQL commands executed in a row-level before
 234        trigger <emphasis>will</emphasis> see the effects of data
 235        changes for rows previously processed in the same outer
 236        command.  This requires caution, since the ordering of these
 237        change events is not in general predictable; a SQL command that
 238        affects multiple rows can visit the rows in any order.
 239       </para>
 240      </listitem>
 241
 242      <listitem>
 243       <para>
 244        When a row-level after trigger is fired, all data changes made
 245        by the outer command are already complete, and are visible to
 246        the invoked trigger function.
 247       </para>
 248      </listitem>
 249     </itemizedlist>
 250    </para>
 251
 252    <para>
 253     If your trigger function is written in any of the standard procedural
 254     languages, then the above statements apply only if the function is
 255     declared <literal>VOLATILE</>.  Functions that are declared
 256     <literal>STABLE</> or <literal>IMMUTABLE</> will not see changes made by
 257     the calling command in any case.
 258    </para>
 259
 260    <para>
 261     Further information about data visibility rules can be found in
 262     <xref linkend="spi-visibility">.  The example in <xref
 263     linkend="trigger-example"> contains a demonstration of these rules.
 264    </para>
 265   </sect1>
 266
 267   <sect1 id="trigger-interface">
 268    <title>Writing Trigger Functions in C</title>
 269
 270    <indexterm zone="trigger-interface">
 271     <primary>trigger</primary>
 272     <secondary>in C</secondary>
 273    </indexterm>
 274
 275    <para>
 276     This section describes the low-level details of the interface to a
 277     trigger function.  This information is only needed when writing
 278     trigger functions in C.  If you are using a higher-level language then
 279     these details are handled for you.  In most cases you should consider
 280     using a procedural language before writing your triggers in C.  The
 281     documentation of each procedural language explains how to write a
 282     trigger in that language.
 283    </para>
 284
 285    <para>
 286     Trigger functions must use the <quote>version 1</> function manager
 287     interface.
 288    </para>
 289
 290    <para>
 291     When a function is called by the trigger manager, it is not passed
 292     any normal arguments, but it is passed a <quote>context</>
 293     pointer pointing to a <structname>TriggerData</> structure.  C
 294     functions can check whether they were called from the trigger
 295     manager or not by executing the macro:
 296 <programlisting>
 297 CALLED_AS_TRIGGER(fcinfo)
 298 </programlisting>
 299     which expands to:
 300 <programlisting>
 301 ((fcinfo)-&gt;context != NULL &amp;&amp; IsA((fcinfo)-&gt;context, TriggerData))
 302 </programlisting>
 303     If this returns true, then it is safe to cast
 304     <literal>fcinfo-&gt;context</> to type <literal>TriggerData
 305     *</literal> and make use of the pointed-to
 306     <structname>TriggerData</> structure.  The function must
 307     <emphasis>not</emphasis> alter the <structname>TriggerData</>
 308     structure or any of the data it points to.
 309    </para>
 310
 311    <para>
 312     <structname>struct TriggerData</structname> is defined in
 313     <filename>commands/trigger.h</filename>:
 314
 315 <programlisting>
 316 typedef struct TriggerData
 317 {
 318     NodeTag       type;
 319     TriggerEvent  tg_event;
 320     Relation      tg_relation;
 321     HeapTuple     tg_trigtuple;
 322     HeapTuple     tg_newtuple;
 323     Trigger      *tg_trigger;
 324     Buffer        tg_trigtuplebuf;
 325     Buffer        tg_newtuplebuf;
 326 } TriggerData;
 327 </programlisting>
 328
 329     where the members are defined as follows:
 330
 331     <variablelist>
 332      <varlistentry>
 333       <term><structfield>type</></term>
 334       <listitem>
 335        <para>
 336         Always <literal>T_TriggerData</literal>.
 337        </para>
 338       </listitem>
 339      </varlistentry>
 340
 341      <varlistentry>
 342       <term><structfield>tg_event</></term>
 343       <listitem>
 344        <para>
 345         Describes the event for which the function is called. You can use the
 346         following macros to examine <literal>tg_event</literal>:
 347
 348         <variablelist>
 349          <varlistentry>
 350           <term><literal>TRIGGER_FIRED_BEFORE(tg_event)</literal></term>
 351           <listitem>
 352            <para>
 353             Returns true if the trigger fired before the operation.
 354            </para>
 355           </listitem>
 356          </varlistentry>
 357
 358          <varlistentry>
 359           <term><literal>TRIGGER_FIRED_AFTER(tg_event)</literal></term>
 360           <listitem>
 361            <para>
 362             Returns true if the trigger fired after the operation.
 363            </para>
 364           </listitem>
 365          </varlistentry>
 366
 367          <varlistentry>
 368           <term><literal>TRIGGER_FIRED_FOR_ROW(tg_event)</literal></term>
 369           <listitem>
 370            <para>
 371             Returns true if the trigger fired for a row-level event.
 372            </para>
 373           </listitem>
 374          </varlistentry>
 375
 376          <varlistentry>
 377           <term><literal>TRIGGER_FIRED_FOR_STATEMENT(tg_event)</literal></term>
 378           <listitem>
 379            <para>
 380             Returns true if the trigger fired for a statement-level event.
 381            </para>
 382           </listitem>
 383          </varlistentry>
 384
 385          <varlistentry>
 386           <term><literal>TRIGGER_FIRED_BY_INSERT(tg_event)</literal></term>
 387           <listitem>
 388            <para>
 389             Returns true if the trigger was fired by an <command>INSERT</command> command.
 390            </para>
 391           </listitem>
 392          </varlistentry>
 393
 394          <varlistentry>
 395           <term><literal>TRIGGER_FIRED_BY_UPDATE(tg_event)</literal></term>
 396           <listitem>
 397            <para>
 398             Returns true if the trigger was fired by an <command>UPDATE</command> command.
 399            </para>
 400           </listitem>
 401          </varlistentry>
 402
 403          <varlistentry>
 404           <term><literal>TRIGGER_FIRED_BY_DELETE(tg_event)</literal></term>
 405           <listitem>
 406            <para>
 407             Returns true if the trigger was fired by a <command>DELETE</command> command.
 408            </para>
 409           </listitem>
 410          </varlistentry>
 411
 412          <varlistentry>
 413           <term><literal>TRIGGER_FIRED_BY_TRUNCATE(tg_event)</literal></term>
 414           <listitem>
 415            <para>
 416             Returns true if the trigger was fired by a <command>TRUNCATE</command> command.
 417            </para>
 418           </listitem>
 419          </varlistentry>
 420         </variablelist>
 421        </para>
 422       </listitem>
 423      </varlistentry>
 424
 425      <varlistentry>
 426       <term><structfield>tg_relation</></term>
 427       <listitem>
 428        <para>
 429         A pointer to a structure describing the relation that the trigger fired for.
 430         Look at <filename>utils/rel.h</> for details about
 431         this structure.  The most interesting things are
 432         <literal>tg_relation-&gt;rd_att</> (descriptor of the relation
 433         tuples) and <literal>tg_relation-&gt;rd_rel-&gt;relname</>
 434         (relation name; the type is not <type>char*</> but
 435         <type>NameData</>; use
 436         <literal>SPI_getrelname(tg_relation)</> to get a <type>char*</> if you
 437         need a copy of the name).
 438        </para>
 439       </listitem>
 440      </varlistentry>
 441
 442      <varlistentry>
 443       <term><structfield>tg_trigtuple</></term>
 444       <listitem>
 445        <para>
 446         A pointer to the row for which the trigger was fired. This is
 447         the row being inserted, updated, or deleted.  If this trigger
 448         was fired for an <command>INSERT</command> or
 449         <command>DELETE</command> then this is what you should return
 450         from the function if you don't want to replace the row with
 451         a different one (in the case of <command>INSERT</command>) or
 452         skip the operation.
 453        </para>
 454       </listitem>
 455      </varlistentry>
 456
 457      <varlistentry>
 458       <term><structfield>tg_newtuple</></term>
 459       <listitem>
 460        <para>
 461         A pointer to the new version of the row, if the trigger was
 462         fired for an <command>UPDATE</command>, and <symbol>NULL</> if
 463         it is for an <command>INSERT</command> or a
 464         <command>DELETE</command>. This is what you have to return
 465         from the function if the event is an <command>UPDATE</command>
 466         and you don't want to replace this row by a different one or
 467         skip the operation.
 468        </para>
 469       </listitem>
 470      </varlistentry>
 471
 472      <varlistentry>
 473       <term><structfield>tg_trigger</></term>
 474       <listitem>
 475        <para>
 476         A pointer to a structure of type <structname>Trigger</>,
 477         defined in <filename>utils/rel.h</>:
 478
 479 <programlisting>
 480 typedef struct Trigger
 481 {
 482     Oid         tgoid;
 483     char       *tgname;
 484     Oid         tgfoid;
 485     int16       tgtype;
 486     bool        tgenabled;
 487     bool        tgisconstraint;
 488     Oid         tgconstrrelid;
 489     Oid         tgconstraint;
 490     bool        tgdeferrable;
 491     bool        tginitdeferred;
 492     int16       tgnargs;
 493     int16       tgnattr;
 494     int16      *tgattr;
 495     char      **tgargs;
 496 } Trigger;
 497 </programlisting>
 498
 499        where <structfield>tgname</> is the trigger's name,
 500        <structfield>tgnargs</> is number of arguments in
 501        <structfield>tgargs</>, and <structfield>tgargs</> is an array of
 502        pointers to the arguments specified in the <command>CREATE
 503        TRIGGER</command> statement. The other members are for internal use
 504        only.
 505        </para>
 506       </listitem>
 507      </varlistentry>
 508
 509      <varlistentry>
 510       <term><structfield>tg_trigtuplebuf</></term>
 511       <listitem>
 512        <para>
 513         The buffer containing <structfield>tg_trigtuple</structfield>, or <symbol>InvalidBuffer</symbol> if there
 514         is no such tuple or it is not stored in a disk buffer.
 515        </para>
 516       </listitem>
 517      </varlistentry>
 518
 519      <varlistentry>
 520       <term><structfield>tg_newtuplebuf</></term>
 521       <listitem>
 522        <para>
 523         The buffer containing <structfield>tg_newtuple</structfield>, or <symbol>InvalidBuffer</symbol> if there
 524         is no such tuple or it is not stored in a disk buffer.
 525        </para>
 526       </listitem>
 527      </varlistentry>
 528
 529     </variablelist>
 530    </para>
 531
 532    <para>
 533     A trigger function must return either a
 534     <structname>HeapTuple</> pointer or a <symbol>NULL</> pointer
 535     (<emphasis>not</> an SQL null value, that is, do not set <parameter>isNull</parameter> true).
 536     Be careful to return either
 537     <structfield>tg_trigtuple</> or <structfield>tg_newtuple</>,
 538     as appropriate, if you don't want to modify the row being operated on.
 539    </para>
 540   </sect1>
 541
 542   <sect1 id="trigger-example">
 543    <title>A Complete Example</title>
 544
 545    <para>
 546     Here is a very simple example of a trigger function written in C.
 547     (Examples of triggers written in procedural languages can be found
 548     in the documentation of the procedural languages.)
 549    </para>
 550
 551    <para>
 552     The function <function>trigf</> reports the number of rows in the
 553     table <structname>ttest</> and skips the actual operation if the
 554     command attempts to insert a null value into the column
 555     <structfield>x</>. (So the trigger acts as a not-null constraint but
 556     doesn't abort the transaction.)
 557    </para>
 558
 559    <para>
 560     First, the table definition:
 561 <programlisting>
 562 CREATE TABLE ttest (
 563     x integer
 564 );
 565 </programlisting>
 566    </para>
 567
 568    <para>
 569     This is the source code of the trigger function:
 570 <programlisting><![CDATA[
 571 #include "postgres.h"
 572 #include "executor/spi.h"       /* this is what you need to work with SPI */
 573 #include "commands/trigger.h"   /* ... and triggers */
 574
 575 extern Datum trigf(PG_FUNCTION_ARGS);
 576
 577 PG_FUNCTION_INFO_V1(trigf);
 578
 579 Datum
 580 trigf(PG_FUNCTION_ARGS)
 581 {
 582     TriggerData *trigdata = (TriggerData *) fcinfo->context;
 583     TupleDesc   tupdesc;
 584     HeapTuple   rettuple;
 585     char       *when;
 586     bool        checknull = false;
 587     bool        isnull;
 588     int         ret, i;
 589
 590     /* make sure it's called as a trigger at all */
 591     if (!CALLED_AS_TRIGGER(fcinfo))
 592         elog(ERROR, "trigf: not called by trigger manager");
 593
 594     /* tuple to return to executor */
 595     if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
 596         rettuple = trigdata->tg_newtuple;
 597     else
 598         rettuple = trigdata->tg_trigtuple;
 599
 600     /* check for null values */
 601     if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)
 602         && TRIGGER_FIRED_BEFORE(trigdata->tg_event))
 603         checknull = true;
 604
 605     if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))
 606         when = "before";
 607     else
 608         when = "after ";
 609
 610     tupdesc = trigdata->tg_relation->rd_att;
 611
 612     /* connect to SPI manager */
 613     if ((ret = SPI_connect()) < 0)
 614         elog(ERROR, "trigf (fired %s): SPI_connect returned %d", when, ret);
 615
 616     /* get number of rows in table */
 617     ret = SPI_exec("SELECT count(*) FROM ttest", 0);
 618
 619     if (ret < 0)
 620         elog(ERROR, "trigf (fired %s): SPI_exec returned %d", when, ret);
 621
 622     /* count(*) returns int8, so be careful to convert */
 623     i = DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0],
 624                                     SPI_tuptable->tupdesc,
 625                                     1,
 626                                     &isnull));
 627
 628     elog (INFO, "trigf (fired %s): there are %d rows in ttest", when, i);
 629
 630     SPI_finish();
 631
 632     if (checknull)
 633     {
 634         SPI_getbinval(rettuple, tupdesc, 1, &isnull);
 635         if (isnull)
 636             rettuple = NULL;
 637     }
 638
 639     return PointerGetDatum(rettuple);
 640 }
 641 ]]>
 642 </programlisting>
 643    </para>
 644
 645    <para>
 646     After you have compiled the source code (see <xref
 647     linkend="dfunc">), declare the function and the triggers:
 648 <programlisting>
 649 CREATE FUNCTION trigf() RETURNS trigger
 650     AS '<replaceable>filename</>'
 651     LANGUAGE C;
 652
 653 CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest
 654     FOR EACH ROW EXECUTE PROCEDURE trigf();
 655
 656 CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest
 657     FOR EACH ROW EXECUTE PROCEDURE trigf();
 658 </programlisting>
 659    </para>
 660
 661    <para>
 662     Now you can test the operation of the trigger:
 663 <screen>
 664 =&gt; INSERT INTO ttest VALUES (NULL);
 665 INFO:  trigf (fired before): there are 0 rows in ttest
 666 INSERT 0 0
 667
 668 -- Insertion skipped and AFTER trigger is not fired
 669
 670 =&gt; SELECT * FROM ttest;
 671  x
 672 ---
 673 (0 rows)
 674
 675 =&gt; INSERT INTO ttest VALUES (1);
 676 INFO:  trigf (fired before): there are 0 rows in ttest
 677 INFO:  trigf (fired after ): there are 1 rows in ttest
 678                                        ^^^^^^^^
 679                              remember what we said about visibility.
 680 INSERT 167793 1
 681 vac=&gt; SELECT * FROM ttest;
 682  x
 683 ---
 684  1
 685 (1 row)
 686
 687 =&gt; INSERT INTO ttest SELECT x * 2 FROM ttest;
 688 INFO:  trigf (fired before): there are 1 rows in ttest
 689 INFO:  trigf (fired after ): there are 2 rows in ttest
 690                                        ^^^^^^
 691                              remember what we said about visibility.
 692 INSERT 167794 1
 693 =&gt; SELECT * FROM ttest;
 694  x
 695 ---
 696  1
 697  2
 698 (2 rows)
 699
 700 =&gt; UPDATE ttest SET x = NULL WHERE x = 2;
 701 INFO:  trigf (fired before): there are 2 rows in ttest
 702 UPDATE 0
 703 =&gt; UPDATE ttest SET x = 4 WHERE x = 2;
 704 INFO:  trigf (fired before): there are 2 rows in ttest
 705 INFO:  trigf (fired after ): there are 2 rows in ttest
 706 UPDATE 1
 707 vac=&gt; SELECT * FROM ttest;
 708  x
 709 ---
 710  1
 711  4
 712 (2 rows)
 713
 714 =&gt; DELETE FROM ttest;
 715 INFO:  trigf (fired before): there are 2 rows in ttest
 716 INFO:  trigf (fired before): there are 1 rows in ttest
 717 INFO:  trigf (fired after ): there are 0 rows in ttest
 718 INFO:  trigf (fired after ): there are 0 rows in ttest
 719                                        ^^^^^^
 720                              remember what we said about visibility.
 721 DELETE 2
 722 =&gt; SELECT * FROM ttest;
 723  x
 724 ---
 725 (0 rows)
 726 </screen>
 727
 728    </para>
 729
 730    <para>
 731     There are more complex examples in
 732     <filename>src/test/regress/regress.c</filename> and
 733     in <filename>contrib/spi</filename>.
 734    </para>
 735   </sect1>
 736  </chapter>