Home Explore Blog CI



postgresql

9th chunk of `doc/src/sgml/ref/create_trigger.sgml`
49860ffca9ec5f83edc64dd90c6a365ae1ee72ced249f5300000000100000e0a
 transaction that has already
   performed updating actions on the trigger's table is not recommended.
   Trigger firing decisions, or portions of firing decisions, that have
   already been made will not be reconsidered, so the effects could be
   surprising.
  </para>

  <para>
   There are a few built-in trigger functions that can be used to
   solve common problems without having to write your own trigger code;
   see <xref linkend="functions-trigger"/>.
  </para>
 </refsect1>

 <refsect1 id="sql-createtrigger-examples">
  <title>Examples</title>

  <para>
   Execute the function <function>check_account_update</function> whenever
   a row of the table <literal>accounts</literal> is about to be updated:

<programlisting>
CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION check_account_update();
</programlisting>

   Modify that trigger definition to only execute the function if
   column <literal>balance</literal> is specified as a target in
   the <command>UPDATE</command> command:

<programlisting>
CREATE OR REPLACE TRIGGER check_update
    BEFORE UPDATE OF balance ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION check_account_update();
</programlisting>

   This form only executes the function if column <literal>balance</literal>
   has in fact changed value:

<programlisting>
CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
    EXECUTE FUNCTION check_account_update();
</programlisting>

   Call a function to log updates of <literal>accounts</literal>, but only if
   something changed:

<programlisting>
CREATE TRIGGER log_update
    AFTER UPDATE ON accounts
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE FUNCTION log_account_update();
</programlisting>

   Execute the function <function>view_insert_row</function> for each row to insert
   rows into the tables underlying a view:

<programlisting>
CREATE TRIGGER view_insert
    INSTEAD OF INSERT ON my_view
    FOR EACH ROW
    EXECUTE FUNCTION view_insert_row();
</programlisting>

   Execute the function <function>check_transfer_balances_to_zero</function> for each
   statement to confirm that the <literal>transfer</literal> rows offset to a net of
   zero:

<programlisting>
CREATE TRIGGER transfer_insert
    AFTER INSERT ON transfer
    REFERENCING NEW TABLE AS inserted
    FOR EACH STATEMENT
    EXECUTE FUNCTION check_transfer_balances_to_zero();
</programlisting>

   Execute the function <function>check_matching_pairs</function> for each row to
   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

Title: CREATE TRIGGER Examples and Compatibility
Summary
The text provides several examples of using the CREATE TRIGGER command in PostgreSQL. These examples demonstrate how to execute a function before or after updates on a table, how to use CREATE OR REPLACE to modify an existing trigger, how to conditionally execute a function based on column changes, how to create an INSTEAD OF trigger for views, and how to use REFERENCING NEW TABLE and OLD TABLE for statement-level triggers. It concludes with a note on compatibility, stating that the CREATE TRIGGER statement in PostgreSQL conforms to the SQL standard.