<refsynopsisdiv>

<synopsis>

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

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

<title>Description</title>

<para>

<command>CREATE TRIGGER</command> creates a new trigger. The

<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

<title>Notes</title>

<para>

To create a trigger on a table, the user must have the

To createor replacea trigger on a table, the user must have the

<literal>TRIGGER</literal> privilege on the table. The user must

also have <literal>EXECUTE</literal> privilege on the trigger function.

</para>

Use <link linkend="sql-droptrigger"><command>DROP TRIGGER</command></link> to remove a trigger.

</para>

<para>

Creating a row-level trigger on a partitioned table will cause an

identical <quote>clone</quote> trigger to be created on each of its

existing partitions; and any partitions created or attached later will have

an identical trigger, too. If there is a conflictingly-named trigger on a

child partition already, an error occurs unless <command>CREATE OR REPLACE

TRIGGER</command> is used, in which case that trigger is replaced with a

clone trigger. When a partition is detached from its parent, its clone

triggers are removed.

</para>

<para>

A column-specific trigger (one defined using the <literal>UPDATE OF

<replaceable>column_name</replaceable></literal> syntax) will fire when any

value did not change.

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

<para>

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

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

the ones that are fired.

</para>

<para>

Creating a row-level trigger on a partitioned table will cause identical

triggers to be created in all its existing partitions; and any partitions

created or attached later will contain an identical trigger, too.

If the partition is detached from its parent, the trigger is removed.

Triggers on partitioned tables may not be <literal>INSTEAD OF</literal>.

</para>

<para>

Modifying a partitioned table or a table with inheritance children fires

statement-level triggers attached to the explicitly named table, but not

named by a <literal>REFERENCING</literal> clause, then before and after

images of rows are visible from all affected partitions or child tables.

In the case of inheritance children, the row images include only columns

that are present in the table that the trigger is attached to. Currently,

row-level triggers with transition relations cannot be defined on

partitions or inheritance child tables.

that are present in the table that the trigger is attached to.

</para>

<para>

Currently, row-level triggers with transition relations cannot be defined

on partitions or inheritance child tables. Also, triggers on partitioned

tables may not be <literal>INSTEAD OF</literal>.

</para>

<para>

Currently, the <literal>OR REPLACE</literal> option is not supported for

constraint triggers.

</para>

<para>

Replacing an existing trigger within a 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>

EXECUTE FUNCTION check_account_update();

</programlisting>

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

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

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

CREATEOR REPLACETRIGGER check_update

BEFORE UPDATE OF balance ON accounts

FOR EACH ROW

EXECUTE FUNCTION check_account_update();

<command>CREATE CONSTRAINT TRIGGER</command> is a

<productname>PostgreSQL</productname> extension of the <acronym>SQL</acronym>

standard.

So is the <literal>OR REPLACE</literal> option.

</para>

</refsect1>

if (deferrable)

{

CreateTrigStmt*trigger;

CreateTrigStmt*trigger=makeNode(CreateTrigStmt);

trigger=makeNode(CreateTrigStmt);

trigger->replace= false;

trigger->isconstraint= true;

trigger->trigname= (constraintType==CONSTRAINT_PRIMARY) ?

"PK_ConstraintTrigger" :

"Unique_ConstraintTrigger";

trigger->events=TRIGGER_TYPE_INSERT |TRIGGER_TYPE_UPDATE;

trigger->columns=NIL;

trigger->whenClause=NULL;

trigger->isconstraint=true;

trigger->transitionRels=NIL;

trigger->deferrable= true;

trigger->initdeferred=initdeferred;

trigger->constrrel=NULL;

* and "RI_ConstraintTrigger_c_NNNN" for the check triggers.

*/

fk_trigger = makeNode(CreateTrigStmt);

fk_trigger->replace = false;

fk_trigger->isconstraint = true;

fk_trigger->trigname = "RI_ConstraintTrigger_c";

fk_trigger->relation = NULL;

fk_trigger->row = true;

fk_trigger->timing = TRIGGER_TYPE_AFTER;

/* Either ON INSERT or ON UPDATE */

if (on_insert)

fk_trigger->events = TRIGGER_TYPE_UPDATE;

}

fk_trigger->args = NIL;

fk_trigger->row = true;

fk_trigger->timing = TRIGGER_TYPE_AFTER;

fk_trigger->columns = NIL;

fk_trigger->transitionRels = NIL;

fk_trigger->whenClause = NULL;

fk_trigger->isconstraint =true;

fk_trigger->transitionRels =NIL;

fk_trigger->deferrable = fkconstraint->deferrable;

fk_trigger->initdeferred = fkconstraint->initdeferred;

fk_trigger->constrrel = NULL;

fk_trigger->args = NIL;

(void) CreateTrigger(fk_trigger, NULL, myRelOid, refRelOid, constraintOid,

indexOid, InvalidOid, InvalidOid, NULL, true, false);

* DELETE action on the referenced table.

*/

fk_trigger = makeNode(CreateTrigStmt);

fk_trigger->replace = false;

fk_trigger->isconstraint = true;

fk_trigger->trigname = "RI_ConstraintTrigger_a";

fk_trigger->relation = NULL;

fk_trigger->args = NIL;

fk_trigger->row = true;

fk_trigger->timing = TRIGGER_TYPE_AFTER;

fk_trigger->events = TRIGGER_TYPE_DELETE;

fk_trigger->columns = NIL;

fk_trigger->transitionRels = NIL;

fk_trigger->whenClause = NULL;

fk_trigger->isconstraint =true;

fk_trigger->transitionRels =NIL;

fk_trigger->constrrel = NULL;

switch (fkconstraint->fk_del_action)

{

(int) fkconstraint->fk_del_action);

break;

}

fk_trigger->args = NIL;

(void) CreateTrigger(fk_trigger, NULL, refRelOid, RelationGetRelid(rel),

constraintOid,

* UPDATE action on the referenced table.

*/

fk_trigger = makeNode(CreateTrigStmt);

fk_trigger->replace = false;

fk_trigger->isconstraint = true;

fk_trigger->trigname = "RI_ConstraintTrigger_a";

fk_trigger->relation = NULL;

fk_trigger->args = NIL;

fk_trigger->row = true;

fk_trigger->timing = TRIGGER_TYPE_AFTER;

fk_trigger->events = TRIGGER_TYPE_UPDATE;

fk_trigger->columns = NIL;

fk_trigger->transitionRels = NIL;

fk_trigger->whenClause = NULL;

fk_trigger->isconstraint =true;

fk_trigger->transitionRels =NIL;

fk_trigger->constrrel = NULL;

switch (fkconstraint->fk_upd_action)

{

(int) fkconstraint->fk_upd_action);

break;

}

fk_trigger->args = NIL;

(void) CreateTrigger(fk_trigger, NULL, refRelOid, RelationGetRelid(rel),

constraintOid,

}

trigStmt = makeNode(CreateTrigStmt);

trigStmt->replace = false;

trigStmt->isconstraint = OidIsValid(trigForm->tgconstraint);

trigStmt->trigname = NameStr(trigForm->tgname);

trigStmt->relation = NULL;

trigStmt->funcname = NULL;/* passed separately */

trigStmt->events = trigForm->tgtype & TRIGGER_TYPE_EVENT_MASK;

trigStmt->columns = cols;

trigStmt->whenClause = NULL;/* passed separately */

trigStmt->isconstraint = OidIsValid(trigForm->tgconstraint);

trigStmt->transitionRels = NIL; /* not supported at present */

trigStmt->deferrable = trigForm->tgdeferrable;

trigStmt->initdeferred = trigForm->tginitdeferred;