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

Commit7692d8d

Browse files
committed
Support statement-level ON TRUNCATE triggers. Simon Riggs
1 parent107b3d0 commit7692d8d

File tree

23 files changed

+454
-83
lines changed

23 files changed

+454
-83
lines changed

‎doc/src/sgml/plperl.sgml

Lines changed: 12 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.67 2008/01/25 15:28:35 adunstan Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.68 2008/03/28 00:21:55 tgl Exp $ -->
22

33
<chapter id="plperl">
44
<title>PL/Perl - Perl Procedural Language</title>
@@ -17,12 +17,14 @@
1717
<ulink url="http://www.perl.com">Perl programming language</ulink>.
1818
</para>
1919

20-
<para> The usual advantage to using PL/Perl is that this allows use,
20+
<para>
21+
The main advantage to using PL/Perl is that this allows use,
2122
within stored functions, of the manyfold <quote>string
22-
munging</quote> operators and functions available for Perl. Parsing
23+
munging</quote> operators and functions available for Perl. Parsing
2324
complex strings might be easier using Perl than it is with the
24-
string functions and control structures provided in PL/pgSQL.</para>
25-
25+
string functions and control structures provided in PL/pgSQL.
26+
</para>
27+
2628
<para>
2729
To install PL/Perl in a particular database, use
2830
<literal>createlang plperl <replaceable>dbname</></literal>.
@@ -739,7 +741,8 @@ $$ LANGUAGE plperl;
739741
<term><literal>$_TD-&gt;{event}</literal></term>
740742
<listitem>
741743
<para>
742-
Trigger event: <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>, or <literal>UNKNOWN</>
744+
Trigger event: <literal>INSERT</>, <literal>UPDATE</>,
745+
<literal>DELETE</>, <literal>TRUNCATE</>, or <literal>UNKNOWN</>
743746
</para>
744747
</listitem>
745748
</varlistentry>
@@ -822,14 +825,14 @@ $$ LANGUAGE plperl;
822825
</para>
823826

824827
<para>
825-
Triggers can return one of the following:
828+
Row-level triggers can return one of the following:
826829

827830
<variablelist>
828831
<varlistentry>
829832
<term><literal>return;</literal></term>
830833
<listitem>
831834
<para>
832-
Execute thestatement
835+
Execute theoperation
833836
</para>
834837
</listitem>
835838
</varlistentry>
@@ -838,7 +841,7 @@ $$ LANGUAGE plperl;
838841
<term><literal>"SKIP"</literal></term>
839842
<listitem>
840843
<para>
841-
Don't execute thestatement
844+
Don't execute theoperation
842845
</para>
843846
</listitem>
844847
</varlistentry>

‎doc/src/sgml/plpgsql.sgml

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.124 2008/03/23 00:24:19 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.125 2008/03/28 00:21:55 tgl Exp $ -->
22

33
<chapter id="plpgsql">
44
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -2785,9 +2785,9 @@ RAISE EXCEPTION 'Nonexistent ID --> %', user_id;
27852785
<listitem>
27862786
<para>
27872787
Data type <type>text</type>; a string of
2788-
<literal>INSERT</literal>, <literal>UPDATE</literal>, or
2789-
<literal>DELETE</literal> telling for which operation the
2790-
trigger was fired.
2788+
<literal>INSERT</literal>, <literal>UPDATE</literal>,
2789+
<literal>DELETE</literal>, or <literal>TRUNCATE</>
2790+
telling for which operation thetrigger was fired.
27912791
</para>
27922792
</listitem>
27932793
</varlistentry>

‎doc/src/sgml/plpython.sgml

Lines changed: 14 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpython.sgml,v 1.38 2007/02/01 00:28:17 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpython.sgml,v 1.39 2008/03/28 00:21:55 tgl Exp $ -->
22

33
<chapter id="plpython">
44
<title>PL/Python - Python Procedural Language</title>
@@ -381,31 +381,34 @@ $$ LANGUAGE plpythonu;
381381

