Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1b7f3cc

Browse files
committed
This patch implements FOR EACH STATEMENT triggers, per my email to
-hackers a couple days ago.Notes/caveats: - added regression tests for the new functionality, all regression tests pass on my machine - added pg_dump support - updated PL/PgSQL to support per-statement triggers; didn't look at the other procedural languages. - there's (even) more code duplication in trigger.c than there was previously. Any suggestions on how to refactor the ExecXXXTriggers() functions to reuse more code would be welcome -- I took a brief look at it, but couldn't see an easy way to do it (there are several subtly-different versions of the code in question) - updated the documentation. I also took the liberty of removing a big chunk of duplicated syntax documentation in the Programmer's Guide on triggers, and moving that information to the CREATE TRIGGER reference page. - I also included some spelling fixes and similar small cleanups I noticed while making the changes. If you'd like me to split those into a separate patch, let me know.Neil Conway
1 parentea29b32 commit1b7f3cc

File tree

24 files changed

+702
-411
lines changed

24 files changed

+702
-411
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 55 additions & 49 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.11 2002/11/15 03:22:30 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.12 2002/11/23 03:59:05 momjian Exp $
33
-->
44

55
<chapter id="plpgsql">
@@ -674,24 +674,25 @@ RENAME this_var TO that_var;
674674
<title>Expressions</title>
675675

676676
<para>
677-
All expressions used in <application>PL/pgSQL</application> statements
678-
are processed using the server's regular SQL executor. Expressions that
679-
appear to contain
680-
constants may in fact require run-time evaluation
681-
(e.g. <literal>'now'</literal>for the
682-
<type>timestamp</type> type) so
683-
it is impossible for the<application>PL/pgSQL</application> parser
684-
to identify realconstant values other than the NULL keyword. All
685-
expressions areevaluated internally by executing a query
677+
All expressions used in <application>PL/pgSQL</application>
678+
statementsare processed using the server's regular
679+
<acronym>SQL</acronym> executor. Expressions that appear to
680+
containconstants may in fact require run-time evaluation
681+
(e.g. <literal>'now'</literal> for the<type>timestamp</type>
682+
type) so it is impossible for the
683+
<application>PL/pgSQL</application> parser to identify real
684+
constant values other than the NULL keyword. All expressions are
685+
evaluated internally by executing a query
686686
<synopsis>
687687
SELECT <replaceable>expression</replaceable>
688688
</synopsis>
689-
using the <acronym>SPI</acronym> manager. In the expression, occurrences
690-
of <application>PL/pgSQL</application> variable
689+
using the <acronym>SPI</acronym> manager. In the expression,
690+
occurrencesof <application>PL/pgSQL</application> variable
691691
identifiers are replaced by parameters and the actual values from
692692
the variables are passed to the executor in the parameter array.
693-
This allows the query plan for the SELECT to be prepared just once
694-
and then re-used for subsequent evaluations.
693+
This allows the query plan for the <command>SELECT</command> to
694+
be prepared just once and then re-used for subsequent
695+
evaluations.
695696
</para>
696697

