<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.33 2002/03/22 19:20:39 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.34 2002/04/19 16:36:08 tgl Exp $

PostgreSQL documentation

-->

</refsynopsisdivinfo>

<synopsis>

CREATE RULE <replaceable class="parameter">name</replaceable> AS ON <replaceable class="parameter">event</replaceable>

TO <replaceable class="parameter">object</replaceable> [ WHERE <replaceable class="parameter">condition</replaceable> ]

TO <replaceable class="parameter">table</replaceable> [ WHERE <replaceable class="parameter">condition</replaceable> ]

DO [ INSTEAD ] <replaceable class="parameter">action</replaceable>

where <replaceable class="PARAMETER">action</replaceable> can be:

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

<listitem>

<para>

The name of a rule to create.

The name of a rule to create. This must be distinct from the name

of any other rule for the same table.

</para>

</listitem>

</varlistentry>

</listitem>

</varlistentry>

<varlistentry>

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

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

<listitem>

<para>

Object is either <replaceable class="parameter">table</replaceable>

or <replaceable class="parameter">table</replaceable>.<replaceable

class="parameter">column</replaceable>. (Currently, only the

<replaceable class="parameter">table</replaceable> form is

actually implemented.)

The name (optionally schema-qualified) of the table or view the rule

applies to.

</para>

</listitem>

</varlistentry>

Within the <replaceable class="parameter">condition</replaceable>

and <replaceable class="PARAMETER">action</replaceable>, the special

table names <literal>new</literal> and <literal>old</literal> may be

used to refer to values in the referenced table (the

<replaceable class="parameter">object</replaceable>).

used to refer to values in the referenced table.

<literal>new</literal> is valid in ON INSERT and ON UPDATE rules

to refer to the new row being inserted or updated.

<literal>old</literal> is valid in ON UPDATE and ON DELETE

accessed, inserted, updated, or deleted, there is an old instance (for

selects, updates and deletes) and a new instance (for inserts and

updates). All the rules for the given event type and the given target

object (table) are examined,inan unspecified order. If the

table are examined successively (inorder by name). If the

<replaceable class="parameter">condition</replaceable> specified in the

WHERE clause (if any) is true, the

<replaceable class="parameter">action</replaceable> part of the rule is

The <replaceable class="parameter">action</replaceable> part of the

rule can consist of one or more queries. To write multiple queries,

surround them with parentheses. Such queries will be performed in the

specified order (whereas there are no guarantees about the execution

order of multiple rules for an object). The <replaceable

specified order. The <replaceable

class="parameter">action</replaceable> can also be NOTHING indicating

no action. Thus, a DO INSTEAD NOTHING rule suppresses the original

query from executing (when its condition is true); a DO NOTHING rule

executes with the same command and transaction identifier as the user

command that caused activation.

</para>

<para>

It is important to realize that a rule is really a query transformation

mechanism, or query macro. The entire query is processed to convert it

into a series of queries that include the rule actions. This occurs

before evaluation of the query starts. So, conditional rules are

handled by adding the rule condition to the WHERE clause of the action(s)

derived from the rule. The above description of a rule as an operation

that executes for each row is thus somewhat misleading. If you actually

want an operation that fires independently for each physical row, you

probably want to use a trigger not a rule. Rules are most useful for

situations that call for transforming entire queries independently of

the specific data being handled.

</para>

<refsect2 id="R2-SQL-CREATERULE-3">

<refsect2info>

<para>

Presently, ON SELECT rules must be unconditional INSTEAD rules and must

have actions that consist of a single SELECT query. Thus, an ON SELECT

rule effectively turns theobjecttable into a view, whose visible

rule effectively turns the table into a view, whose visible

contents are the rows returned by the rule's SELECT query rather than

whatever had been stored in the table (if anything). It is considered

better style to write a CREATE VIEW command than to create a real table

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_trigger.sgml,v 1.22 2002/01/20 22:19:56 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_trigger.sgml,v 1.23 2002/04/19 16:36:08 tgl Exp $

