<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.210 2009/10/14 22:14:21 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.211 2009/11/20 20:38:09 tgl Exp $ -->

<!--

Documentation of the system catalogs, directed toward PostgreSQL developers

-->

<entry></entry>

<entry>Argument strings to pass to trigger, each NULL-terminated</entry>

</row>

<row>

<entry><structfield>tgqual</structfield></entry>

<entry><type>text</type></entry>

<entry></entry>

<entry>Expression tree (in <function>nodeToString()</function>

representation) for the trigger's <literal>WHEN</> condition, or NULL

if none</entry>

</row>

</tbody>

</tgroup>

</table>

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/create_constraint.sgml,v 1.20 2009/09/19 10:23:26 petere Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/create_constraint.sgml,v 1.21 2009/11/20 20:38:09 tgl Exp $

PostgreSQL documentation

-->

[ FROM <replaceable class="parameter">referenced_table_name</replaceable> ]

{ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }

FOR EACH ROW

[ WHEN ( <replaceable class="parameter">condition</replaceable> ) ]

EXECUTE PROCEDURE <replaceable class="parameter">function_name</replaceable> ( <replaceable class="parameter">arguments</replaceable> )

</synopsis>

</refsynopsisdiv>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="parameter">condition</replaceable></term>

<listitem>

<para>

A Boolean expression that determines whether the trigger function

will actually be executed. This acts the same as in <xref

linkend="SQL-CREATETRIGGER" endterm="SQL-CREATETRIGGER-TITLE">.

Note in particular that evaluation of the <literal>WHEN</>

condition is not deferred, but occurs immediately after the row

update operation is performed. If the condition does not evaluate

to <literal>true</> then the trigger is not queued for deferred

execution.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="PARAMETER">function_name</replaceable></term>

<listitem>

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/create_trigger.sgml,v 1.51 2009/10/14 22:14:21 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/create_trigger.sgml,v 1.52 2009/11/20 20:38:09 tgl Exp $

PostgreSQL documentation

-->

<synopsis>

CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTER } { <replaceable class="PARAMETER">event</replaceable> [ OR ... ] }

ON <replaceable class="PARAMETER">table</replaceable> [ FOR [ EACH ] { ROW | STATEMENT } ]

[ WHEN ( <replaceable class="parameter">condition</replaceable> ) ]

EXECUTE PROCEDURE <replaceable class="PARAMETER">function_name</replaceable> ( <replaceable class="PARAMETER">arguments</replaceable> )

</synopsis>

</refsynopsisdiv>

<literal>FOR EACH STATEMENT</literal>.

</para>

<para>

Also, a trigger definition can specify a boolean <literal>WHEN</>

condition, which will be tested to see whether the trigger should

be fired. In row-level triggers the <literal>WHEN</> condition can

examine the old and/or new values of columns of the row. Statement-level

triggers can also have <literal>WHEN</> conditions, although the feature

is not so useful for them since the condition cannot refer to any values

in the table.

</para>

<para>

If multiple triggers of the same kind are defined for the same event,

they will be fired in alphabetical order by name.

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="parameter">condition</replaceable></term>

<listitem>

<para>

A Boolean expression that determines whether the trigger function

will actually be executed. If <literal>WHEN</> is specified, the

function will only be called if the <replaceable

class="parameter">condition</replaceable> returns <literal>true</>.

In <literal>FOR EACH ROW</literal> triggers, the <literal>WHEN</>

condition can refer to columns of the old and/or new row values

by writing <literal>OLD.<replaceable

class="parameter">column_name</replaceable></literal> or

<literal>NEW.<replaceable

class="parameter">column_name</replaceable></literal> respectively.

Of course, <literal>INSERT</> triggers cannot refer to <literal>OLD</>

and <literal>DELETE</> triggers cannot refer to <literal>NEW</>.

</para>

<para>

Currently, <literal>WHEN</literal> expressions cannot contain

subqueries.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="parameter">function_name</replaceable></term>

<listitem>

value did not change.

</para>

<para>

In a <literal>BEFORE</> trigger, the <literal>WHEN</> condition is

evaluated just before the function is or would be executed, so using

<literal>WHEN</> is not materially different from testing the same

condition at the beginning of the trigger function. Note in particular

that the <literal>NEW</> row seen by the condition is the current value,

as possibly modified by earlier triggers. Also, a <literal>BEFORE</>

trigger's <literal>WHEN</> condition is not allowed to examine the

system columns of the <literal>NEW</> row (such as <literal>oid</>),

because those won't have been set yet.

</para>

<para>

In an <literal>AFTER</> trigger, the <literal>WHEN</> condition is

evaluated just after the row update occurs, and it determines whether an

event is queued to fire the trigger at the end of statement. So when an

<literal>AFTER</> trigger's <literal>WHEN</> condition does not return

true, it is not necessary to queue an event nor to re-fetch the row at end

of statement. This can result in significant speedups in statements that

modify many rows, if the trigger only needs to be fired for a few of the

rows.

</para>

<para>

In <productname>PostgreSQL</productname> versions before 7.3, it was

necessary to declare trigger functions as returning the placeholder

</para>

</refsect1>

<refsect1 id="R1-SQL-CREATETRIGGER-2">

<refsect1 id="SQL-CREATETRIGGER-examples">

<title>Examples</title>

<para>

<xref linkend="trigger-example"> contains a complete example.

Execute the function <function>check_account_update</> whenever

a row of the table <literal>accounts</> is about to be updated:

<programlisting>

CREATE TRIGGER check_update

BEFORE UPDATE ON accounts

FOR EACH ROW

EXECUTE PROCEDURE check_account_update();

</programlisting>

The same, but only execute the function if column <literal>balance</>