697698
<para>
@@ -1100,41 +1101,43 @@ GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replace
11001101
<itemizedlist>
11011102
<listitem>
11021103
<para>
1103-
A SELECT INTO statement sets <literal>FOUND</literal>
1104-
true if it returns a row, false if no row is returned.
1104+
A <command>SELECT INTO</command> statement sets
1105+
<literal>FOUND</literal> true if it returns a row, false if no
1106+
row is returned.
11051107
</para>
11061108
</listitem>
11071109
<listitem>
11081110
<para>
1109-
A PERFORM statement sets <literal>FOUND</literal>
1111+
A<command>PERFORM</> statement sets <literal>FOUND</literal>
11101112
true if it produces (discards) a row, false if no row is
11111113
produced.
11121114
</para>
11131115
</listitem>
11141116
<listitem>
11151117
<para>
1116-
UPDATE,INSERT, and DELETE statements set
1117-
<literal>FOUND</literal> true if at least one row is
1118-
affected, false if no row is affected.
1118+
<command>UPDATE</>, <command>INSERT</>, and<command>DELETE</>
1119+
statements set<literal>FOUND</literal> true if at least one
1120+
row isaffected, false if no row is affected.
11191121
</para>
11201122
</listitem>
11211123
<listitem>
11221124
<para>
1123-
A FETCH statement sets <literal>FOUND</literal>
1125+
A<command>FETCH</> statement sets <literal>FOUND</literal>
11241126
true if it returns a row, false if no row is returned.
11251127
</para>
11261128
</listitem>
11271129
<listitem>
11281130
<para>
1129-
A FOR statement sets <literal>FOUND</literal>
1130-
true if it iterates one or more times, else false.
1131-
This applies to all three variants of the FOR statement
1132-
(integer FOR loops, record-set FOR loops, and dynamic
1133-
record-set FOR loops). <literal>FOUND</literal> is only set
1134-
when the FOR loop exits: inside the execution of the loop,
1135-
<literal>FOUND</literal> is not modified by the FOR statement,
1136-
although it may be changed by the execution of other
1137-
statements within the loop body.
1131+
A <command>FOR</> statement sets <literal>FOUND</literal> true
1132+
if it iterates one or more times, else false. This applies to
1133+
all three variants of the <command>FOR</> statement (integer
1134+
<command>FOR</> loops, record-set <command>FOR</> loops, and
1135+
dynamic record-set <command>FOR</>
1136+
loops). <literal>FOUND</literal> is only set when the
1137+
<command>FOR</> loop exits: inside the execution of the loop,
1138+
<literal>FOUND</literal> is not modified by the
1139+
<command>FOR</> statement, although it may be changed by the
1140+
execution of other statements within the loop body.
11381141
</para>
11391142
</listitem>
11401143
</itemizedlist>
@@ -1975,7 +1978,7 @@ RAISE EXCEPTION ''Inexistent ID --> %'',user_id;
19751978
<application>PL/pgSQL</application> can be used to define trigger
19761979
procedures. A trigger procedure is created with the
19771980
<command>CREATE FUNCTION</> command as a function with no
1978-
arguments and a return type of <type>TRIGGER</type>. Note that
1981+
arguments and a return type of <type>trigger</type>. Note that
19791982
the function must be declared with no arguments even if it expects
19801983
to receive arguments specified in <command>CREATE TRIGGER</> ---
19811984
trigger arguments are passed via <varname>TG_ARGV</>, as described
@@ -1992,8 +1995,9 @@ RAISE EXCEPTION ''Inexistent ID --> %'',user_id;
19921995
<term><varname>NEW</varname></term>
19931996
<listitem>
19941997
<para>
1995-
Data type <type>RECORD</type>; variable holding the new database row for INSERT/UPDATE
1996-
operations in ROW level triggers.
1998+
Data type <type>RECORD</type>; variable holding the new
1999+
database row for INSERT/UPDATE operations in ROW level
2000+
triggers. This variable is NULL in STATEMENT level triggers.
19972001
</para>
19982002
</listitem>
19992003
</varlistentry>
@@ -2002,8 +2006,9 @@ RAISE EXCEPTION ''Inexistent ID --> %'',user_id;
20022006
<term><varname>OLD</varname></term>
20032007
<listitem>
20042008
<para>
2005-
Data type <type>RECORD</type>; variable holding the old database row for UPDATE/DELETE
2006-
operations in ROW level triggers.
2009+
Data type <type>RECORD</type>; variable holding the old
2010+
database row for UPDATE/DELETE operations in ROW level
2011+
triggers. This variable is NULL in STATEMENT level triggers.
20072012
</para>
20082013
</listitem>
20092014
</varlistentry>
@@ -2098,22 +2103,23 @@ RAISE EXCEPTION ''Inexistent ID --> %'',user_id;
20982103

20992104
<para>
21002105
A trigger function must return either NULL or a record/row value
2101-
having exactly the structure of the table the trigger was fired for.
2102-
Triggers fired BEFORE may return NULL to signal the trigger manager
2103-
to skip the rest of the operation for this row (ie, subsequent triggers
2104-
are not fired, and the INSERT/UPDATE/DELETE does not occur for this
2105-
row). If a non-NULL value is returned then the operation proceeds with
2106-
that row value. Note that returning a row value different from the
2107-
original value of NEW alters the row that will be inserted or updated.
2108-
It is possible to replace single values directly
2109-
in NEW and return that, or to build a complete new record/row to
2110-
return.
2106+
having exactly the structure of the table the trigger was fired
2107+
for. The return value of a BEFORE or AFTER STATEMENT level
2108+
trigger, or an AFTER ROW level trigger is ignored; it may as well
2109+
return NULL. However, any of these types of triggers can still
2110+
abort the entire trigger operation by raising an error.
21112111
</para>
21122112