382382
<para>
383383
When a function is used as a trigger, the dictionary
384-
<literal>TD</literal> contains trigger-related values. The trigger
385-
rows are in <literal>TD["new"]</> and/or <literal>TD["old"]</>
386-
depending on the trigger event. <literal>TD["event"]</> contains
384+
<literal>TD</literal> contains trigger-related values.
385+
<literal>TD["event"]</> contains
387386
the event as a string (<literal>INSERT</>, <literal>UPDATE</>,
388-
<literal>DELETE</>, or <literal>UNKNOWN</>).
387+
<literal>DELETE</>,<literal>TRUNCATE</>,or <literal>UNKNOWN</>).
389388
<literal>TD["when"]</> contains one of <literal>BEFORE</>,
390-
<literal>AFTER</>,and <literal>UNKNOWN</>.
389+
<literal>AFTER</>,or <literal>UNKNOWN</>.
391390
<literal>TD["level"]</> contains one of <literal>ROW</>,
392-
<literal>STATEMENT</>, and <literal>UNKNOWN</>.
391+
<literal>STATEMENT</>, or <literal>UNKNOWN</>.
392+
For a row-level trigger, the trigger
393+
rows are in <literal>TD["new"]</> and/or <literal>TD["old"]</>
394+
depending on the trigger event.
393395
<literal>TD["name"]</> contains the trigger name,
394396
<literal>TD["table_name"]</> contains the name of the table on which the trigger occurred,
395397
<literal>TD["table_schema"]</> contains the schema of the table on which the trigger occurred,
396-
<literal>TD["name"]</> contains the trigger name, and
397-
<literal>TD["relid"]</> contains the OID of the table on
398+
and <literal>TD["relid"]</> contains the OID of the table on
398399
which the trigger occurred. If the <command>CREATE TRIGGER</> command
399400
included arguments, they are available in <literal>TD["args"][0]</> to
400-
<literal>TD["args"][(<replaceable>n</>-1)]</>.
401+
<literal>TD["args"][<replaceable>n</>-1]</>.
401402
</para>
402403

403404
<para>
404-
If <literal>TD["when"]</literal> is <literal>BEFORE</>, you can
405+
If <literal>TD["when"]</literal> is <literal>BEFORE</> and
406+
<literal>TD["level"]</literal> is <literal>ROW</>, you can
405407
return <literal>None</literal> or <literal>"OK"</literal> from the
406408
Python function to indicate the row is unmodified,
407409
<literal>"SKIP"</> to abort the event, or <literal>"MODIFY"</> to
408410
indicate you've modified the row.
411+
Otherwise the return value is ignored.
409412
</para>
410413
</sect1>
411414

‎doc/src/sgml/pltcl.sgml

Lines changed: 9 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/pltcl.sgml,v 2.47 2007/12/03 23:49:50 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/pltcl.sgml,v 2.48 2008/03/28 00:21:55 tgl Exp $ -->
22

33
<chapter id="pltcl">
44
<title>PL/Tcl - Tcl Procedural Language</title>
@@ -569,7 +569,7 @@ SELECT 'doesn''t' AS ret
569569
<listitem>
570570
<para>
571571
The string <literal>BEFORE</> or <literal>AFTER</> depending on the
572-
type of triggercall.
572+
type of triggerevent.
573573
</para>
574574
</listitem>
575575
</varlistentry>
@@ -579,7 +579,7 @@ SELECT 'doesn''t' AS ret
579579
<listitem>
580580
<para>
581581
The string <literal>ROW</> or <literal>STATEMENT</> depending on the
582-
type of triggercall.
582+
type of triggerevent.
583583
</para>
584584
</listitem>
585585
</varlistentry>
@@ -588,8 +588,9 @@ SELECT 'doesn''t' AS ret
588588
<term><varname>$TG_op</varname></term>
589589
<listitem>
590590
<para>
591-
The string <literal>INSERT</>, <literal>UPDATE</>, or
592-
<literal>DELETE</> depending on the type of trigger call.
591+
The string <literal>INSERT</>, <literal>UPDATE</>,
592+
<literal>DELETE</>, or <literal>TRUNCATE</> depending on the type of
593+
trigger event.
593594
</para>
594595
</listitem>
595596
</varlistentry>
@@ -602,6 +603,7 @@ SELECT 'doesn''t' AS ret
602603
row for <command>INSERT</> or <command>UPDATE</> actions, or
603604
empty for <command>DELETE</>. The array is indexed by column
604605
name. Columns that are null will not appear in the array.
606+
This is not set for statement-level triggers.
605607
</para>
606608
</listitem>
607609
</varlistentry>
@@ -614,6 +616,7 @@ SELECT 'doesn''t' AS ret
614616
row for <command>UPDATE</> or <command>DELETE</> actions, or
615617
empty for <command>INSERT</>. The array is indexed by column
616618
name. Columns that are null will not appear in the array.
619+
This is not set for statement-level triggers.
617620
</para>
618621
</listitem>
619622
</varlistentry>
@@ -644,6 +647,7 @@ SELECT 'doesn''t' AS ret
644647
only.) Needless to say that all this is only meaningful when the trigger
645648
is <literal>BEFORE</> and <command>FOR EACH ROW</>; otherwise the return value is ignored.
646649
</para>
650+
647651
<para>
648652
Here's a little example trigger procedure that forces an integer value
649653
in a table to keep track of the number of updates that are performed on the

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

