diff options
Diffstat (limited to 'doc/src/sgml/ref/create_trigger.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_trigger.sgml | 777 |
1 files changed, 777 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml new file mode 100644 index 0000000..ee42f41 --- /dev/null +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -0,0 +1,777 @@ +<!-- +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 OF</literal> the trigger + event must be marked <literal>FOR EACH ROW</literal>, and can only be defined + on views. <literal>BEFORE</literal> and <literal>AFTER</literal> triggers on a view + must be marked as <literal>FOR EACH STATEMENT</literal>. + </para> + + <para> + In addition, triggers may be defined to fire for + <command>TRUNCATE</command>, though only + <literal>FOR EACH STATEMENT</literal>. + </para> + + <para> + The following table summarizes which types of triggers may be used on + tables, views, and foreign tables: + </para> + + <informaltable id="supported-trigger-types"> + <tgroup cols="4"> + <thead> + <row> + <entry>When</entry> + <entry>Event</entry> + <entry>Row-level</entry> + <entry>Statement-level</entry> + </row> + </thead> + <tbody> + <row> + <entry align="center" morerows="1"><literal>BEFORE</literal></entry> + <entry align="center"><command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command></entry> + <entry align="center">Tables and foreign tables</entry> + <entry align="center">Tables, views, and foreign tables</entry> + </row> + <row> + <entry align="center"><command>TRUNCATE</command></entry> + <entry align="center">—</entry> + <entry align="center">Tables</entry> + </row> + <row> + <entry align="center" morerows="1"><literal>AFTER</literal></entry> + <entry align="center"><command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command></entry> + <entry align="center">Tables and foreign tables</entry> + <entry align="center">Tables, views, and foreign tables</entry> + </row> + <row> + <entry align="center"><command>TRUNCATE</command></entry> + <entry align="center">—</entry> + <entry align="center">Tables</entry> + </row> + <row> + <entry align="center" morerows="1"><literal>INSTEAD OF</literal></entry> + <entry align="center"><command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command></entry> + <entry align="center">Views</entry> + <entry align="center">—</entry> + </row> + <row> + <entry align="center"><command>TRUNCATE</command></entry> + <entry align="center">—</entry> + <entry align="center">—</entry> + </row> + </tbody> + </tgroup> + </informaltable> + + <para> + Also, a trigger definition can specify a Boolean <literal>WHEN</literal> + condition, which will be tested to see whether the trigger should + be fired. In row-level triggers the <literal>WHEN</literal> condition can + examine the old and/or new values of columns of the row. Statement-level + triggers can also have <literal>WHEN</literal> 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. + </para> + + <para> + When the <literal>CONSTRAINT</literal> option is specified, this command creates a + <firstterm>constraint trigger</firstterm>.<indexterm><primary>trigger</primary> + <secondary>constraint trigger</secondary></indexterm> + This is the same as a regular trigger + except that the timing of the trigger firing can be adjusted using + <link linkend="sql-set-constraints"><command>SET CONSTRAINTS</command></link>. + Constraint triggers must be <literal>AFTER ROW</literal> triggers on plain + tables (not foreign tables). They + can be fired either at the end of the statement causing the triggering + event, or at the end of the containing transaction; in the latter case they + are said to be <firstterm>deferred</firstterm>. A pending deferred-trigger firing + can also be forced to happen immediately by using <command>SET + CONSTRAINTS</command>. Constraint triggers are expected to raise an exception + when the constraints they implement are violated. + </para> + + <para> + The <literal>REFERENCING</literal> option enables collection + of <firstterm>transition relations</firstterm>, which are row sets that include all + of the rows inserted, deleted, or modified by the current SQL statement. + This feature lets the trigger see a global view of what the statement did, + not just one row at a time. This option is only allowed for + an <literal>AFTER</literal> trigger that is not a constraint trigger; also, if + the trigger is an <literal>UPDATE</literal> trigger, it must not specify + a <replaceable class="parameter">column_name</replaceable> list. + <literal>OLD TABLE</literal> may only be specified once, and only for a trigger + that can fire on <literal>UPDATE</literal> or <literal>DELETE</literal>; it creates a + transition relation containing the <firstterm>before-images</firstterm> of all rows + updated or deleted by the statement. + Similarly, <literal>NEW TABLE</literal> may only be specified once, and only for + a trigger that can fire on <literal>UPDATE</literal> or <literal>INSERT</literal>; + it creates a transition relation containing the <firstterm>after-images</firstterm> + of all rows updated or inserted by the statement. + </para> + + <para> + <command>SELECT</command> does not modify any rows so you cannot + create <command>SELECT</command> triggers. Rules and views may provide + workable solutions to problems that seem to need <command>SELECT</command> + triggers. + </para> + + <para> + Refer to <xref linkend="triggers"/> for more information about triggers. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="parameter">name</replaceable></term> + <listitem> + <para> + The name to give the new trigger. This must be distinct from + the name of any other trigger for the same table. + The name cannot be schema-qualified — the trigger inherits the + schema of its table. For a constraint trigger, this is also the name to + use when modifying the trigger's behavior using + <command>SET CONSTRAINTS</command>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>BEFORE</literal></term> + <term><literal>AFTER</literal></term> + <term><literal>INSTEAD OF</literal></term> + <listitem> + <para> + Determines whether the function is called before, after, or instead of + the event. A constraint trigger can only be specified as + <literal>AFTER</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">event</replaceable></term> + <listitem> + <para> + One of <literal>INSERT</literal>, <literal>UPDATE</literal>, + <literal>DELETE</literal>, or <literal>TRUNCATE</literal>; + this specifies the event that will fire the trigger. Multiple + events can be specified using <literal>OR</literal>, except when + transition relations are requested. + </para> + + <para> + For <literal>UPDATE</literal> events, it is possible to + specify a list of columns using this syntax: +<synopsis> +UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</replaceable> ... ] +</synopsis> + The trigger will only fire if at least one of the listed columns + is mentioned as a target of the <command>UPDATE</command> command + or if one of the listed columns is a generated column that depends on a + column that is the target of the <command>UPDATE</command>. + </para> + + <para> + <literal>INSTEAD OF UPDATE</literal> events do not allow a list of columns. + A column list cannot be specified when requesting transition relations, + either. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">table_name</replaceable></term> + <listitem> + <para> + The name (optionally schema-qualified) of the table, view, or foreign + table the trigger is for. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">referenced_table_name</replaceable></term> + <listitem> + <para> + The (possibly schema-qualified) name of another table referenced by the + constraint. This option is used for foreign-key constraints and is not + recommended for general use. This can only be specified for + constraint triggers. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>DEFERRABLE</literal></term> + <term><literal>NOT DEFERRABLE</literal></term> + <term><literal>INITIALLY IMMEDIATE</literal></term> + <term><literal>INITIALLY DEFERRED</literal></term> + <listitem> + <para> + The default timing of the trigger. + See the <xref linkend="sql-createtable"/> documentation for details of + these constraint options. This can only be specified for constraint + triggers. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>REFERENCING</literal></term> + <listitem> + <para> + This keyword immediately precedes the declaration of one or two + relation names that provide access to the transition relations of the + triggering statement. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>OLD TABLE</literal></term> + <term><literal>NEW TABLE</literal></term> + <listitem> + <para> + This clause indicates whether the following relation name is for the + before-image transition relation or the after-image transition + relation. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">transition_relation_name</replaceable></term> + <listitem> + <para> + The (unqualified) name to be used within the trigger for this + transition relation. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>FOR EACH ROW</literal></term> + <term><literal>FOR EACH STATEMENT</literal></term> + + <listitem> + <para> + This specifies whether the trigger function should be fired + once for every row affected by the trigger event, or just once + per SQL statement. If neither is specified, <literal>FOR EACH + STATEMENT</literal> is the default. Constraint triggers can only + be specified <literal>FOR EACH ROW</literal>. + </para> + </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</literal> is specified, the + function will only be called if the <replaceable + class="parameter">condition</replaceable> returns <literal>true</literal>. + In <literal>FOR EACH ROW</literal> triggers, the <literal>WHEN</literal> + 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</literal> triggers cannot refer to <literal>OLD</literal> + and <literal>DELETE</literal> triggers cannot refer to <literal>NEW</literal>. + </para> + + <para><literal>INSTEAD OF</literal> triggers do not support <literal>WHEN</literal> + conditions. + </para> + + <para> + Currently, <literal>WHEN</literal> expressions cannot contain + subqueries. + </para> + + <para> + Note that for constraint triggers, evaluation of the <literal>WHEN</literal> + condition is not deferred, but occurs immediately after the row update + operation is performed. If the condition does not evaluate to true then + the trigger is not queued for deferred execution. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">function_name</replaceable></term> + <listitem> + <para> + A user-supplied function that is declared as taking no arguments + and returning type <literal>trigger</literal>, which is executed when + the trigger fires. + </para> + + <para> + In the syntax of <literal>CREATE TRIGGER</literal>, the keywords + <literal>FUNCTION</literal> and <literal>PROCEDURE</literal> are + equivalent, but the referenced function must in any case be a function, + not a procedure. The use of the keyword <literal>PROCEDURE</literal> + here is historical and deprecated. + </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. The arguments are + literal string constants. Simple names and numeric constants + can be written here, too, but they will all be converted to + strings. Please check the description of the implementation + language of the trigger function to find out how these arguments + can be accessed within the function; it might be different from + normal function arguments. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1 id="sql-createtrigger-notes"> + <title>Notes</title> + + <para> + To create or replace a 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> + + <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 + of its columns are listed as targets in the <command>UPDATE</command> + command's <literal>SET</literal> list. It is possible for a column's value + to change even when the trigger is not fired, because changes made to the + row's contents by <literal>BEFORE UPDATE</literal> triggers are not considered. + Conversely, a command such as <literal>UPDATE ... SET x = x ...</literal> + will fire a trigger on column <literal>x</literal>, even though the column's + value did not change. + </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 + <literal>WHEN</literal> is not materially different from testing the same + condition at the beginning of the trigger function. Note in particular + that the <literal>NEW</literal> row seen by the condition is the current value, + as possibly modified by earlier triggers. Also, a <literal>BEFORE</literal> + trigger's <literal>WHEN</literal> condition is not allowed to examine the + system columns of the <literal>NEW</literal> row (such as <literal>ctid</literal>), + because those won't have been set yet. + </para> + + <para> + In an <literal>AFTER</literal> trigger, the <literal>WHEN</literal> 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</literal> trigger's <literal>WHEN</literal> 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 some cases it is possible for a single SQL command to fire more than + one kind of trigger. For instance an <command>INSERT</command> with + an <literal>ON CONFLICT DO UPDATE</literal> clause may cause both insert and + update operations, so it will fire both kinds of triggers as needed. + The transition relations supplied to triggers are + specific to their event type; thus an <command>INSERT</command> trigger + will see only the inserted rows, while an <command>UPDATE</command> + trigger will see only the updated rows. + </para> + + <para> + Row updates or deletions caused by foreign-key enforcement actions, such + as <literal>ON UPDATE CASCADE</literal> or <literal>ON DELETE SET NULL</literal>, are + treated as part of the SQL command that caused them (note that such + actions are never deferred). Relevant triggers on the affected table will + be fired, so that this provides another way in which an SQL command might + fire triggers not directly matching its type. In simple cases, triggers + that request transition relations will see all changes caused in their + table by a single original SQL command as a single transition relation. + However, there are cases in which the presence of an <literal>AFTER ROW</literal> + trigger that requests transition relations will cause the foreign-key + enforcement actions triggered by a single SQL command to be split into + multiple steps, each with its own transition relation(s). In such cases, + any statement-level triggers that are present will be fired once per + creation of a transition relation set, ensuring that the triggers see + each affected row in a transition relation once and only once. + </para> + + <para> + Statement-level triggers on a view are fired only if the action on the + view is handled by a row-level <literal>INSTEAD OF</literal> trigger. + If the action is handled by an <literal>INSTEAD</literal> rule, then + whatever statements are emitted by the rule are executed in place of the + original statement naming the view, so that the triggers that will be + fired are those on tables named in the replacement statements. + Similarly, if the view is automatically updatable, then the action is + handled by automatically rewriting the statement into an action on the + view's base table, so that the base table's statement-level triggers are + the ones that are fired. + </para> + + <para> + Modifying a partitioned table or a table with inheritance children fires + statement-level triggers attached to the explicitly named table, but not + statement-level triggers for its partitions or child tables. In contrast, + row-level triggers are fired on the rows in affected partitions or + child tables, even if they are not explicitly named in the query. + If a statement-level trigger has been defined with transition relations + 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. + </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> + + <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 + <productname>PostgreSQL</productname> implements a subset of the + <acronym>SQL</acronym> standard. The following functionalities are currently + missing: + + <itemizedlist> + <listitem> + <para> + While transition table names for <literal>AFTER</literal> triggers are + specified using the <literal>REFERENCING</literal> clause in the standard way, + the row variables used in <literal>FOR EACH ROW</literal> triggers may not be + specified in a <literal>REFERENCING</literal> clause. They are available in a + manner that is dependent on the language in which the trigger function + is written, but is fixed for any one language. Some languages + effectively behave as though there is a <literal>REFERENCING</literal> clause + containing <literal>OLD ROW AS OLD NEW ROW AS NEW</literal>. + </para> + </listitem> + + <listitem> + <para> + The standard allows transition tables to be used with + column-specific <literal>UPDATE</literal> triggers, but then the set of rows + that should be visible in the transition tables depends on the + trigger's column list. This is not currently implemented by + <productname>PostgreSQL</productname>. + </para> + </listitem> + + <listitem> + <para> + <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 + limitation is not hard to work around by creating a user-defined + function that executes the desired commands. + </para> + </listitem> + + </itemizedlist> + </para> + + <para> + SQL specifies that multiple triggers should be fired in + time-of-creation order. <productname>PostgreSQL</productname> uses + name order, which was judged to be more convenient. + </para> + + <para> + SQL specifies that <literal>BEFORE DELETE</literal> triggers on cascaded + deletes fire <emphasis>after</emphasis> the cascaded <literal>DELETE</literal> completes. + The <productname>PostgreSQL</productname> behavior is for <literal>BEFORE + DELETE</literal> to always fire before the delete action, even a cascading + one. This is considered more consistent. There is also nonstandard + behavior if <literal>BEFORE</literal> triggers modify rows or prevent + updates during an update that is caused by a referential action. This can + lead to constraint violations or stored data that does not honor the + referential constraint. + </para> + + <para> + The ability to specify multiple actions for a single trigger using + <literal>OR</literal> is a <productname>PostgreSQL</productname> extension of + the SQL standard. + </para> + + <para> + The ability to fire triggers for <command>TRUNCATE</command> is a + <productname>PostgreSQL</productname> extension of the SQL standard, as is the + ability to define statement-level triggers on views. + </para> + + <para> + <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> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="sql-altertrigger"/></member> + <member><xref linkend="sql-droptrigger"/></member> + <member><xref linkend="sql-createfunction"/></member> + <member><xref linkend="sql-set-constraints"/></member> + </simplelist> + </refsect1> +</refentry> |