21132113
<para>
2114-
The return value of a trigger fired AFTER is ignored; it may as well
2115-
always return a NULL value. But an AFTER trigger can still abort the
2116-
operation by raising an error.
2114+
ROW level triggers fired BEFORE may return NULL to signal the
2115+
trigger manager to skip the rest of the operation for this row
2116+
(ie, subsequent triggers are not fired, and the
2117+
INSERT/UPDATE/DELETE does not occur for this row). If a non-NULL
2118+
value is returned then the operation proceeds with that row value.
2119+
Note that returning a row value different from the original value
2120+
of NEW alters the row that will be inserted or updated. It is
2121+
possible to replace single values directly in NEW and return that,
2122+
or to build a complete new record/row to return.
21172123
</para>
21182124

21192125
<example>
@@ -2143,7 +2149,7 @@ CREATE FUNCTION emp_stamp () RETURNS TRIGGER AS '
21432149
RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
21442150
END IF;
21452151

2146-
-- Who works for us when she must pay for?
2152+
-- Who works for us when she must pay for it?
21472153
IF NEW.salary < 0 THEN
21482154
RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
21492155
END IF;

‎doc/src/sgml/ref/alter_trigger.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -153,8 +153,8 @@ ALTER TRIGGER emp_stamp ON emp RENAME TO emp_track_chgs;
153153
</refsect2info>
154154
<title>SQL92</title>
155155
<para>
156-
The clause to rename triggersis a
157-
<productname>PostgreSQL</productname>extensionfrom SQL92.
156+
<command>ALTER TRIGGER</command>is a <productname>PostgreSQL</>
157+
extensionof SQL92.
158158
</para>
159159
</refsect2>
160160
</refsect1>

‎doc/src/sgml/ref/create_trigger.sgml

Lines changed: 62 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_trigger.sgml,v 1.29 2002/11/21 23:34:43 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_trigger.sgml,v 1.30 2002/11/23 03:59:06 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -21,8 +21,9 @@ PostgreSQL documentation
2121
<date>2000-03-25</date>
2222
</refsynopsisdivinfo>
2323
<synopsis>
24-
CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTER } { <replaceable class="PARAMETER">event</replaceable> [OR ...] }
25-
ON <replaceable class="PARAMETER">table</replaceable> FOR EACH { ROW | STATEMENT }
24+
CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> {
25+
BEFORE | AFTER } { <replaceable class="PARAMETER">event</replaceable> [ OR ... ] }
26+
ON <replaceable class="PARAMETER">table</replaceable> [ FOR EACH { ROW | STATEMENT } ]
2627
EXECUTE PROCEDURE <replaceable class="PARAMETER">func</replaceable> ( <replaceable class="PARAMETER">arguments</replaceable> )
2728
</synopsis>
2829

@@ -45,22 +46,53 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
4546
</para>
4647
</listitem>
4748
</varlistentry>
49+
50+
<varlistentry>
51+
<term>BEFORE</term>
52+
<term>AFTER</term>
53+
<listitem>
54+
<para>
55+
Determines whether the function is called before or after the
56+
event.
57+
</para>
58+
</listitem>
59+
</varlistentry>
60+
4861
<varlistentry>
4962
<term><replaceable class="parameter">event</replaceable></term>
5063
<listitem>
5164
<para>
52-
One of INSERT, DELETE or UPDATE.
65+
One of <command>INSERT</command>, <command>DELETE</command> or
66+
<command>UPDATE</command>; this specifies the event that will
67+
fire the trigger. Multiple events can be specified using
68+
<literal>OR</literal>.
5369
</para>
5470
</listitem>
5571
</varlistentry>
5672
<varlistentry>
5773
<term><replaceable class="parameter">table</replaceable></term>
5874
<listitem>
5975
<para>
60-
The name (optionally schema-qualified) of the table the trigger is for.
76+
The name (optionally schema-qualified) of the table the
77+
trigger is for.
6178
</para>
6279
</listitem>
6380
</varlistentry>
81+
82+
<varlistentry>
83+
<term>FOR EACH ROW</term>
84+
<term>FOR EACH STATEMENT</term>
85+
86+
<listitem>
87+
<para>
88+
This specifies whether the trigger procedure should be fired
89+
once for every row affected by the trigger event, or just once
90+
per SQL statement. If neither is specified, <literal>FOR EACH
91+
STATEMENT</literal> is the default.
92+
</para>
93+
</listitem>
94+
</varlistentry>
95+
6496
<varlistentry>
6597
<term><replaceable class="parameter">func</replaceable></term>
6698
<listitem>
@@ -74,11 +106,15 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
74106
<term><replaceable class="parameter">arguments</replaceable></term>
75107
<listitem>
76108
<para>
77-
An optional comma-separated list of arguments to be provided to the
78-
function when the trigger is executed, along with the standard trigger
79-
data such as old and new tuple contents. The arguments are literal
80-
string constants. Simple names and numeric constants may be written
81-
here too, but they will all be converted to strings.
109+
An optional comma-separated list of arguments to be provided to
110+
the function when the trigger is executed, along with the standard
111+
trigger data such as old and new tuple contents. The arguments
112+
are literal string constants. Simple names and numeric constants
113+
may be written here too, but they will all be converted to
114+
strings. Note that these arguments are not provided as normal
115+
function parameters (since a trigger procedure must be declared to
116+
take zero parameters), but are instead accessed through the
117+
<literal>TG_ARGV</literal> array.
82118
</para>
83119
</listitem>
84120
</varlistentry>
@@ -121,7 +157,7 @@ CREATE TRIGGER
121157