PostgreSQL documentation

-->

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

<listitem>

<para>

The name to give the new trigger.

The name to give the new trigger. This must be distinct from the name

of any other trigger for the same table.

</para>

</listitem>

</varlistentry>

<varlistentry>

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

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

<listitem>

<para>

The nameofan existing table.

OneofINSERT, DELETE or UPDATE.

</para>

</listitem>

</varlistentry>

<varlistentry>

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

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

<listitem>

<para>

OneofINSERT, DELETE or UPDATE.

The name (optionally schema-qualified)ofthe table the trigger is for.

</para>

</listitem>

</varlistentry>

<varlistentry>

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

<listitem>

<para>

A user-supplied function.

A user-supplied function that is declared as taking no arguments

and returning type <literal>opaque</>.

</para>

</listitem>

</varlistentry>

<varlistentry>

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

<listitem>

<para>

An optional comma-separated list of arguments to be provided to the

function when the trigger is executed, along with the standard trigger

data such as old and new tuple contents. The arguments are literal

string constants. Simple names and numeric constants may be written

here too, but they will all be converted to strings.

</para>

</listitem>

</varlistentry>

after the event, all changes, including the last insertion, update,

or deletion, are <quote>visible</quote> to the trigger.

</para>

<para>

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

they will be fired in alphabetical order by name.

</para>

<para>

<command>SELECT</command> does not modify any rows so you can not

create <command>SELECT</command> triggers. Rules and views are more

</listitem>

</itemizedlist>

</para>

<para>

SQL99 specifies that multiple triggers should be fired in

time-of-creation order. <productname>PostgreSQL</productname>

uses name order, which was judged more convenient to work with.

</para>

</listitem>

</varlistentry>

</variablelist>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/trigger.sgml,v 1.22 2002/04/01 22:36:06 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/trigger.sgml,v 1.23 2002/04/19 16:36:08 tgl Exp $

-->

<chapter id="triggers">

AFTER on INSERT, DELETE or UPDATE of a tuple as a trigger event.

</para>

<sect1 id="trigger-create">

<title>TriggerCreation</title>

<sect1 id="trigger-definition">

<title>TriggerDefinition</title>

<para>

If a trigger event occurs, the trigger manager (called by the Executor)

</para>

<para>

The trigger function must be defined before the trigger is created as a

function taking no arguments and returning opaque. If the function is

written in C, it must use the <quote>version 1</> function manager interface.

The trigger function must be defined before the trigger itself can be

created. The trigger function must be declared as a

function taking no arguments and returning type <literal>opaque</>.

(The trigger function receives its input through a TriggerData

structure, not in the form of ordinary function arguments.)

If the function is written in C, it must use the <quote>version 1</>

function manager interface.

</para>

<para>

The syntax for creating triggers is as follows:

The syntax for creating triggers is:

<programlisting>

CREATE TRIGGER <replaceable>trigger</replaceable> [ BEFORE | AFTER ] [ INSERT | DELETE | UPDATE [ OR ... ] ]

</term>

<listitem>

<para>

The nameof the trigger is

It is used as an argumentto the<command>DROP TRIGGER</command> command.

Thetrigger must have anamedistinct from all other triggers on

if you ever havetodeletethetrigger.

</para>

</listitem>

</varlistentry>

<term>UPDATE</term>

<listitem>

<para>

The next element of the command determinesonwhat event(s) will trigger

The next element of the command determines what event(s) will trigger

the function. Multiple events can be specified separated by OR.

</para>

</listitem>

<term><replaceable>relation</replaceable></term>

<listitem>

<para>

The relation namedetermines which table the event applies to.

The relation nameindicates which table the event applies to.

</para>

</listitem>

</varlistentry>

<para>

The FOR EACH clause determines whether the trigger is fired for each

affected row or before (or after) the entire statement has completed.

Currently only the ROW case is supported.