Lines changed: 25 additions & 13 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.47 2007/02/01 19:10:24 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_trigger.sgml,v 1.48 2008/03/28 00:21:55 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -25,7 +25,7 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
2525
EXECUTE PROCEDURE <replaceable class="PARAMETER">funcname</replaceable> ( <replaceable class="PARAMETER">arguments</replaceable> )
2626
</synopsis>
2727
</refsynopsisdiv>
28-
28+
2929
<refsect1>
3030
<title>Description</title>
3131

@@ -65,6 +65,12 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
6565
EACH STATEMENT</literal> triggers).
6666
</para>
6767

68+
<para>
69+
In addition, triggers may be defined to fire for a
70+
<command>TRUNCATE</command>, though only
71+
<literal>FOR EACH STATEMENT</literal>.
72+
</para>
73+
6874
<para>
6975
If multiple triggers of the same kind are defined for the same event,
7076
they will be fired in alphabetical order by name.
@@ -80,7 +86,7 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
8086
Refer to <xref linkend="triggers"> for more information about triggers.
8187
</para>
8288
</refsect1>
83-
89+
8490
<refsect1>
8591
<title>Parameters</title>
8692

@@ -110,10 +116,10 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
110116
<term><replaceable class="parameter">event</replaceable></term>
111117
<listitem>
112118
<para>
113-
One of <command>INSERT</command>, <command>UPDATE</command>, or
114-
<command>DELETE</command>; this specifies the event that will
115-
firethetrigger. Multiple events can be specified using
116-
<literal>OR</literal>.
119+
One of <command>INSERT</command>, <command>UPDATE</command>,
120+
<command>DELETE</command>, or <command>TRUNCATE</command>;
121+
this specifiestheevent that will fire the trigger. Multiple
122+
events can be specified using<literal>OR</literal>.
117123
</para>
118124
</listitem>
119125
</varlistentry>
@@ -179,6 +185,11 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
179185
<literal>TRIGGER</literal> privilege on the table.
180186
</para>
181187

188+
<para>
189+
Use <xref linkend="sql-droptrigger"
190+
endterm="sql-droptrigger-title"> to remove a trigger.
191+
</para>
192+
182193
<para>
183194
In <productname>PostgreSQL</productname> versions before 7.3, it was
184195
necessary to declare trigger functions as returning the placeholder
@@ -187,11 +198,6 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
187198
declared as returning <type>opaque</>, but it will issue a notice and
188199
change the function's declared return type to <type>trigger</>.
189200
</para>
190-
191-
<para>
192-
Use <xref linkend="sql-droptrigger"
193-
endterm="sql-droptrigger-title"> to remove a trigger.
194-
</para>
195201
</refsect1>
196202

197203
<refsect1 id="R1-SQL-CREATETRIGGER-2">
@@ -204,7 +210,7 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
204210

