Home • Explore • Blog • CI
☰



postgresql
11th chunk of `doc/src/sgml/ref/create_trigger.sgml`
390cf0556dbf0beb7b402570766cb3f8c101f286c20c95040000000100000cd4
 <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> only allows the execution
      of a user-defined function for the triggered action.  The standard
      allows the execution of a number of other SQL commands, such as
      <command>CREATE TABLE</command>, as the triggered action.  This
      limitation is not hard to work around by creating a user-defined
      function that executes the desired commands.
     </para>
    </listitem>

   </itemizedlist>
  </para>

  <para>
   SQL specifies that multiple triggers should be fired in
   time-of-creation order.  <productname>PostgreSQL</productname> uses
   name order, which was judged to be more convenient.
  </para>

  <para>
   SQL specifies that <literal>BEFORE DELETE</literal> triggers on cascaded
   deletes fire <emphasis>after</emphasis> the cascaded <literal>DELETE</literal> completes.
   The <productname>PostgreSQL</productname> behavior is for <literal>BEFORE
   DELETE</literal> to always fire before the delete action, even a cascading
   one.  This is considered more consistent.  There is also nonstandard
   behavior if <literal>BEFORE</literal> triggers modify rows or prevent
   updates during an update that is caused by a referential action.  This can
   lead to constraint violations or stored data that does not honor the
   referential constraint.
  </para>

  <para>
   The ability to specify multiple actions for a single trigger using
   <literal>OR</literal> is a <productname>PostgreSQL</productname> extension of
   the SQL standard.
  </para>

  <para>
   The ability to fire triggers for <command>TRUNCATE</command> is a
   <productname>PostgreSQL</productname> extension of the SQL standard, as is the
   ability to define statement-level triggers on views.
  </para>

  <para>
   <command>CREATE CONSTRAINT TRIGGER</command> is a
   <productname>PostgreSQL</productname> extension of the <acronym>SQL</acronym>
   standard.
   So is the <literal>OR REPLACE</literal> option.
  </para>

 </refsect1>

 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-altertrigger"/></member>
   <member><xref linkend="sql-droptrigger"/></member>
   <member><xref linkend="sql-createfunction"/></member>
   <member><xref linkend="sql-set-constraints"/></member>
  </simplelist>
 </refsect1>
</refentry>
Title: CREATE TRIGGER: PostgreSQL's Deviations from SQL Standard
Summary
This section details how PostgreSQL's CREATE TRIGGER implementation deviates from the SQL standard. Key differences include: limitations on triggered actions (only user-defined functions are allowed), trigger firing order (name order instead of creation order), timing of BEFORE DELETE triggers on cascaded deletes, and the non-standard behavior of BEFORE triggers modifying rows during referential actions. Furthermore, the use of 'OR' for multiple actions, triggers for TRUNCATE, statement-level triggers on views, and the CREATE CONSTRAINT TRIGGER command are all PostgreSQL extensions to the SQL standard. The section also provides a list of related SQL commands for further reference.