</para>

</listitem>

</varlistentry>

<term><replaceable>procedure</replaceable></term>

<listitem>

<para>

The procedure name is the function called.

The procedure name is the functionto becalled.

</para>

</listitem>

</varlistentry>

<listitem>

<para>

The arguments passed to the function in the TriggerData structure.

triggers with similar requirementstocallthesamefunction.

constants (which will be passedto the function as strings).

</para>

<para>

may be used for triggeringdifferent relations (these

functions are named as <firstterm>general trigger functions</>).

As example of using both features above, there couldbea general

function that takes as its arguments two field names and putsthecurrent

be written on INSERT eventsto automatically track creation of records in a

transaction table forexample. It could also be usedas a <quote>last updated</>

function if used inan UPDATEevent.

is to allowdifferent

triggers with similar requirements to call the same function.

Properly written, this trigger function wouldbeindependent of

the specific table it is triggering on. Sothesame function

to automatically track creation of records in a transaction table for

example. It could also be usedto tracklast-update events if

defined asan UPDATEtrigger.

</para>

</listitem>

</varlistentry>

</variablelist>

</para>

<para>

Trigger functions return HeapTuple to the calling Executor.This

is ignored for triggers firedafter anINSERT, DELETE or UPDATEoperation

Trigger functions returnaHeapTuple to the calling Executor.The return

valueis ignored for triggers firedAFTER an operation,

but it allows BEFORE triggers to:

<itemizedlist>

<listitem>

<para>

Return a pointer to another tuple (INSERT and UPDATE only) which will

be inserted (as the new version of the updated tuple if UPDATE) instead

of original tuple.

For INSERT and UPDATE triggers only, the returned tuple becomes the

tuple which will be inserted or will replace the tuple being updated.

This allows the trigger function to modify the row being inserted or

updated.

</para>

</listitem>

</itemizedlist>

A BEFORE trigger that does not intend to cause either of these behaviors

must be careful to return the same NEW tuple it is passed.

</para>

<para>

Note that there is no initialization performed by the CREATE TRIGGER

handler. This will be changed in the future. Also, if more than one trigger

is defined for the same event on the same relation, the order of trigger

firing is unpredictable. This may be changed in the future.

handler. This may be changed in the future.

</para>

<para>

If a trigger function executes SQL-queries (using SPI) then these queries

may fire triggers again. This is known as cascading triggers. There is no

explicit limitation on the number of cascade levels.

If more than one trigger

is defined for the same event on the same relation, the triggers will

be fired in alphabetical order by name. In the case of BEFORE triggers,

the possibly-modified tuple returned by each trigger becomes the input

to the next trigger. If any BEFORE trigger returns NULL, the operation

is abandoned and subsequent triggers are not fired.

</para>

<para>

If a trigger is fired by INSERT and inserts a new tuple in the same

relation then this trigger will be fired again. Currently, there is nothing

provided for synchronization (etc) of these cases but this may change. At

the moment, there is function funny_dup17() in the regress tests which uses

some techniques to stop recursion (cascading) on itself...

If a trigger function executes SQL-queries (using SPI) then these queries

may fire triggers again. This is known as cascading triggers. There is no

direct limitation on the number of cascade levels. It is possible for

cascades to cause recursive invocation of the same trigger --- for

example, an INSERT trigger might execute a query that inserts an

additional tuple into the same table, causing the INSERT trigger to be

fired again. It is the trigger programmer's

responsibility to avoid infinite recursion in such scenarios.

</para>

</sect1>

<para>

is a pointer to structure describing the triggered relation. Look at

src/include/utils/rel.h for details about this structure. The most

interest things are tg_relation->rd_att (descriptor of the relation

interesting things are tg_relation->rd_att (descriptor of the relation

tuples) and tg_relation->rd_rel->relname (relation's name. This is not

char*, but NameData. Use SPI_getrelname(tg_relation) to get char* if

you need a copy of name).