diff options
Diffstat (limited to 'doc/src/sgml/ref/create_trigger.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_trigger.sgml | 76 |
1 files changed, 53 insertions, 23 deletions
diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index 60346e1e83b..561af989a4a 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -26,7 +26,7 @@ PostgreSQL documentation <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 ] ] @@ -48,7 +48,9 @@ CREATE [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name</replaceable> <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 @@ -56,6 +58,12 @@ CREATE [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name</replaceable> </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 @@ -436,7 +444,7 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</ <title>Notes</title> <para> - To create a trigger on a table, the user must have the + 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> @@ -446,6 +454,17 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</ </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> @@ -458,12 +477,6 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</ </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 <literal>WHEN</literal> is not materially different from testing the same @@ -529,14 +542,6 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</ </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 statement-level triggers for its partitions or child tables. In contrast, @@ -546,9 +551,32 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</ 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> @@ -566,11 +594,12 @@ CREATE TRIGGER check_update 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 +CREATE OR REPLACE TRIGGER check_update BEFORE UPDATE OF balance ON accounts FOR EACH ROW EXECUTE FUNCTION check_account_update(); @@ -728,6 +757,7 @@ CREATE TRIGGER paired_items_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> |