|
1 | 1 | <!-- |
2 | | -$PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.33 2003/11/29 19:51:38 pgsql Exp $ |
| 2 | +$PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.34 2004/01/22 19:50:21 neilc Exp $ |
3 | 3 | --> |
4 | 4 |
|
5 | 5 | <chapter id="triggers"> |
@@ -45,50 +45,69 @@ $PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.33 2003/11/29 19:51:38 pgsql Ex |
45 | 45 | </para> |
46 | 46 |
|
47 | 47 | <para> |
48 | | - Trigger functions return a table row (a value of type |
49 | | - <structname>HeapTuple</>) to the calling executor. |
50 | | - A trigger fired before an operation has the following choices: |
| 48 | + There are two types of triggers: per-row triggers and |
| 49 | + per-statement triggers. In a per-row trigger, the trigger function |
| 50 | + is invoked once for every row that is affected by the statement |
| 51 | + that fired the trigger. In contrast, a per-statement trigger is |
| 52 | + invoked only once when an appropriate statement is executed, |
| 53 | + regardless of the number of rows affected by that statement. In |
| 54 | + particular, a statement that affects zero rows will still result |
| 55 | + in the execution of any applicable per-statement triggers. These |
| 56 | + two types of triggers are sometimes called <quote>row-level |
| 57 | + triggers</quote> and <quote>statement-level triggers</quote>, |
| 58 | + respectively. |
| 59 | + </para> |
| 60 | + |
| 61 | + <para> |
| 62 | + Trigger functions invoked by per-statement triggers should always |
| 63 | + return <symbol>NULL</symbol>. Trigger functions invoked by per-row |
| 64 | + triggers can return a table row (a value of |
| 65 | + type <structname>HeapTuple</structname>) to the calling executor, |
| 66 | + if they choose. A row-level trigger fired before an operation has |
| 67 | + the following choices: |
51 | 68 |
|
52 | 69 | <itemizedlist> |
53 | 70 | <listitem> |
54 | 71 | <para> |
55 | | - It can return a <symbol>NULL</> pointer to skip the operation |
56 | | - for the current row (and so the row will not be |
57 | | - inserted/updated/deleted). |
| 72 | + It can return <symbol>NULL</> to skip the operation for the |
| 73 | + current row. This instructs the executor to not perform the |
| 74 | + row-level operation that invoked the trigger (the insertion or |
| 75 | + modification of a particular table row). |
58 | 76 | </para> |
59 | 77 | </listitem> |
60 | 78 |
|
61 | 79 | <listitem> |
62 | 80 | <para> |
63 | | - For<command>INSERT</command> and<command>UPDATE</command> |
64 | | - triggers only, the returned row becomes the row that will |
65 | | - be inserted or will replace the row being updated. This |
66 | | - allows the trigger function to modify the row being inserted or |
67 | | - updated. |
| 81 | + Forrow-level<command>INSERT</command> |
| 82 | +and <command>UPDATE</command>triggers only, the returned row |
| 83 | +becomes the row that willbe inserted or will replace the row |
| 84 | +being updated. Thisallows the trigger function to modify the |
| 85 | +row being inserted orupdated. |
68 | 86 | </para> |
69 | 87 | </listitem> |
70 | 88 | </itemizedlist> |
71 | 89 |
|
72 | | - A before trigger that does not intend to cause either of these |
73 | | - behaviors must be careful to return as its result the same row that was |
74 | | - passed in (that is, the NEW row for <command>INSERT</command> and |
75 | | - <command>UPDATE</command> triggers, the OLD row for |
| 90 | + A row-level before trigger that does not intend to cause either of |
| 91 | + these behaviors must be careful to return as its result the same |
| 92 | + row that was passed in (that is, the <varname>NEW</varname> row |
| 93 | + for <command>INSERT</command> and <command>UPDATE</command> |
| 94 | + triggers, the <varname>OLD</varname> row for |
76 | 95 | <command>DELETE</command> triggers). |
77 | 96 | </para> |
78 | 97 |
|
79 | 98 | <para> |
80 | | - The return |
81 | | - value is ignored for triggers fired after an operation, and so |
82 | | - they may as well return <symbol>NULL</>. |
| 99 | + The return value is ignored for row-level triggers fired after an |
| 100 | + operation, and so they may as well return <symbol>NULL</>. |
83 | 101 | </para> |
84 | 102 |
|
85 | 103 | <para> |
86 | 104 | If more than one trigger is defined for the same event on the same |
87 | | - relation, the triggers will be fired in alphabetical order by trigger |
88 | | - name. In the case of before triggers, the possibly-modified row |
89 | | - returned by each trigger becomes the input to the next trigger. |
90 | | - If any before trigger returns a <symbol>NULL</> pointer, the |
91 | | - operation is abandoned and subsequent triggers are not fired. |
| 105 | + relation, the triggers will be fired in alphabetical order by |
| 106 | + trigger name. In the case of before triggers, the |
| 107 | + possibly-modified row returned by each trigger becomes the input |
| 108 | + to the next trigger. If any before trigger returns |
| 109 | + <symbol>NULL</>, the operation is abandoned and subsequent |
| 110 | + triggers are not fired. |
92 | 111 | </para> |
93 | 112 |
|
94 | 113 | <para> |
@@ -134,30 +153,41 @@ $PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.33 2003/11/29 19:51:38 pgsql Ex |
134 | 153 | is fired for. Briefly: |
135 | 154 |
|
136 | 155 | <itemizedlist> |
| 156 | + |
| 157 | + <listitem> |
| 158 | + <para> |
| 159 | + Statement-level triggers follow simple visibility rules: none of |
| 160 | + the changes made by a statement are visible to statement-level |
| 161 | + triggers that are invoked before the statement, whereas all |
| 162 | + modifications are visible to statement-level after triggers. |
| 163 | + </para> |
| 164 | + </listitem> |
| 165 | + |
137 | 166 | <listitem> |
138 | 167 | <para> |
139 | | - The data change (insertion, update, or deletion) causing the trigger |
140 | | - to fire is naturally |
141 | | -<emphasis>not</emphasis> visibleto SQL commands executed in a |
142 | | -before trigger, becauseit hasn't happened yet. |
| 168 | + The data change (insertion, update, or deletion) causing the |
| 169 | +triggerto fire is naturally <emphasis>not</emphasis> visible |
| 170 | + to SQL commands executed in a row-level before trigger, because |
| 171 | + it hasn't happened yet. |
143 | 172 | </para> |
144 | 173 | </listitem> |
145 | 174 |
|
146 | 175 | <listitem> |
147 | 176 | <para> |
148 | | - However, SQL commands executed in abefore trigger |
149 | | - <emphasis>will</emphasis> see the effects of data changes |
150 | | - for rows previously processed in the same outer command. This |
151 | | - requires caution, since the ordering of these change events |
152 | | - is not in general predictable; a SQL command that affects |
153 | | - multiple rows may visit the rows in any order. |
| 177 | + However, SQL commands executed in arow-level before |
| 178 | +trigger<emphasis>will</emphasis> see the effects of data |
| 179 | +changesfor rows previously processed in the same outer |
| 180 | +command. Thisrequires caution, since the ordering of these |
| 181 | +change eventsis not in general predictable; a SQL command that |
| 182 | +affectsmultiple rows may visit the rows in any order. |
154 | 183 | </para> |
155 | 184 | </listitem> |
156 | 185 |
|
157 | 186 | <listitem> |
158 | 187 | <para> |
159 | | - When an after trigger is fired, all data changes made by the outer |
160 | | - command are already complete, and are visible to executed SQL commands. |
| 188 | + When a row-level after trigger is fired, all data changes made |
| 189 | + by the outer command are already complete, and are visible to |
| 190 | + the invoked trigger function. |
161 | 191 | </para> |
162 | 192 | </listitem> |
163 | 193 | </itemizedlist> |
|
