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

Commitb2734a0

Browse files
committed
Support SQL-compliant triggers on columns, ie fire only if certain columns
are named in the UPDATE's SET list.Note: the schema of pg_trigger has not actually changed; we've just startedto use a column that was there all along. catversion bumped anyway so thatthis commit is included in the history of potentially interesting changesto system catalog contents.Itagaki Takahiro
1 parentbe922e8 commitb2734a0

File tree

17 files changed

+433
-183
lines changed

17 files changed

+433
-183
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 12 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.209 2009/10/07 22:14:14 alvherre Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.210 2009/10/14 22:14:21 tgl Exp $ -->
22
<!--
33
Documentation of the system catalogs, directed toward PostgreSQL developers
44
-->
@@ -4745,8 +4745,9 @@
47454745
<row>
47464746
<entry><structfield>tgattr</structfield></entry>
47474747
<entry><type>int2vector</type></entry>
4748-
<entry></entry>
4749-
<entry>Currently unused</entry>
4748+
<entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</literal></entry>
4749+
<entry>column numbers, if trigger is column-specific; otherwise an
4750+
empty array</entry>
47504751
</row>
47514752

47524753
<row>
@@ -4759,6 +4760,14 @@
47594760
</tgroup>
47604761
</table>
47614762

4763+
<para>
4764+
Currently, column-specific triggering is supported only for
4765+
<literal>UPDATE</> events, and so <structfield>tgattr</> is relevant
4766+
only for that event type. <structfield>tgtype</structfield> might
4767+
contain bits for other event types as well, but those are presumed
4768+
to be table-wide regardless of what is in <structfield>tgattr</>.
4769+
</para>
4770+
47624771
<note>
47634772
<para>
47644773
When <structfield>tgconstraint</> is nonzero,

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

Lines changed: 30 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_trigger.sgml,v 1.50 2009/09/19 10:23:27 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_trigger.sgml,v 1.51 2009/10/14 22:14:21 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -49,7 +49,7 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
4949
current row, or change the row being inserted (for
5050
<command>INSERT</command> and <command>UPDATE</command> operations
5151
only). If the trigger fires after the event, all changes, including
52-
thelast insertion, update, or deletion, are <quote>visible</quote>
52+
theeffects of other triggers, are <quote>visible</quote>
5353
to the trigger.
5454
</para>
5555

@@ -122,6 +122,16 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
122122
this specifies the event that will fire the trigger. Multiple
123123
events can be specified using <literal>OR</literal>.
124124
</para>
125+
126+
<para>
127+
For <command>UPDATE</command> triggers, it is possible to
128+
specify a list of columns using this syntax:
129+
<synopsis>
130+
UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</replaceable> ... ]
131+
</synopsis>
132+
The trigger will only fire if at least one of the listed columns
133+
is mentioned as a target of the update.
134+
</para>
125135
</listitem>
126136
</varlistentry>
127137

@@ -169,8 +179,8 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
169179
literal string constants. Simple names and numeric constants
170180
can be written here, too, but they will all be converted to
171181
strings. Please check the description of the implementation
172-
language of the trigger functionabout how the trigger arguments
173-
are accessible within the function; it might be different from
182+
language of the trigger functionto find out how these arguments
183+
can be accessed within the function; it might be different from
174184
normal function arguments.
175185
</para>
176186
</listitem>
@@ -191,6 +201,18 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
191201
endterm="sql-droptrigger-title"> to remove a trigger.
192202
</para>
193203

204+
<para>
205+
A column-specific trigger (<literal>FOR UPDATE OF
206+
<replaceable>column_name</replaceable></literal>) will fire when any
207+
of its columns are listed as targets in the <command>UPDATE</>
208+
command's <literal>SET</> list. It is possible for a column's value
209+
to change even when the trigger is not fired, because changes made to the
210+
row's contents by <literal>BEFORE UPDATE</> triggers are not considered.
211+
Conversely, a command such as <literal>UPDATE ... SET x = x ...</>
212+
will fire a trigger on column <literal>x</>, even though the column's
213+
value did not change.
214+
</para>
215+
194216
<para>
195217
In <productname>PostgreSQL</productname> versions before 7.3, it was
196218
necessary to declare trigger functions as returning the placeholder
@@ -218,13 +240,6 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
218240
<acronym>SQL</> standard. The following functionality is currently missing:
219241

