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