Home • Explore • Blog • CI
☰



postgresql
10th chunk of `doc/src/sgml/ref/create_trigger.sgml`
5199ab22c4be99366bfa02cfd9415e1adf5badf615a69cdb000000010000090e
 confirm that changes are made to matching pairs at the same time (by the
   same statement):

<programlisting>
CREATE TRIGGER paired_items_update
    AFTER UPDATE ON paired_items
    REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
    FOR EACH ROW
    EXECUTE FUNCTION check_matching_pairs();
</programlisting>
  </para>

  <para>
   <xref linkend="trigger-example"/> contains a complete example of a trigger
   function written in C.
  </para>
 </refsect1>

 <refsect1 id="sql-createtrigger-compatibility">
  <title>Compatibility</title>

  <!--
   It's not clear whether SQL/MED contemplates triggers on foreign tables.
   Its <drop basic column definition> General Rules do mention the possibility
   of a reference from a trigger column list.  On the other hand, nothing
   overrides the fact that CREATE TRIGGER only targets base tables.  For now,
   do not document the compatibility status of triggers on foreign tables.
  -->

  <para>
   The <command>CREATE TRIGGER</command> statement in
   <productname>PostgreSQL</productname> implements a subset of the
   <acronym>SQL</acronym> standard. The following functionalities are currently
   missing:

   <itemizedlist>
    <listitem>
     <para>
      While transition table names for <literal>AFTER</literal> triggers are
      specified using the <literal>REFERENCING</literal> clause in the standard way,
      the row variables used in <literal>FOR EACH ROW</literal> triggers may not be
      specified in a <literal>REFERENCING</literal> clause.  They are available in a
      manner that is dependent on the language in which the trigger function
      is written, but is fixed for any one language.  Some languages
      effectively behave as though there is a <literal>REFERENCING</literal> clause
      containing <literal>OLD ROW AS OLD NEW ROW AS NEW</literal>.
     </para>
    </listitem>

    <listitem>
     <para>
      The standard allows transition tables to be used with
      column-specific <literal>UPDATE</literal> triggers, but then the set of rows
      that should be visible in the transition tables depends on the
      trigger's column list.  This is not currently implemented by
      <productname>PostgreSQL</productname>.
     </para>
    </listitem>

    <listitem>
     <para>
      <productname>PostgreSQL</productname>
Title: CREATE TRIGGER Compatibility with SQL Standard
Summary
This section discusses the compatibility of PostgreSQL's CREATE TRIGGER statement with the SQL standard. While PostgreSQL implements a subset of the SQL standard, some functionalities are missing. Specifically, the row variables in FOR EACH ROW triggers may not be specified in a REFERENCING clause, and the use of transition tables with column-specific UPDATE triggers is not fully implemented. Additionally, PostgreSQL doesn't allow specifying both WHEN and FOR EACH ROW.