<!--
doc/src/sgml/ref/create_trigger.sgml
PostgreSQL documentation
-->

<refentry id="sql-createtrigger">
 <indexterm zone="sql-createtrigger">
  <primary>CREATE TRIGGER</primary>
 </indexterm>

 <indexterm>
  <primary>transition tables</primary>
  <seealso>ephemeral named relation</seealso>
 </indexterm>

 <refmeta>
  <refentrytitle>CREATE TRIGGER</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>CREATE TRIGGER</refname>
  <refpurpose>define a new trigger</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name</replaceable> { BEFORE | AFTER | INSTEAD OF } { <replaceable class="parameter">event</replaceable> [ OR ... ] }
    ON <replaceable class="parameter">table_name</replaceable>
    [ FROM <replaceable class="parameter">referenced_table_name</replaceable> ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] <replaceable class="parameter">transition_relation_name</replaceable> } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( <replaceable class="parameter">condition</replaceable> ) ]
    EXECUTE { FUNCTION | PROCEDURE } <replaceable class="parameter">function_name</replaceable> ( <replaceable class="parameter">arguments</replaceable> )

<phrase>where <replaceable class="parameter">event</replaceable> can be one of:</phrase>

    INSERT
    UPDATE [ OF <replaceable class="parameter">column_name</replaceable> [, ... ] ]
    DELETE
    TRUNCATE
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <command>CREATE TRIGGER</command> creates a new trigger.
   <command>CREATE OR REPLACE TRIGGER</command> will either create a
   new trigger, or replace an existing trigger.  The
   trigger will be associated with the specified table, view, or foreign table
   and will execute the specified
   function <replaceable class="parameter">function_name</replaceable> when
   certain operations are performed on that table.
  </para>

  <para>
   To replace the current definition of an existing trigger, use
   <command>CREATE OR REPLACE TRIGGER</command>, specifying the existing
   trigger's name and parent table.  All other properties are replaced.
  </para>

  <para>
   The trigger can be specified to fire before the
   operation is attempted on a row (before constraints are checked and
   the <command>INSERT</command>, <command>UPDATE</command>, or
   <command>DELETE</command> is attempted); or after the operation has
   completed (after constraints are checked and the
   <command>INSERT</command>, <command>UPDATE</command>, or
   <command>DELETE</command> has completed); or instead of the operation
   (in the case of inserts, updates or deletes on a view).
   If the trigger fires before or instead of the event, the trigger can skip
   the operation for the current row, or change the row being inserted (for
   <command>INSERT</command> and <command>UPDATE</command> operations
   only). If the trigger fires after the event, all changes, including
   the effects of other triggers, are <quote>visible</quote>
   to the trigger.
  </para>

  <para>
   A trigger that is marked <literal>FOR EACH ROW</literal> is called
   once for every row that the operation modifies. For example, a
   <command>DELETE</command> that affects 10 rows will cause any
   <literal>ON DELETE</literal> triggers on the target relation to be
   called 10 separate times, once for each deleted row. In contrast, a
   trigger that is marked <literal>FOR EACH STATEMENT</literal> only
   executes once for any given operation, regardless of how many rows
   it modifies (in particular, an operation that modifies zero rows
   will still result in the execution of any applicable <literal>FOR
   EACH STATEMENT</literal> triggers).
  </para>

  <para>
   Triggers that are specified to fire <literal>INSTEAD