Home Explore Blog CI



postgresql

2nd chunk of `doc/src/sgml/ref/create_trigger.sgml`
c867f0783ae5ba9947329823e5eaeb5a390705de27eb5dcb0000000100000fa0
 the row being inserted (for
   <command>INSERT</command> and <command>UPDATE</command> operations
   only). If the trigger fires after the event, all changes, including
   the effects of other triggers, are <quote>visible</quote>
   to the trigger.
  </para>

  <para>
   A trigger that is marked <literal>FOR EACH ROW</literal> is called
   once for every row that the operation modifies. For example, a
   <command>DELETE</command> that affects 10 rows will cause any
   <literal>ON DELETE</literal> triggers on the target relation to be
   called 10 separate times, once for each deleted row. In contrast, a
   trigger that is marked <literal>FOR EACH STATEMENT</literal> only
   executes once for any given operation, regardless of how many rows
   it modifies (in particular, an operation that modifies zero rows
   will still result in the execution of any applicable <literal>FOR
   EACH STATEMENT</literal> triggers).
  </para>

  <para>
   Triggers that are specified to fire <literal>INSTEAD OF</literal> the trigger
   event must be marked <literal>FOR EACH ROW</literal>, and can only be defined
   on views. <literal>BEFORE</literal> and <literal>AFTER</literal> triggers on a view
   must be marked as <literal>FOR EACH STATEMENT</literal>.
  </para>

  <para>
   In addition, triggers may be defined to fire for
   <command>TRUNCATE</command>, though only
   <literal>FOR EACH STATEMENT</literal>.
  </para>

  <para>
   The following table summarizes which types of triggers may be used on
   tables, views, and foreign tables:
  </para>

  <informaltable id="supported-trigger-types">
   <tgroup cols="4">
    <thead>
     <row>
      <entry>When</entry>
      <entry>Event</entry>
      <entry>Row-level</entry>
      <entry>Statement-level</entry>
     </row>
    </thead>
    <tbody>
     <row>
      <entry align="center" morerows="1"><literal>BEFORE</literal></entry>
      <entry align="center"><command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command></entry>
      <entry align="center">Tables and foreign tables</entry>
      <entry align="center">Tables, views, and foreign tables</entry>
     </row>
     <row>
      <entry align="center"><command>TRUNCATE</command></entry>
      <entry align="center">&mdash;</entry>
      <entry align="center">Tables and foreign tables</entry>
     </row>
     <row>
      <entry align="center" morerows="1"><literal>AFTER</literal></entry>
      <entry align="center"><command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command></entry>
      <entry align="center">Tables and foreign tables</entry>
      <entry align="center">Tables, views, and foreign tables</entry>
     </row>
     <row>
      <entry align="center"><command>TRUNCATE</command></entry>
      <entry align="center">&mdash;</entry>
      <entry align="center">Tables and foreign tables</entry>
     </row>
     <row>
      <entry align="center" morerows="1"><literal>INSTEAD OF</literal></entry>
      <entry align="center"><command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command></entry>
      <entry align="center">Views</entry>
      <entry align="center">&mdash;</entry>
     </row>
     <row>
      <entry align="center"><command>TRUNCATE</command></entry>
      <entry align="center">&mdash;</entry>
      <entry align="center">&mdash;</entry>
     </row>
    </tbody>
   </tgroup>
  </informaltable>

  <para>
   Also, a trigger definition can 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 since the condition cannot refer to any values
   in the table.
  </para>

  <para>
   If multiple triggers of the same kind are defined for the same event,
   they will be fired

Title: Trigger Types, Restrictions, and Conditions
Summary
INSTEAD OF triggers must be FOR EACH ROW and can only be on views, while BEFORE and AFTER triggers on views must be FOR EACH STATEMENT. Triggers can also fire for TRUNCATE, only FOR EACH STATEMENT. The documentation provides a table summarizing which trigger types can be used on tables, views, and foreign tables. A WHEN condition can be specified to determine if a trigger should fire; row-level triggers can examine old and new column values in the WHEN condition.