Home Explore Blog CI



postgresql

10th chunk of `doc/src/sgml/ref/merge.sgml`
64ccdf173cad25201d736156df9bb9fd74b9590b7461d1b40000000100000c18
 table.
            </para>
           </listitem>
           <listitem>
            <para>
             Perform any <literal>AFTER ROW</literal> triggers that fire for
             the action's event type.
            </para>
           </listitem>
          </orderedlist>
          If the target relation is a view with <literal>INSTEAD OF ROW</literal>
          triggers for the action's event type, they are used to perform the
          action instead.
         </para>
        </listitem>
       </orderedlist></para>
     </listitem>
     <listitem>
      <para>
       Perform any <literal>AFTER STATEMENT</literal> triggers for actions
       specified, whether or not they actually occur.  This is similar to the
       behavior of an <command>UPDATE</command> statement that modifies no rows.
      </para>
     </listitem>
    </orderedlist>
   In summary, statement triggers for an event type (say,
   <command>INSERT</command>) will be fired whenever we
   <emphasis>specify</emphasis> an action of that kind.
   In contrast, row-level triggers will fire only for the specific event type
   being <emphasis>executed</emphasis>.
   So a <command>MERGE</command> command might fire statement triggers for both
   <command>UPDATE</command> and <command>INSERT</command>, even though only
   <command>UPDATE</command> row triggers were fired.
  </para>

  <para>
   You should ensure that the join produces at most one candidate change row
   for each target row.  In other words, a target row shouldn't join to more
   than one data source row.  If it does, then only one of the candidate change
   rows will be used to modify the target row; later attempts to modify the
   row will cause an error.
   This can also occur if row triggers make changes to the target table
   and the rows so modified are then subsequently also modified by
   <command>MERGE</command>.
   If the repeated action is an <command>INSERT</command>, this will
   cause a uniqueness violation, while a repeated <command>UPDATE</command>
   or <command>DELETE</command> will cause a cardinality violation; the
   latter behavior is required by the <acronym>SQL</acronym> standard.
   This differs from historical <productname>PostgreSQL</productname>
   behavior of joins in <command>UPDATE</command> and
   <command>DELETE</command> statements where second and subsequent
   attempts to modify the same row are simply ignored.
  </para>

  <para>
   If a <literal>WHEN</literal> clause omits an <literal>AND</literal>
   sub-clause, it becomes the final reachable clause of that
   kind (<literal>MATCHED</literal>, <literal>NOT MATCHED BY SOURCE</literal>,
   or <literal>NOT MATCHED [BY TARGET]</literal>).
   If a later <literal>WHEN</literal> clause of that kind
   is specified it would be provably unreachable and an error is raised.
   If no final reachable clause is specified of either kind, it is
   possible that no action will be taken for a candidate change row.
  </para>

  <para>
   The order in which rows are generated from the data source is
   indeterminate by default.
   A <replaceable

Title: MERGE Statement Trigger Behavior and Row Handling
Summary
This section clarifies the behavior of triggers in MERGE statements, distinguishing between statement-level and row-level triggers. It emphasizes that statement triggers fire based on specified actions, while row-level triggers fire only for executed actions. The section also addresses the importance of ensuring that the join operation yields at most one candidate change row per target row to avoid uniqueness or cardinality violations. It explains how omitting an AND sub-clause in a WHEN clause can make it the final reachable clause of that kind.