122158
<para>
123159
<command>CREATE TRIGGER</command> will enter a new trigger into the current
124-
data base. The trigger will be associated with the relation
160+
database. The trigger will be associated with the relation
125161
<replaceable class="parameter">table</replaceable> and will execute
126162
the specified function <replaceable class="parameter">func</replaceable>.
127163
</para>
@@ -141,15 +177,27 @@ CREATE TRIGGER
141177
or deletion, are <quote>visible</quote> to the trigger.
142178
</para>
143179

180+
<para>
181+
A trigger that executes <literal>FOR EACH ROW</literal> of the
182+
specified operation is called once for every row that the operation
183+
modifies. For example, a <command>DELETE</command> that affects 10
184+
rows will cause any <literal>ON DELETE</literal> triggers on the
185+
target relation to be called 10 separate times, once for each
186+
deleted tuple. In contrast, a trigger that executes <literal>FOR
187+
EACH STATEMENT</literal> of the specified operation only executes
188+
once for any given operation, regardless of how many rows it
189+
modifies.
190+
</para>
191+
144192
<para>
145193
If multiple triggers of the same kind are defined for the same event,
146194
they will be fired in alphabetical order by name.
147195
</para>
148196

149197
<para>
150-
<command>SELECT</command> does not modify any rows so you can not
151-
create <command>SELECT</command> triggers. Rules and views are more
152-
appropriate in such cases.
198+
<command>SELECT</command> does not modify any rows so you can not
199+
create <command>SELECT</command> triggers. Rules and views are more
200+
appropriate in such cases.
153201
</para>
154202

155203
<para>
@@ -176,10 +224,6 @@ CREATE TRIGGER
176224
change the function's declared return type to <type>trigger</>.
177225
</para>
178226

179-
<para>
180-
As of the current release, <literal>STATEMENT</literal> triggers are not implemented.
181-
</para>
182-
183227
<para>
184228
Refer to the <xref linkend="sql-droptrigger" endterm="sql-droptrigger-title"> command for
185229
information on how to remove triggers.
@@ -268,13 +312,6 @@ CREATE TABLE distributors (
268312
</para>
269313
</listitem>
270314

271-
<listitem>
272-
<para>
273-
<productname>PostgreSQL</productname> only has row-level
274-
triggers, no statement-level triggers.
275-
</para>
276-
</listitem>
277-
278315
<listitem>
279316
<para>
280317
<productname>PostgreSQL</productname> only allows the

‎doc/src/sgml/release.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.166 2002/11/2302:41:03 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.167 2002/11/2303:59:06 momjian Exp $
33
-->
44

55
<appendix id="release">
@@ -4619,7 +4619,7 @@ Enhancements
46194619
* pg_dump now output the schema and/or the data, with many fixes to
46204620
enhance completeness.
46214621
* psql used in place of monitor in administration shell scripts.
4622-
monitor to bedepreciated in next release.
4622+
monitor to bedeprecated in next release.
46234623
* date/time functions enhanced
46244624
* NULL insert/update/comparison fixed/enhanced
46254625
* TCL/TK lib and shell fixed to work with both tck7.4/tk4.0 and tcl7.5/tk4.1

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp