CREATE CONSTRAINT TRIGGER
Started by Alvaro Herreraabout 15 years ago4 messages
Some time ago, CREATE CONSTRAINT TRIGGER was a deprecated command and it
was slated for obsolence or removal.
Recent developments have turned it back into non-deprecated mode; it's
not going anywhere, and it needs to be fully documented.
It seems to me that it makes more sense to merge its documentation into
the CREATE TRIGGER page, where it belongs. Right now, the documentation
page for CREATE CONSTRAINT TRIGGER is 50% a repetition of what's in the
CREATE TRIGGER page; and a significant portion of the rest is spent
telling the user to refer to the CREATE TRIGGER page. The original
information to be found in CREATE CONSTRAINT TRIGGER is not all that
much.
Thus the attached patch.
--
Álvaro Herrera <alvherre@alvh.no-ip.org>
Attachments:
0001-Fold-the-CREATE-CONSTRAINT-TRIGGER-doc-page-into-CRE.patchapplication/octet-stream; name=0001-Fold-the-CREATE-CONSTRAINT-TRIGGER-doc-page-into-CRE.patchDownload
From 8fd540e963cd9f3d0bbeddc5205a9f792bc5ef21 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Fri, 5 Nov 2010 18:09:09 -0300
Subject: [PATCH] Fold the CREATE CONSTRAINT TRIGGER doc page into CREATE TRIGGER.
---
doc/src/sgml/ref/allfiles.sgml | 1 -
doc/src/sgml/ref/create_constraint.sgml | 171 -------------------------------
doc/src/sgml/ref/create_trigger.sgml | 67 ++++++++++++-
doc/src/sgml/reference.sgml | 1 -
4 files changed, 64 insertions(+), 176 deletions(-)
delete mode 100644 doc/src/sgml/ref/create_constraint.sgml
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index f5d67a2..a352a43 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -46,7 +46,6 @@ Complete list of usable sgml source files in this directory.
<!entity copyTable system "copy.sgml">
<!entity createAggregate system "create_aggregate.sgml">
<!entity createCast system "create_cast.sgml">
-<!entity createConstraint system "create_constraint.sgml">
<!entity createConversion system "create_conversion.sgml">
<!entity createDatabase system "create_database.sgml">
<!entity createDomain system "create_domain.sgml">
diff --git a/doc/src/sgml/ref/create_constraint.sgml b/doc/src/sgml/ref/create_constraint.sgml
deleted file mode 100644
index 3ec3f74..0000000
--- a/doc/src/sgml/ref/create_constraint.sgml
+++ /dev/null
@@ -1,171 +0,0 @@
-<!--
-doc/src/sgml/ref/create_constraint.sgml
-PostgreSQL documentation
--->
-
-<refentry id="SQL-CREATECONSTRAINT">
- <refmeta>
- <refentrytitle>CREATE CONSTRAINT TRIGGER</refentrytitle>
- <manvolnum>7</manvolnum>
- <refmiscinfo>SQL - Language Statements</refmiscinfo>
- </refmeta>
-
- <refnamediv>
- <refname>CREATE CONSTRAINT TRIGGER</refname>
- <refpurpose>define a new constraint trigger</refpurpose>
- </refnamediv>
-
- <indexterm zone="sql-createconstraint">
- <primary>CREATE CONSTRAINT TRIGGER</primary>
- </indexterm>
-
- <refsynopsisdiv>
-<synopsis>
-CREATE CONSTRAINT TRIGGER <replaceable class="parameter">name</replaceable>
- AFTER <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 } }
- FOR EACH ROW
- [ WHEN ( <replaceable class="parameter">condition</replaceable> ) ]
- EXECUTE PROCEDURE <replaceable class="parameter">function_name</replaceable> ( <replaceable class="parameter">arguments</replaceable> )
-</synopsis>
- </refsynopsisdiv>
-
- <refsect1>
- <title>Description</title>
-
- <para>
- <command>CREATE CONSTRAINT TRIGGER</command> creates a
- <firstterm>constraint trigger</>. This is the same as a regular trigger
- except that the timing of the trigger firing can be adjusted using
- <xref linkend="SQL-SET-CONSTRAINTS">.
- Constraint triggers must be <literal>AFTER ROW</> triggers. 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</>. A pending deferred-trigger firing can
- also be forced to happen immediately by using <command>SET CONSTRAINTS</>.
- </para>
- </refsect1>
-
- <refsect1>
- <title>Parameters</title>
-
- <variablelist>
- <varlistentry>
- <term><replaceable class="PARAMETER">name</replaceable></term>
- <listitem>
- <para>
- The name of the constraint trigger. This is also the name to use
- when modifying the trigger's behavior using <command>SET CONSTRAINTS</>.
- The name cannot be schema-qualified — the trigger inherits the
- schema of its table.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><replaceable class="PARAMETER">event</replaceable></term>
- <listitem>
- <para>
- One of <literal>INSERT</literal>, <literal>UPDATE</literal>, or
- <literal>DELETE</literal>; this specifies the event that will fire the
- trigger. Multiple events can be specified using <literal>OR</literal>.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><replaceable class="PARAMETER">table_name</replaceable></term>
- <listitem>
- <para>
- The (possibly schema-qualified) name of the table in which
- the triggering events occur.
- </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.
- </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.
- </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. This acts the same as in <xref
- linkend="SQL-CREATETRIGGER">.
- 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>
- <para>
- The function to call when the trigger is fired. See <xref
- linkend="SQL-CREATETRIGGER"> for
- details.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><replaceable class="PARAMETER">arguments</replaceable></term>
- <listitem>
- <para>
- Optional argument strings to pass to the trigger function. See <xref
- linkend="SQL-CREATETRIGGER"> for
- details.
- </para>
- </listitem>
- </varlistentry>
- </variablelist>
- </refsect1>
-
- <refsect1>
- <title>Compatibility</title>
- <para>
- <command>CREATE CONSTRAINT TRIGGER</command> is a
- <productname>PostgreSQL</productname> extension of the <acronym>SQL</>
- standard.
- </para>
- </refsect1>
-
- <refsect1>
- <title>See Also</title>
-
- <simplelist type="inline">
- <member><xref linkend="sql-createtrigger"></member>
- <member><xref linkend="sql-droptrigger"></member>
- <member><xref linkend="sql-set-constraints"></member>
- </simplelist>
- </refsect1>
-</refentry>
diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
index 95d67aa..3db7c14 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -21,8 +21,10 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTER | INSTEAD OF } { <replaceable class="PARAMETER">event</replaceable> [ OR ... ] }
+CREATE [ CONSTRAINT ] TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTER | INSTEAD OF } { <replaceable class="PARAMETER">event</replaceable> [ OR ... ] }
ON <replaceable class="PARAMETER">table</replaceable> [ FOR [ EACH ] { ROW | STATEMENT } ]
+ [ FROM <replaceable class="parameter">referenced_table_name</replaceable> ]
+ { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
[ WHEN ( <replaceable class="parameter">condition</replaceable> ) ]
EXECUTE PROCEDURE <replaceable class="PARAMETER">function_name</replaceable> ( <replaceable class="PARAMETER">arguments</replaceable> )
</synopsis>
@@ -156,6 +158,18 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
</para>
<para>
+ When the <literal>CONSTRAINT</> option is specified, this command creates a
+ <firstterm>constraint trigger</>. This is the same as a regular trigger
+ except that the timing of the trigger firing can be adjusted using
+ <xref linkend="SQL-SET-CONSTRAINTS">.
+ Constraint triggers must be <literal>AFTER ROW</> triggers. 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</>. A pending deferred-trigger firing can
+ also be forced to happen immediately by using <command>SET CONSTRAINTS</>.
+ </para>
+
+ <para>
Refer to <xref linkend="triggers"> for more information about triggers.
</para>
</refsect1>
@@ -170,6 +184,10 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
<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</>.
</para>
</listitem>
</varlistentry>
@@ -181,7 +199,8 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
<listitem>
<para>
Determines whether the function is called before, after, or instead of
- the event.
+ the event. A constraint trigger can only be specified as
+ <literal>AFTER</>.
</para>
</listitem>
</varlistentry>
@@ -223,6 +242,33 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
</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>FOR EACH ROW</literal></term>
<term><literal>FOR EACH STATEMENT</literal></term>
@@ -231,7 +277,8 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
This specifies whether the trigger procedure 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.
+ STATEMENT</literal> is the default. Constraint triggers can only
+ be specified <literal>FOR EACH ROW</>.
</para>
</listitem>
</varlistentry>
@@ -263,6 +310,13 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
Currently, <literal>WHEN</literal> expressions cannot contain
subqueries.
</para>
+
+ <para>
+ Note that for constraint triggers, 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 true then
+ the trigger is not queued for deferred execution.
+ </para>
</listitem>
</varlistentry>
@@ -481,6 +535,12 @@ CREATE TRIGGER view_insert
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</>
+ standard.
+ </para>
+
</refsect1>
<refsect1>
@@ -490,6 +550,7 @@ CREATE TRIGGER view_insert
<member><xref linkend="sql-createfunction"></member>
<member><xref linkend="sql-altertrigger"></member>
<member><xref linkend="sql-droptrigger"></member>
+ <member><xref linkend="sql-set-constraints"></member>
</simplelist>
</refsect1>
</refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index 463746c..13de002 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -74,7 +74,6 @@
©Table;
&createAggregate;
&createCast;
- &createConstraint;
&createConversion;
&createDatabase;
&createDomain;
--
1.7.1
Re: CREATE CONSTRAINT TRIGGER
On Fri, Nov 5, 2010 at 2:37 PM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
Recent developments have turned it back into non-deprecated mode; it's
not going anywhere, and it needs to be fully documented.
From what I recall, there isn't anything in the trigger documentation
or CREATE CONSTRAINT TRIGGER documentation that says the trigger
function must explicitly raise an exception to create the notification
that the custom constraint was violated.
Would this be a good place for it?
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
Re: CREATE CONSTRAINT TRIGGER
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
Some time ago, CREATE CONSTRAINT TRIGGER was a deprecated command and it
was slated for obsolence or removal.
Recent developments have turned it back into non-deprecated mode; it's
not going anywhere, and it needs to be fully documented.
It seems to me that it makes more sense to merge its documentation into
the CREATE TRIGGER page, where it belongs.
Seems reasonable. I didn't read the patch in detail though.
regards, tom lane
Re: CREATE CONSTRAINT TRIGGER
Excerpts from Richard Broersma's message of vie nov 05 18:54:54 -0300 2010:
On Fri, Nov 5, 2010 at 2:37 PM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
Recent developments have turned it back into non-deprecated mode; it's
not going anywhere, and it needs to be fully documented.From what I recall, there isn't anything in the trigger documentation
or CREATE CONSTRAINT TRIGGER documentation that says the trigger
function must explicitly raise an exception to create the notification
that the custom constraint was violated.Would this be a good place for it?
I added a sentence about this, and pushed it.
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support