205211
<refsect1 id="SQL-CREATETRIGGER-compatibility">
206212
<title>Compatibility</title>
207-
213+
208214
<para>
209215
The <command>CREATE TRIGGER</command> statement in
210216
<productname>PostgreSQL</productname> implements a subset of the
@@ -267,6 +273,12 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
267273
<literal>OR</literal> is a <productname>PostgreSQL</> extension of
268274
the SQL standard.
269275
</para>
276+
277+
<para>
278+
The ability to fire triggers for <command>TRUNCATE</command> is a
279+
<productname>PostgreSQL</> extension of the SQL standard.
280+
</para>
281+
270282
</refsect1>
271283

272284
<refsect1>

‎doc/src/sgml/ref/truncate.sgml

Lines changed: 12 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/truncate.sgml,v 1.24 2007/05/11 19:40:08 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/truncate.sgml,v 1.25 2008/03/28 00:21:55 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -36,7 +36,7 @@ TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ C
3636
operation. This is most useful on large tables.
3737
</para>
3838
</refsect1>
39-
39+
4040
<refsect1>
4141
<title>Parameters</title>
4242

@@ -91,8 +91,16 @@ TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ C
9191
</para>
9292

9393
<para>
94-
<command>TRUNCATE</> will not run any <literal>ON DELETE</literal>
95-
triggers that might exist for the tables.
94+
<command>TRUNCATE</> will not fire any <literal>ON DELETE</literal>
95+
triggers that might exist for the tables. But it will fire
96+
<literal>ON TRUNCATE</literal> triggers.
97+
If <literal>ON TRUNCATE</> triggers are defined for any of
98+
the tables, then all <literal>BEFORE TRUNCATE</literal> triggers are
99+
fired before any truncation happens, and all <literal>AFTER
100+
TRUNCATE</literal> triggers are fired after the last truncation is
101+
performed. The triggers will fire in the order that the tables are
102+
to be processed (first those listed in the command, and then any
103+
that were added due to cascading).
96104
</para>
97105

98106
<warning>

‎doc/src/sgml/trigger.sgml

Lines changed: 19 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.51 2007/12/03 23:49:51 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.52 2008/03/28 00:21:55 tgl Exp $ -->
22

33
<chapter id="triggers">
44
<title>Triggers</title>
@@ -36,14 +36,15 @@
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.
40-
If a trigger event occurs, the trigger's function is called
41-
at the appropriate time to handle the event.
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.
4243
</para>
4344

4445
<para>
4546
The trigger function must be defined before the trigger itself can be
46-
created. The trigger function must be declared as a
47+
created. The trigger function must be declared as a
4748
function taking no arguments and returning type <literal>trigger</>.
4849
(The trigger function receives its input through a specially-passed
4950
<structname>TriggerData</> structure, not in the form of ordinary function
@@ -69,7 +70,8 @@
6970
in the execution of any applicable per-statement triggers. These
7071
two types of triggers are sometimes called <firstterm>row-level</>
7172
triggers and <firstterm>statement-level</> triggers,
72-
respectively.
73+
respectively. Triggers on <command>TRUNCATE</command> may only be
74+
defined at statement-level.
7375
</para>
7476

7577
<para>
@@ -398,6 +400,15 @@ typedef struct TriggerData
398400
</para>
399401
</listitem>
400402
</varlistentry>
403+
404+
<varlistentry>
405+
<term><literal>TRIGGER_FIRED_BY_TRUNCATE(tg_event)</literal></term>
406+
<listitem>
407+
<para>
408+
Returns true if the trigger was fired by a <command>TRUNCATE</command> command.
409+
</para>
410+
</listitem>
411+
</varlistentry>
401412
</variablelist>
402413
</para>
403414
</listitem>
@@ -630,10 +641,10 @@ CREATE FUNCTION trigf() RETURNS trigger
630641
AS '<replaceable>filename</>'
631642
LANGUAGE C;
632643

633-
CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest
644+
CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest
634645
FOR EACH ROW EXECUTE PROCEDURE trigf();
635646

636-
CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest
647+
CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest
637648
FOR EACH ROW EXECUTE PROCEDURE trigf();
638649
</programlisting>
639650
</para>

0 commit comments

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