220242
<itemizedlist>
221-
<listitem>
222-
<para>
223-
SQL allows triggers to fire on updates to specific columns
224-
(e.g., <literal>AFTER UPDATE OF col1, col2</literal>).
225-
</para>
226-
</listitem>
227-
228243
<listitem>
229244
<para>
230245
SQL allows you to define aliases for the <quote>old</quote>
@@ -264,9 +279,10 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
264279
The <productname>PostgreSQL</productname> behavior is for <literal>BEFORE
265280
DELETE</literal> to always fire before the delete action, even a cascading
266281
one. This is considered more consistent. There is also unpredictable
267-
behavior when <literal>BEFORE</literal> triggers modify rows that are later
268-
to be modified by referential actions. This can lead to constraint violations
269-
or stored data that does not honor the referential constraint.
282+
behavior when <literal>BEFORE</literal> triggers modify rows or prevent
283+
updates during an update that is caused by a referential action. This can
284+
lead to constraint violations or stored data that does not honor the
285+
referential constraint.
270286
</para>
271287

272288
<para>

‎doc/src/sgml/trigger.sgml

Lines changed: 8 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.58 2009/08/04 22:04:37 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.59 2009/10/14 22:14:21 tgl Exp $ -->
22

33
<chapter id="triggers">
44
<title>Triggers</title>
@@ -36,10 +36,13 @@
3636
performed. Triggers can be defined to execute either before or after any
3737
<command>INSERT</command>, <command>UPDATE</command>, or
3838
<command>DELETE</command> operation, either once per modified row,
39-
or once per <acronym>SQL</acronym> statement. Triggers can also fire
40-
for <command>TRUNCATE</command> statements. If a trigger event occurs,
41-
the trigger's function is called at the appropriate time to handle the
42-
event.
39+
or once per <acronym>SQL</acronym> statement.
40+
<command>UPDATE</command> triggers can moreover be set to fire only if
41+
certain columns are mentioned in the <literal>SET</literal> clause of the
42+
<command>UPDATE</command> statement.
43+
Triggers can also fire for <command>TRUNCATE</command> statements.
44+
If a trigger event occurs, the trigger's function is called at the
45+
appropriate time to handle the event.
4346
</para>
4447

4548
<para>

‎src/backend/catalog/index.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.322 2009/10/05 19:24:35 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.323 2009/10/14 22:14:21 tgl Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -792,6 +792,7 @@ index_create(Oid heapRelationId,
792792
trigger->before= false;
793793
trigger->row= true;
794794
trigger->events=TRIGGER_TYPE_INSERT |TRIGGER_TYPE_UPDATE;
795+
trigger->columns=NIL;
795796
trigger->isconstraint= true;
796797
trigger->deferrable= true;
797798
trigger->initdeferred=initdeferred;

‎src/backend/commands/tablecmds.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.303 2009/10/13 00:53:07 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.304 2009/10/14 22:14:21 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -5441,6 +5441,7 @@ CreateFKCheckTrigger(RangeVar *myRel, Constraint *fkconstraint,
54415441
fk_trigger->events=TRIGGER_TYPE_UPDATE;
54425442
}
54435443

5444+
fk_trigger->columns=NIL;
54445445
fk_trigger->isconstraint= true;
54455446
fk_trigger->deferrable=fkconstraint->deferrable;
54465447
fk_trigger->initdeferred=fkconstraint->initdeferred;
@@ -5491,6 +5492,7 @@ createForeignKeyTriggers(Relation rel, Constraint *fkconstraint,
54915492
fk_trigger->before= false;
54925493
fk_trigger->row= true;
54935494
fk_trigger->events=TRIGGER_TYPE_DELETE;
5495+
fk_trigger->columns=NIL;
54945496
fk_trigger->isconstraint= true;
54955497
fk_trigger->constrrel=myRel;
54965498
switch (fkconstraint->fk_del_action)
@@ -5543,6 +5545,7 @@ createForeignKeyTriggers(Relation rel, Constraint *fkconstraint,
55435545
fk_trigger->before= false;
55445546
fk_trigger->row= true;
55455547
fk_trigger->events=TRIGGER_TYPE_UPDATE;
5548+
fk_trigger->columns=NIL;
55465549
fk_trigger->isconstraint= true;
55475550
fk_trigger->constrrel=myRel;
55485551
switch (fkconstraint->fk_upd_action)

0 commit comments

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