is specified as a target in the <command>UPDATE</> command:

<programlisting>

CREATE TRIGGER check_update

BEFORE UPDATE OF balance ON accounts

FOR EACH ROW

EXECUTE PROCEDURE check_account_update();

</programlisting>

This form only executes the function if column <literal>balance</>

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 PROCEDURE check_account_update();

</programlisting>

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

something changed:

<programlisting>

CREATE TRIGGER log_update

AFTER UPDATE ON accounts

FOR EACH ROW

WHEN (OLD.* IS DISTINCT FROM NEW.*)

EXECUTE PROCEDURE log_account_update();

</programlisting>

</para>

<para>

<xref linkend="trigger-example"> contains a complete example of a trigger

function written in C.

</para>

</refsect1>

<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

<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>

<!-- $PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.59 2009/10/14 22:14:21 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.60 2009/11/20 20:38:09 tgl Exp $ -->

<chapter id="triggers">

<title>Triggers</title>

triggers are not fired.

</para>

<para>

A trigger definition can also specify a boolean <literal>WHEN</>

condition, which will be tested to see whether the trigger should

be fired. In row-level triggers the <literal>WHEN</> condition can

examine the old and/or new values of columns of the row. (Statement-level

triggers can also have <literal>WHEN</> conditions, although the feature

is not so useful for them.) In a before trigger, the <literal>WHEN</>

condition is evaluated just before the function is or would be executed,

so using <literal>WHEN</> is not materially different from testing the

same condition at the beginning of the trigger function. However, in

an after trigger, the <literal>WHEN</> condition is evaluated just after

the row update occurs, and it determines whether an event is queued to

fire the trigger at the end of statement. So when an after trigger's

<literal>WHEN</> condition does not return true, it is not necessary

to queue an event nor to re-fetch the row at end of statement. This

can result in significant speedups in statements that modify many

rows, if the trigger only needs to be fired for a few of the rows.

</para>

<para>

Typically, row before triggers are used for checking or

modifying the data that will be inserted or updated. For example,

int16 tgnattr;

int16 *tgattr;

char **tgargs;

char *tgqual;

} Trigger;

</programlisting>

trigger->row= true;

trigger->events=TRIGGER_TYPE_INSERT |TRIGGER_TYPE_UPDATE;

trigger->columns=NIL;

trigger->whenClause=NULL;

trigger->isconstraint= true;

trigger->deferrable= true;

trigger->initdeferred=initdeferred;

trigger->constrrel=NULL;

(void)CreateTrigger(trigger,conOid,indexRelationId,

(void)CreateTrigger(trigger,NULL,conOid,indexRelationId,

isprimary ?"PK_ConstraintTrigger" :

"Unique_ConstraintTrigger",

false);

resultRelInfo->ri_RelationDesc=cstate->rel;

resultRelInfo->ri_TrigDesc=CopyTriggerDesc(cstate->rel->trigdesc);

if (resultRelInfo->ri_TrigDesc)

{

resultRelInfo->ri_TrigFunctions= (FmgrInfo*)

palloc0(resultRelInfo->ri_TrigDesc->numtriggers*sizeof(FmgrInfo));

resultRelInfo->ri_TrigWhenExprs= (List**)

palloc0(resultRelInfo->ri_TrigDesc->numtriggers*sizeof(List*));

}

resultRelInfo->ri_TrigInstrument=NULL;

ExecOpenIndices(resultRelInfo);

estate->es_result_relation_info=resultRelInfo;

slot=MakeSingleTupleTableSlot(tupDesc);

slot=ExecInitExtraTupleSlot(estate);

ExecSetSlotDescriptor(slot,tupDesc);

econtext=GetPerTupleExprContext(estate);

pfree(defmap);

pfree(defexprs);

ExecDropSingleTupleTableSlot(slot);

ExecResetTupleTable(estate->es_tupleTable, false);

ExecCloseIndices(resultRelInfo);

trig.tgconstraint=constraintOid;

trig.tgdeferrable= FALSE;

trig.tginitdeferred= FALSE;

}

fk_trigger->columns=NIL;

fk_trigger->whenClause=NULL;

fk_trigger->isconstraint= true;

fk_trigger->deferrable=fkconstraint->deferrable;

fk_trigger->initdeferred=fkconstraint->initdeferred;

fk_trigger->constrrel=fkconstraint->pktable;

fk_trigger->args=NIL;

(void)CreateTrigger(fk_trigger,constraintOid,indexOid,

(void)CreateTrigger(fk_trigger,NULL,constraintOid,indexOid,

"RI_ConstraintTrigger", false);

fk_trigger->row= true;

fk_trigger->events=TRIGGER_TYPE_DELETE;

fk_trigger->columns=NIL;

fk_trigger->whenClause=NULL;

fk_trigger->isconstraint= true;

fk_trigger->constrrel=myRel;

switch (fkconstraint->fk_del_action)

}

fk_trigger->args=NIL;

(void)CreateTrigger(fk_trigger,constraintOid,indexOid,

(void)CreateTrigger(fk_trigger,NULL,constraintOid,indexOid,

"RI_ConstraintTrigger", false);

fk_trigger->row= true;

fk_trigger->events=TRIGGER_TYPE_UPDATE;

fk_trigger->columns=NIL;

fk_trigger->whenClause=NULL;

fk_trigger->isconstraint= true;

fk_trigger->constrrel=myRel;

switch (fkconstraint->fk_upd_action)

}

fk_trigger->args=NIL;

(void)CreateTrigger(fk_trigger,constraintOid,indexOid,

(void)CreateTrigger(fk_trigger,NULL,constraintOid,indexOid,

"RI_ConstraintTrigger", false);

}