| 1 |
<!-- doc/src/sgml/trigger.sgml --> |
| 2 |
|
| 3 |
<chapter id="triggers"> |
| 4 |
<title>Triggers</title> |
| 5 |
|
| 6 |
<indexterm zone="triggers"> |
| 7 |
<primary>trigger</primary> |
| 8 |
</indexterm> |
| 9 |
|
| 10 |
<para> |
| 11 |
This chapter provides general information about writing trigger functions. |
| 12 |
Trigger functions can be written in most of the available procedural |
| 13 |
languages, including |
| 14 |
<application>PL/pgSQL</application> (<xref linkend="plpgsql"/>), |
| 15 |
<application>PL/Tcl</application> (<xref linkend="pltcl"/>), |
| 16 |
<application>PL/Perl</application> (<xref linkend="plperl"/>), and |
| 17 |
<application>PL/Python</application> (<xref linkend="plpython"/>). |
| 18 |
After reading this chapter, you should consult the chapter for |
| 19 |
your favorite procedural language to find out the language-specific |
| 20 |
details of writing a trigger in it. |
| 21 |
</para> |
| 22 |
|
| 23 |
<para> |
| 24 |
It is also possible to write a trigger function in C, although |
| 25 |
most people find it easier to use one of the procedural languages. |
| 26 |
It is not currently possible to write a trigger function in the |
| 27 |
plain SQL function language. |
| 28 |
</para> |
| 29 |
|
| 30 |
<sect1 id="trigger-definition"> |
| 31 |
<title>Overview of Trigger Behavior</title> |
| 32 |
|
| 33 |
<para> |
| 34 |
A trigger is a specification that the database should automatically |
| 35 |
execute a particular function whenever a certain type of operation is |
| 36 |
performed. Triggers can be attached to tables (partitioned or not), |
| 37 |
views, and foreign tables. |
| 38 |
</para> |
| 39 |
|
| 40 |
<para> |
| 41 |
On tables and foreign tables, triggers can be defined to execute either |
| 42 |
before or after any <command>INSERT</command>, <command>UPDATE</command>, |
| 43 |
or <command>DELETE</command> operation, either once per modified row, |
| 44 |
or once per <acronym>SQL</acronym> statement. |
| 45 |
<command>UPDATE</command> triggers can moreover be set to fire only if |
| 46 |
certain columns are mentioned in the <literal>SET</literal> clause of |
| 47 |
the <command>UPDATE</command> statement. Triggers can also fire |
| 48 |
for <command>TRUNCATE</command> statements. If a trigger event occurs, |
| 49 |
the trigger's function is called at the appropriate time to handle the |
| 50 |
event. |
| 51 |
</para> |
| 52 |
|
| 53 |
<para> |
| 54 |
On views, triggers can be defined to execute instead of |
| 55 |
<command>INSERT</command>, <command>UPDATE</command>, or |
| 56 |
<command>DELETE</command> operations. |
| 57 |
Such <literal>INSTEAD OF</literal> triggers |
| 58 |
are fired once for each row that needs to be modified in the view. |
| 59 |
It is the responsibility of the |
| 60 |
trigger's function to perform the necessary modifications to the view's |
| 61 |
underlying base table(s) and, where appropriate, return the modified |
| 62 |
row as it will appear in the view. Triggers on views can also be defined |
| 63 |
to execute once per <acronym>SQL</acronym> statement, before or after |
| 64 |
<command>INSERT</command>, <command>UPDATE</command>, or |
| 65 |
<command>DELETE</command> operations. |
| 66 |
However, such triggers are fired only if there is also |
| 67 |
an <literal>INSTEAD OF</literal> trigger on the view. Otherwise, |
| 68 |
any statement targeting the view must be rewritten into a statement |
| 69 |
affecting its underlying base table(s), and then the triggers |
| 70 |
that will be fired are the ones attached to the base table(s). |
| 71 |
</para> |
| 72 |
|
| 73 |
<para> |
| 74 |
The trigger function must be defined before the trigger itself can be |
| 75 |
created. The trigger function must be declared as a |
| 76 |
function taking no arguments and returning type <literal>trigger</literal>. |
| 77 |
(The trigger function receives its input through a specially-passed |
| 78 |
<structname>TriggerData</structname> structure, not in the form of ordinary function |
| 79 |
arguments.) |
| 80 |
</para> |
| 81 |
|
| 82 |
<para> |
| 83 |
Once a suitable trigger function has been created, the trigger is |
| 84 |
established with |
| 85 |
<xref linkend="sql-createtrigger"/>. |
| 86 |
The same trigger function can be used for multiple triggers. |
| 87 |
</para> |
| 88 |
|
| 89 |
<para> |
| 90 |
<productname>PostgreSQL</productname> offers both <firstterm>per-row</firstterm> |
| 91 |
triggers and <firstterm>per-statement</firstterm> triggers. With a per-row |
| 92 |
trigger, the trigger function |
| 93 |
is invoked once for each row that is affected by the statement |
| 94 |
that fired the trigger. In contrast, a per-statement trigger is |
| 95 |
invoked only once when an appropriate statement is executed, |
| 96 |
regardless of the number of rows affected by that statement. In |
| 97 |
particular, a statement that affects zero rows will still result |
| 98 |
in the execution of any applicable per-statement triggers. These |
| 99 |
two types of triggers are sometimes called <firstterm>row-level</firstterm> |
| 100 |
triggers and <firstterm>statement-level</firstterm> triggers, |
| 101 |
respectively. Triggers on <command>TRUNCATE</command> may only be |
| 102 |
defined at statement level, not per-row. |
| 103 |
</para> |
| 104 |
|
| 105 |
<para> |
| 106 |
Triggers are also classified according to whether they fire |
| 107 |
<firstterm>before</firstterm>, <firstterm>after</firstterm>, or |
| 108 |
<firstterm>instead of</firstterm> the operation. These are referred to |
| 109 |
as <literal>BEFORE</literal> triggers, <literal>AFTER</literal> triggers, and |
| 110 |
<literal>INSTEAD OF</literal> triggers respectively. |
| 111 |
Statement-level <literal>BEFORE</literal> triggers naturally fire before the |
| 112 |
statement starts to do anything, while statement-level <literal>AFTER</literal> |
| 113 |
triggers fire at the very end of the statement. These types of |
| 114 |
triggers may be defined on tables, views, or foreign tables. Row-level |
| 115 |
<literal>BEFORE</literal> triggers fire immediately before a particular row is |
| 116 |
operated on, while row-level <literal>AFTER</literal> triggers fire at the end of |
| 117 |
the statement (but before any statement-level <literal>AFTER</literal> triggers). |
| 118 |
These types of triggers may only be defined on tables and |
| 119 |
foreign tables, not views. |
| 120 |
<literal>INSTEAD OF</literal> triggers may only be |
| 121 |
defined on views, and only at row level; they fire immediately as each |
| 122 |
row in the view is identified as needing to be operated on. |
| 123 |
</para> |
| 124 |
|
| 125 |
<para> |
| 126 |
The execution of an <literal>AFTER</literal> trigger can be deferred |
| 127 |
to the end of the transaction, rather than the end of the statement, |
| 128 |
if it was defined as a <firstterm>constraint trigger</firstterm>. |
| 129 |
In all cases, a trigger is executed as part of the same transaction as |
| 130 |
the statement that triggered it, so if either the statement or the |
| 131 |
trigger causes an error, the effects of both will be rolled back. |
| 132 |
Also, the trigger will always run as the role that queued the trigger |
| 133 |
event, unless the trigger function is marked as <literal>SECURITY |
| 134 |
DEFINER</literal>, in which case it will run as the function owner. |
| 135 |
</para> |
| 136 |
|
| 137 |
<para> |
| 138 |
If an <command>INSERT</command> contains an <literal>ON CONFLICT |
| 139 |
DO UPDATE</literal> clause, it is possible for row-level |
| 140 |
<literal>BEFORE</literal> <command>INSERT</command> and then |
| 141 |
<literal>BEFORE</literal> <command>UPDATE</command> triggers |
| 142 |
to be executed on triggered rows. Such interactions can be |
| 143 |
complex if the triggers are not idempotent because change made by |
| 144 |
<literal>BEFORE</literal> <command>INSERT</command> triggers will be |
| 145 |
seen by <literal>BEFORE</literal> <command>UPDATE</command> triggers, |
| 146 |
including changes to <varname>EXCLUDED</varname> columns. |
| 147 |
</para> |
| 148 |
|
| 149 |
<para> |
| 150 |
Note that statement-level |
| 151 |
<command>UPDATE</command> triggers are executed when <literal>ON |
| 152 |
CONFLICT DO UPDATE</literal> is specified, regardless of whether or not |
| 153 |
any rows were affected by the <command>UPDATE</command> (and |
| 154 |
regardless of whether the alternative <command>UPDATE</command> |
| 155 |
path was ever taken). An <command>INSERT</command> with an |
| 156 |
<literal>ON CONFLICT DO UPDATE</literal> clause will execute |
| 157 |
statement-level <literal>BEFORE</literal> <command>INSERT</command> |
| 158 |
triggers first, then statement-level <literal>BEFORE</literal> |
| 159 |
<command>UPDATE</command> triggers, followed by statement-level |
| 160 |
<literal>AFTER</literal> <command>UPDATE</command> triggers and finally |
| 161 |
statement-level <literal>AFTER</literal> <command>INSERT</command> |
| 162 |
triggers. |
| 163 |
</para> |
| 164 |
|
| 165 |
<para> |
| 166 |
A statement that targets a parent table in an inheritance or partitioning |
| 167 |
hierarchy does not cause the statement-level triggers of affected child |
| 168 |
tables to be fired; only the parent table's statement-level triggers are |
| 169 |
fired. However, row-level triggers of any affected child tables will be |
| 170 |
fired. |
| 171 |
</para> |
| 172 |
|
| 173 |
<para> |
| 174 |
If an <command>UPDATE</command> on a partitioned table causes a row to move |
| 175 |
to another partition, it will be performed as a <command>DELETE</command> |
| 176 |
from the original partition followed by an <command>INSERT</command> into |
| 177 |
the new partition. In this case, all row-level <literal>BEFORE</literal> |
| 178 |
<command>UPDATE</command> triggers and all row-level |
| 179 |
<literal>BEFORE</literal> <command>DELETE</command> triggers are fired on |
| 180 |
the original partition. Then all row-level <literal>BEFORE</literal> |
| 181 |
<command>INSERT</command> triggers are fired on the destination partition. |
| 182 |
The possibility of surprising outcomes should be considered when all these |
| 183 |
triggers affect the row being moved. As far as <literal>AFTER ROW</literal> |
| 184 |
triggers are concerned, <literal>AFTER</literal> <command>DELETE</command> |
| 185 |
and <literal>AFTER</literal> <command>INSERT</command> triggers are |
| 186 |
applied; but <literal>AFTER</literal> <command>UPDATE</command> triggers |
| 187 |
are not applied because the <command>UPDATE</command> has been converted to |
| 188 |
a <command>DELETE</command> and an <command>INSERT</command>. As far as |
| 189 |
statement-level triggers are concerned, none of the |
| 190 |
<command>DELETE</command> or <command>INSERT</command> triggers are fired, |
| 191 |
even if row movement occurs; only the <command>UPDATE</command> triggers |
| 192 |
defined on the target table used in the <command>UPDATE</command> statement |
| 193 |
will be fired. |
| 194 |
</para> |
| 195 |
|
| 196 |
<para> |
| 197 |
No separate triggers are defined for <command>MERGE</command>. Instead, |
| 198 |
statement-level or row-level <command>UPDATE</command>, |
| 199 |
<command>DELETE</command>, and <command>INSERT</command> triggers are fired |
| 200 |
depending on (for statement-level triggers) what actions are specified in |
| 201 |
the <command>MERGE</command> query and (for row-level triggers) what |
| 202 |
actions are performed. |
| 203 |
</para> |
| 204 |
|
| 205 |
<para> |
| 206 |
While running a <command>MERGE</command> command, statement-level |
| 207 |
<literal>BEFORE</literal> and <literal>AFTER</literal> triggers are |
| 208 |
fired for events specified in the actions of the <command>MERGE</command> |
| 209 |
command, irrespective of whether or not the action is ultimately performed. |
| 210 |
This is the same as an <command>UPDATE</command> statement that updates |
| 211 |
no rows, yet statement-level triggers are fired. |
| 212 |
The row-level triggers are fired only when a row is actually updated, |
| 213 |
inserted or deleted. So it's perfectly legal that while statement-level |
| 214 |
triggers are fired for certain types of action, no row-level triggers |
| 215 |
are fired for the same kind of action. |
| 216 |
</para> |
| 217 |
|
| 218 |
<para> |
| 219 |
Trigger functions invoked by per-statement triggers should always |
| 220 |
return <symbol>NULL</symbol>. Trigger functions invoked by per-row |
| 221 |
triggers can return a table row (a value of |
| 222 |
type <structname>HeapTuple</structname>) to the calling executor, |
| 223 |
if they choose. A row-level trigger fired before an operation has |
| 224 |
the following choices: |
| 225 |
|
| 226 |
<itemizedlist> |
| 227 |
<listitem> |
| 228 |
<para> |
| 229 |
It can return <symbol>NULL</symbol> to skip the operation for the |
| 230 |
current row. This instructs the executor to not perform the |
| 231 |
row-level operation that invoked the trigger (the insertion, |
| 232 |
modification, or deletion of a particular table row). |
| 233 |
</para> |
| 234 |
</listitem> |
| 235 |
|
| 236 |
<listitem> |
| 237 |
<para> |
| 238 |
For row-level <command>INSERT</command> |
| 239 |
and <command>UPDATE</command> triggers only, the returned row |
| 240 |
becomes the row that will be inserted or will replace the row |
| 241 |
being updated. This allows the trigger function to modify the |
| 242 |
row being inserted or updated. |
| 243 |
</para> |
| 244 |
</listitem> |
| 245 |
</itemizedlist> |
| 246 |
|
| 247 |
A row-level <literal>BEFORE</literal> trigger that does not intend to cause |
| 248 |
either of these behaviors must be careful to return as its result the same |
| 249 |
row that was passed in (that is, the <varname>NEW</varname> row |
| 250 |
for <command>INSERT</command> and <command>UPDATE</command> |
| 251 |
triggers, the <varname>OLD</varname> row for |
| 252 |
<command>DELETE</command> triggers). |
| 253 |
</para> |
| 254 |
|
| 255 |
<para> |
| 256 |
A row-level <literal>INSTEAD OF</literal> trigger should either return |
| 257 |
<symbol>NULL</symbol> to indicate that it did not modify any data from |
| 258 |
the view's underlying base tables, or it should return the view |
| 259 |
row that was passed in (the <varname>NEW</varname> row |
| 260 |
for <command>INSERT</command> and <command>UPDATE</command> |
| 261 |
operations, or the <varname>OLD</varname> row for |
| 262 |
<command>DELETE</command> operations). A nonnull return value is |
| 263 |
used to signal that the trigger performed the necessary data |
| 264 |
modifications in the view. This will cause the count of the number |
| 265 |
of rows affected by the command to be incremented. For |
| 266 |
<command>INSERT</command> and <command>UPDATE</command> operations only, the trigger |
| 267 |
may modify the <varname>NEW</varname> row before returning it. This will |
| 268 |
change the data returned by |
| 269 |
<command>INSERT RETURNING</command> or <command>UPDATE RETURNING</command>, |
| 270 |
and is useful when the view will not show exactly the same data |
| 271 |
that was provided. |
| 272 |
</para> |
| 273 |
|
| 274 |
<para> |
| 275 |
The return value is ignored for row-level triggers fired after an |
| 276 |
operation, and so they can return <symbol>NULL</symbol>. |
| 277 |
</para> |
| 278 |
|
| 279 |
<para> |
| 280 |
Some considerations apply for generated |
| 281 |
columns.<indexterm><primary>generated column</primary><secondary>in |
| 282 |
triggers</secondary></indexterm> Stored generated columns are computed after |
| 283 |
<literal>BEFORE</literal> triggers and before <literal>AFTER</literal> |
| 284 |
triggers. Therefore, the generated value can be inspected in |
| 285 |
<literal>AFTER</literal> triggers. In <literal>BEFORE</literal> triggers, |
| 286 |
the <literal>OLD</literal> row contains the old generated value, as one |
| 287 |
would expect, but the <literal>NEW</literal> row does not yet contain the |
| 288 |
new generated value and should not be accessed. In the C language |
| 289 |
interface, the content of the column is undefined at this point; a |
| 290 |
higher-level programming language should prevent access to a stored |
| 291 |
generated column in the <literal>NEW</literal> row in a |
| 292 |
<literal>BEFORE</literal> trigger. Changes to the value of a generated |
| 293 |
column in a <literal>BEFORE</literal> trigger are ignored and will be |
| 294 |
overwritten. |
| 295 |
Virtual generated columns are never computed when triggers fire. In the C |
| 296 |
language interface, their content is undefined in a trigger function. |
| 297 |
Higher-level programming languages should prevent access to virtual |
| 298 |
generated columns in triggers. |
| 299 |
</para> |
| 300 |
|
| 301 |
<para> |
| 302 |
If more than one trigger is defined for the same event on the same |
| 303 |
relation, the triggers will be fired in alphabetical order by |
| 304 |
trigger name. In the case of <literal>BEFORE</literal> and |
| 305 |
<literal>INSTEAD OF</literal> triggers, the possibly-modified row returned by |
| 306 |
each trigger becomes the input to the next trigger. If any |
| 307 |
<literal>BEFORE</literal> or <literal>INSTEAD OF</literal> trigger returns |
| 308 |
<symbol>NULL</symbol>, the operation is abandoned for that row and subsequent |
| 309 |
triggers are not fired (for that row). |
| 310 |
</para> |
| 311 |
|
| 312 |
<para> |
| 313 |
A trigger definition can also specify a Boolean <literal>WHEN</literal> |
| 314 |
condition, which will be tested to see whether the trigger should |
| 315 |
be fired. In row-level triggers the <literal>WHEN</literal> condition can |
| 316 |
examine the old and/or new values of columns of the row. (Statement-level |
| 317 |
triggers can also have <literal>WHEN</literal> conditions, although the feature |
| 318 |
is not so useful for them.) In a <literal>BEFORE</literal> trigger, the |
| 319 |
<literal>WHEN</literal> |
| 320 |
condition is evaluated just before the function is or would be executed, |
| 321 |
so using <literal>WHEN</literal> is not materially different from testing the |
| 322 |
same condition at the beginning of the trigger function. However, in |
| 323 |
an <literal>AFTER</literal> trigger, the <literal>WHEN</literal> condition is evaluated |
| 324 |
just after the row update occurs, and it determines whether an event is |
| 325 |
queued to fire the trigger at the end of statement. So when an |
| 326 |
<literal>AFTER</literal> trigger's |
| 327 |
<literal>WHEN</literal> condition does not return true, it is not necessary |
| 328 |
to queue an event nor to re-fetch the row at end of statement. This |
| 329 |
can result in significant speedups in statements that modify many |
| 330 |
rows, if the trigger only needs to be fired for a few of the rows. |
| 331 |
<literal>INSTEAD OF</literal> triggers do not support |
| 332 |
<literal>WHEN</literal> conditions. |
| 333 |
</para> |
| 334 |
|
| 335 |
<para> |
| 336 |
Typically, row-level <literal>BEFORE</literal> triggers are used for checking or |
| 337 |
modifying the data that will be inserted or updated. For example, |
| 338 |
a <literal>BEFORE</literal> trigger might be used to insert the current time into a |
| 339 |
<type>timestamp</type> column, or to check that two elements of the row are |
| 340 |
consistent. Row-level <literal>AFTER</literal> triggers are most sensibly |
| 341 |
used to propagate the updates to other tables, or make consistency |
| 342 |
checks against other tables. The reason for this division of labor is |
| 343 |
that an <literal>AFTER</literal> trigger can be certain it is seeing the final |
| 344 |
value of the row, while a <literal>BEFORE</literal> trigger cannot; there might |
| 345 |
be other <literal>BEFORE</literal> triggers firing after it. If you have no |
| 346 |
specific reason to make a trigger <literal>BEFORE</literal> or |
| 347 |
<literal>AFTER</literal>, the <literal>BEFORE</literal> case is more efficient, since |
| 348 |
the information about |
| 349 |
the operation doesn't have to be saved until end of statement. |
| 350 |
</para> |
| 351 |
|
| 352 |
<para> |
| 353 |
If a trigger function executes SQL commands then these |
| 354 |
commands might fire triggers again. This is known as cascading |
| 355 |
triggers. There is no direct limitation on the number of cascade |
| 356 |
levels. It is possible for cascades to cause a recursive invocation |
| 357 |
of the same trigger; for example, an <command>INSERT</command> |
| 358 |
trigger might execute a command that inserts an additional row |
| 359 |
into the same table, causing the <command>INSERT</command> trigger |
| 360 |
to be fired again. It is the trigger programmer's responsibility |
| 361 |
to avoid infinite recursion in such scenarios. |
| 362 |
</para> |
| 363 |
|
| 364 |
<para> |
| 365 |
If a foreign key constraint specifies referential actions (that |
| 366 |
is, cascading updates or deletes), those actions are performed via |
| 367 |
ordinary SQL <command>UPDATE</command> or <command>DELETE</command> |
| 368 |
commands on the referencing table. |
| 369 |
In particular, any triggers that exist on the referencing table |
| 370 |
will be fired for those changes. If such a trigger modifies or |
| 371 |
blocks the effect of one of these commands, the end result could |
| 372 |
be to break referential integrity. It is the trigger programmer's |
| 373 |
responsibility to avoid that. |
| 374 |
</para> |
| 375 |
|
| 376 |
<para> |
| 377 |
<indexterm> |
| 378 |
<primary>trigger</primary> |
| 379 |
<secondary>arguments for trigger functions</secondary> |
| 380 |
</indexterm> |
| 381 |
When a trigger is being defined, arguments can be specified for |
| 382 |
it. The purpose of including arguments in the |
| 383 |
trigger definition is to allow different triggers with similar |
| 384 |
requirements to call the same function. As an example, there |
| 385 |
could be a generalized trigger function that takes as its |
| 386 |
arguments two column names and puts the current user in one and |
| 387 |
the current time stamp in the other. Properly written, this |
| 388 |
trigger function would be independent of the specific table it is |
| 389 |
triggering on. So the same function could be used for |
| 390 |
<command>INSERT</command> events on any table with suitable |
| 391 |
columns, to automatically track creation of records in a |
| 392 |
transaction table for example. It could also be used to track |
| 393 |
last-update events if defined as an <command>UPDATE</command> |
| 394 |
trigger. |
| 395 |
</para> |
| 396 |
|
| 397 |
<para> |
| 398 |
Each programming language that supports triggers has its own method |
| 399 |
for making the trigger input data available to the trigger function. |
| 400 |
This input data includes the type of trigger event (e.g., |
| 401 |
<command>INSERT</command> or <command>UPDATE</command>) as well as any |
| 402 |
arguments that were listed in <command>CREATE TRIGGER</command>. |
| 403 |
For a row-level trigger, the input data also includes the |
| 404 |
<varname>NEW</varname> row for <command>INSERT</command> and |
| 405 |
<command>UPDATE</command> triggers, and/or the <varname>OLD</varname> row |
| 406 |
for <command>UPDATE</command> and <command>DELETE</command> triggers. |
| 407 |
</para> |
| 408 |
|
| 409 |
<para> |
| 410 |
By default, statement-level triggers do not have any way to examine the |
| 411 |
individual row(s) modified by the statement. But an <literal>AFTER |
| 412 |
STATEMENT</literal> trigger can request that <firstterm>transition tables</firstterm> |
| 413 |
be created to make the sets of affected rows available to the trigger. |
| 414 |
<literal>AFTER ROW</literal> triggers can also request transition tables, so |
| 415 |
that they can see the total changes in the table as well as the change in |
| 416 |
the individual row they are currently being fired for. The method for |
| 417 |
examining the transition tables again depends on the programming language |
| 418 |
that is being used, but the typical approach is to make the transition |
| 419 |
tables act like read-only temporary tables that can be accessed by SQL |
| 420 |
commands issued within the trigger function. |
| 421 |
</para> |
| 422 |
|
| 423 |
</sect1> |
| 424 |
|
| 425 |
<sect1 id="trigger-datachanges"> |
| 426 |
<title>Visibility of Data Changes</title> |
| 427 |
|
| 428 |
<para> |
| 429 |
If you execute SQL commands in your trigger function, and these |
| 430 |
commands access the table that the trigger is for, then |
| 431 |
you need to be aware of the data visibility rules, because they determine |
| 432 |
whether these SQL commands will see the data change that the trigger |
| 433 |
is fired for. Briefly: |
| 434 |
|
| 435 |
<itemizedlist> |
| 436 |
|
| 437 |
<listitem> |
| 438 |
<para> |
| 439 |
Statement-level triggers follow simple visibility rules: none of |
| 440 |
the changes made by a statement are visible to statement-level |
| 441 |
<literal>BEFORE</literal> triggers, whereas all |
| 442 |
modifications are visible to statement-level <literal>AFTER</literal> |
| 443 |
triggers. |
| 444 |
</para> |
| 445 |
</listitem> |
| 446 |
|
| 447 |
<listitem> |
| 448 |
<para> |
| 449 |
The data change (insertion, update, or deletion) causing the |
| 450 |
trigger to fire is naturally <emphasis>not</emphasis> visible |
| 451 |
to SQL commands executed in a row-level <literal>BEFORE</literal> trigger, |
| 452 |
because it hasn't happened yet. |
| 453 |
</para> |
| 454 |
</listitem> |
| 455 |
|
| 456 |
<listitem> |
| 457 |
<para> |
| 458 |
However, SQL commands executed in a row-level <literal>BEFORE</literal> |
| 459 |
trigger <emphasis>will</emphasis> see the effects of data |
| 460 |
changes for rows previously processed in the same outer |
| 461 |
command. This requires caution, since the ordering of these |
| 462 |
change events is not in general predictable; an SQL command that |
| 463 |
affects multiple rows can visit the rows in any order. |
| 464 |
</para> |
| 465 |
</listitem> |
| 466 |
|
| 467 |
<listitem> |
| 468 |
<para> |
| 469 |
Similarly, a row-level <literal>INSTEAD OF</literal> trigger will see the |
| 470 |
effects of data changes made by previous firings of <literal>INSTEAD |
| 471 |
OF</literal> triggers in the same outer command. |
| 472 |
</para> |
| 473 |
</listitem> |
| 474 |
|
| 475 |
<listitem> |
| 476 |
<para> |
| 477 |
When a row-level <literal>AFTER</literal> trigger is fired, all data |
| 478 |
changes made |
| 479 |
by the outer command are already complete, and are visible to |
| 480 |
the invoked trigger function. |
| 481 |
</para> |
| 482 |
</listitem> |
| 483 |
</itemizedlist> |
| 484 |
</para> |
| 485 |
|
| 486 |
<para> |
| 487 |
If your trigger function is written in any of the standard procedural |
| 488 |
languages, then the above statements apply only if the function is |
| 489 |
declared <literal>VOLATILE</literal>. Functions that are declared |
| 490 |
<literal>STABLE</literal> or <literal>IMMUTABLE</literal> will not see changes made by |
| 491 |
the calling command in any case. |
| 492 |
</para> |
| 493 |
|
| 494 |
<para> |
| 495 |
Further information about data visibility rules can be found in |
| 496 |
<xref linkend="spi-visibility"/>. The example in <xref |
| 497 |
linkend="trigger-example"/> contains a demonstration of these rules. |
| 498 |
</para> |
| 499 |
</sect1> |
| 500 |
|
| 501 |
<sect1 id="trigger-interface"> |
| 502 |
<title>Writing Trigger Functions in C</title> |
| 503 |
|
| 504 |
<indexterm zone="trigger-interface"> |
| 505 |
<primary>trigger</primary> |
| 506 |
<secondary>in C</secondary> |
| 507 |
</indexterm> |
| 508 |
|
| 509 |
<indexterm> |
| 510 |
<primary>transition tables</primary> |
| 511 |
<secondary>referencing from C trigger</secondary> |
| 512 |
</indexterm> |
| 513 |
|
| 514 |
<para> |
| 515 |
This section describes the low-level details of the interface to a |
| 516 |
trigger function. This information is only needed when writing |
| 517 |
trigger functions in C. If you are using a higher-level language then |
| 518 |
these details are handled for you. In most cases you should consider |
| 519 |
using a procedural language before writing your triggers in C. The |
| 520 |
documentation of each procedural language explains how to write a |
| 521 |
trigger in that language. |
| 522 |
</para> |
| 523 |
|
| 524 |
<para> |
| 525 |
Trigger functions must use the <quote>version 1</quote> function manager |
| 526 |
interface. |
| 527 |
</para> |
| 528 |
|
| 529 |
<para> |
| 530 |
When a function is called by the trigger manager, it is not passed |
| 531 |
any normal arguments, but it is passed a <quote>context</quote> |
| 532 |
pointer pointing to a <structname>TriggerData</structname> structure. C |
| 533 |
functions can check whether they were called from the trigger |
| 534 |
manager or not by executing the macro: |
| 535 |
<programlisting> |
| 536 |
CALLED_AS_TRIGGER(fcinfo) |
| 537 |
</programlisting> |
| 538 |
which expands to: |
| 539 |
<programlisting> |
| 540 |
((fcinfo)->context != NULL && IsA((fcinfo)->context, TriggerData)) |
| 541 |
</programlisting> |
| 542 |
If this returns true, then it is safe to cast |
| 543 |
<literal>fcinfo->context</literal> to type <literal>TriggerData |
| 544 |
*</literal> and make use of the pointed-to |
| 545 |
<structname>TriggerData</structname> structure. The function must |
| 546 |
<emphasis>not</emphasis> alter the <structname>TriggerData</structname> |
| 547 |
structure or any of the data it points to. |
| 548 |
</para> |
| 549 |
|
| 550 |
<para> |
| 551 |
<structname>struct TriggerData</structname> is defined in |
| 552 |
<filename>commands/trigger.h</filename>: |
| 553 |
|
| 554 |
<programlisting> |
| 555 |
typedef struct TriggerData |
| 556 |
{ |
| 557 |
NodeTag type; |
| 558 |
TriggerEvent tg_event; |
| 559 |
Relation tg_relation; |
| 560 |
HeapTuple tg_trigtuple; |
| 561 |
HeapTuple tg_newtuple; |
| 562 |
Trigger *tg_trigger; |
| 563 |
TupleTableSlot *tg_trigslot; |
| 564 |
TupleTableSlot *tg_newslot; |
| 565 |
Tuplestorestate *tg_oldtable; |
| 566 |
Tuplestorestate *tg_newtable; |
| 567 |
const Bitmapset *tg_updatedcols; |
| 568 |
} TriggerData; |
| 569 |
</programlisting> |
| 570 |
|
| 571 |
where the members are defined as follows: |
| 572 |
|
| 573 |
<variablelist> |
| 574 |
<varlistentry> |
| 575 |
<term><structfield>type</structfield></term> |
| 576 |
<listitem> |
| 577 |
<para> |
| 578 |
Always <literal>T_TriggerData</literal>. |
| 579 |
</para> |
| 580 |
</listitem> |
| 581 |
</varlistentry> |
| 582 |
|
| 583 |
<varlistentry> |
| 584 |
<term><structfield>tg_event</structfield></term> |
| 585 |
<listitem> |
| 586 |
<para> |
| 587 |
Describes the event for which the function is called. You can use the |
| 588 |
following macros to examine <literal>tg_event</literal>: |
| 589 |
|
| 590 |
<variablelist> |
| 591 |
<varlistentry> |
| 592 |
<term><literal>TRIGGER_FIRED_BEFORE(tg_event)</literal></term> |
| 593 |
<listitem> |
| 594 |
<para> |
| 595 |
Returns true if the trigger fired before the operation. |
| 596 |
</para> |
| 597 |
</listitem> |
| 598 |
</varlistentry> |
| 599 |
|
| 600 |
<varlistentry> |
| 601 |
<term><literal>TRIGGER_FIRED_AFTER(tg_event)</literal></term> |
| 602 |
<listitem> |
| 603 |
<para> |
| 604 |
Returns true if the trigger fired after the operation. |
| 605 |
</para> |
| 606 |
</listitem> |
| 607 |
</varlistentry> |
| 608 |
|
| 609 |
<varlistentry> |
| 610 |
<term><literal>TRIGGER_FIRED_INSTEAD(tg_event)</literal></term> |
| 611 |
<listitem> |
| 612 |
<para> |
| 613 |
Returns true if the trigger fired instead of the operation. |
| 614 |
</para> |
| 615 |
</listitem> |
| 616 |
</varlistentry> |
| 617 |
|
| 618 |
<varlistentry> |
| 619 |
<term><literal>TRIGGER_FIRED_FOR_ROW(tg_event)</literal></term> |
| 620 |
<listitem> |
| 621 |
<para> |
| 622 |
Returns true if the trigger fired for a row-level event. |
| 623 |
</para> |
| 624 |
</listitem> |
| 625 |
</varlistentry> |
| 626 |
|
| 627 |
<varlistentry> |
| 628 |
<term><literal>TRIGGER_FIRED_FOR_STATEMENT(tg_event)</literal></term> |
| 629 |
<listitem> |
| 630 |
<para> |
| 631 |
Returns true if the trigger fired for a statement-level event. |
| 632 |
</para> |
| 633 |
</listitem> |
| 634 |
</varlistentry> |
| 635 |
|
| 636 |
<varlistentry> |
| 637 |
<term><literal>TRIGGER_FIRED_BY_INSERT(tg_event)</literal></term> |
| 638 |
<listitem> |
| 639 |
<para> |
| 640 |
Returns true if the trigger was fired by an <command>INSERT</command> command. |
| 641 |
</para> |
| 642 |
</listitem> |
| 643 |
</varlistentry> |
| 644 |
|
| 645 |
<varlistentry> |
| 646 |
<term><literal>TRIGGER_FIRED_BY_UPDATE(tg_event)</literal></term> |
| 647 |
<listitem> |
| 648 |
<para> |
| 649 |
Returns true if the trigger was fired by an <command>UPDATE</command> command. |
| 650 |
</para> |
| 651 |
</listitem> |
| 652 |
</varlistentry> |
| 653 |
|
| 654 |
<varlistentry> |
| 655 |
<term><literal>TRIGGER_FIRED_BY_DELETE(tg_event)</literal></term> |
| 656 |
<listitem> |
| 657 |
<para> |
| 658 |
Returns true if the trigger was fired by a <command>DELETE</command> command. |
| 659 |
</para> |
| 660 |
</listitem> |
| 661 |
</varlistentry> |
| 662 |
|
| 663 |
<varlistentry> |
| 664 |
<term><literal>TRIGGER_FIRED_BY_TRUNCATE(tg_event)</literal></term> |
| 665 |
<listitem> |
| 666 |
<para> |
| 667 |
Returns true if the trigger was fired by a <command>TRUNCATE</command> command. |
| 668 |
</para> |
| 669 |
</listitem> |
| 670 |
</varlistentry> |
| 671 |
</variablelist> |
| 672 |
</para> |
| 673 |
</listitem> |
| 674 |
</varlistentry> |
| 675 |
|
| 676 |
<varlistentry> |
| 677 |
<term><structfield>tg_relation</structfield></term> |
| 678 |
<listitem> |
| 679 |
<para> |
| 680 |
A pointer to a structure describing the relation that the trigger fired for. |
| 681 |
Look at <filename>utils/rel.h</filename> for details about |
| 682 |
this structure. The most interesting things are |
| 683 |
<literal>tg_relation->rd_att</literal> (descriptor of the relation |
| 684 |
tuples) and <literal>tg_relation->rd_rel->relname</literal> |
| 685 |
(relation name; the type is not <type>char*</type> but |
| 686 |
<type>NameData</type>; use |
| 687 |
<literal>SPI_getrelname(tg_relation)</literal> to get a <type>char*</type> if you |
| 688 |
need a copy of the name). |
| 689 |
</para> |
| 690 |
</listitem> |
| 691 |
</varlistentry> |
| 692 |
|
| 693 |
<varlistentry> |
| 694 |
<term><structfield>tg_trigtuple</structfield></term> |
| 695 |
<listitem> |
| 696 |
<para> |
| 697 |
A pointer to the row for which the trigger was fired. This is |
| 698 |
the row being inserted, updated, or deleted. If this trigger |
| 699 |
was fired for an <command>INSERT</command> or |
| 700 |
<command>DELETE</command> then this is what you should return |
| 701 |
from the function if you don't want to replace the row with |
| 702 |
a different one (in the case of <command>INSERT</command>) or |
| 703 |
skip the operation. For triggers on foreign tables, values of system |
| 704 |
columns herein are unspecified. |
| 705 |
</para> |
| 706 |
</listitem> |
| 707 |
</varlistentry> |
| 708 |
|
| 709 |
<varlistentry> |
| 710 |
<term><structfield>tg_newtuple</structfield></term> |
| 711 |
<listitem> |
| 712 |
<para> |
| 713 |
A pointer to the new version of the row, if the trigger was |
| 714 |
fired for an <command>UPDATE</command>, and <symbol>NULL</symbol> if |
| 715 |
it is for an <command>INSERT</command> or a |
| 716 |
<command>DELETE</command>. This is what you have to return |
| 717 |
from the function if the event is an <command>UPDATE</command> |
| 718 |
and you don't want to replace this row by a different one or |
| 719 |
skip the operation. For triggers on foreign tables, values of system |
| 720 |
columns herein are unspecified. |
| 721 |
</para> |
| 722 |
</listitem> |
| 723 |
</varlistentry> |
| 724 |
|
| 725 |
<varlistentry> |
| 726 |
<term><structfield>tg_trigger</structfield></term> |
| 727 |
<listitem> |
| 728 |
<para> |
| 729 |
A pointer to a structure of type <structname>Trigger</structname>, |
| 730 |
defined in <filename>utils/reltrigger.h</filename>: |
| 731 |
|
| 732 |
<programlisting> |
| 733 |
typedef struct Trigger |
| 734 |
{ |
| 735 |
Oid tgoid; |
| 736 |
char *tgname; |
| 737 |
Oid tgfoid; |
| 738 |
int16 tgtype; |
| 739 |
char tgenabled; |
| 740 |
bool tgisinternal; |
| 741 |
bool tgisclone; |
| 742 |
Oid tgconstrrelid; |
| 743 |
Oid tgconstrindid; |
| 744 |
Oid tgconstraint; |
| 745 |
bool tgdeferrable; |
| 746 |
bool tginitdeferred; |
| 747 |
int16 tgnargs; |
| 748 |
int16 tgnattr; |
| 749 |
int16 *tgattr; |
| 750 |
char **tgargs; |
| 751 |
char *tgqual; |
| 752 |
char *tgoldtable; |
| 753 |
char *tgnewtable; |
| 754 |
} Trigger; |
| 755 |
</programlisting> |
| 756 |
|
| 757 |
where <structfield>tgname</structfield> is the trigger's name, |
| 758 |
<structfield>tgnargs</structfield> is the number of arguments in |
| 759 |
<structfield>tgargs</structfield>, and <structfield>tgargs</structfield> is an array of |
| 760 |
pointers to the arguments specified in the <command>CREATE |
| 761 |
TRIGGER</command> statement. The other members are for internal use |
| 762 |
only. |
| 763 |
</para> |
| 764 |
</listitem> |
| 765 |
</varlistentry> |
| 766 |
|
| 767 |
<varlistentry> |
| 768 |
<term><structfield>tg_trigslot</structfield></term> |
| 769 |
<listitem> |
| 770 |
<para> |
| 771 |
The slot containing <structfield>tg_trigtuple</structfield>, |
| 772 |
or a <symbol>NULL</symbol> pointer if there is no such tuple. |
| 773 |
</para> |
| 774 |
</listitem> |
| 775 |
</varlistentry> |
| 776 |
|
| 777 |
<varlistentry> |
| 778 |
<term><structfield>tg_newslot</structfield></term> |
| 779 |
<listitem> |
| 780 |
<para> |
| 781 |
The slot containing <structfield>tg_newtuple</structfield>, |
| 782 |
or a <symbol>NULL</symbol> pointer if there is no such tuple. |
| 783 |
</para> |
| 784 |
</listitem> |
| 785 |
</varlistentry> |
| 786 |
|
| 787 |
<varlistentry> |
| 788 |
<term><structfield>tg_oldtable</structfield></term> |
| 789 |
<listitem> |
| 790 |
<para> |
| 791 |
A pointer to a structure of type <structname>Tuplestorestate</structname> |
| 792 |
containing zero or more rows in the format specified by |
| 793 |
<structfield>tg_relation</structfield>, or a <symbol>NULL</symbol> pointer |
| 794 |
if there is no <literal>OLD TABLE</literal> transition relation. |
| 795 |
</para> |
| 796 |
</listitem> |
| 797 |
</varlistentry> |
| 798 |
|
| 799 |
<varlistentry> |
| 800 |
<term><structfield>tg_newtable</structfield></term> |
| 801 |
<listitem> |
| 802 |
<para> |
| 803 |
A pointer to a structure of type <structname>Tuplestorestate</structname> |
| 804 |
containing zero or more rows in the format specified by |
| 805 |
<structfield>tg_relation</structfield>, or a <symbol>NULL</symbol> pointer |
| 806 |
if there is no <literal>NEW TABLE</literal> transition relation. |
| 807 |
</para> |
| 808 |
</listitem> |
| 809 |
</varlistentry> |
| 810 |
|
| 811 |
<varlistentry> |
| 812 |
<term><structfield>tg_updatedcols</structfield></term> |
| 813 |
<listitem> |
| 814 |
<para> |
| 815 |
For <literal>UPDATE</literal> triggers, a bitmap set indicating the |
| 816 |
columns that were updated by the triggering command. Generic trigger |
| 817 |
functions can use this to optimize actions by not having to deal with |
| 818 |
columns that were not changed. |
| 819 |
</para> |
| 820 |
|
| 821 |
<para> |
| 822 |
As an example, to determine whether a column with attribute number |
| 823 |
<varname>attnum</varname> (1-based) is a member of this bitmap set, |
| 824 |
call <literal>bms_is_member(attnum - |
| 825 |
FirstLowInvalidHeapAttributeNumber, |
| 826 |
trigdata->tg_updatedcols))</literal>. |
| 827 |
</para> |
| 828 |
|
| 829 |
<para> |
| 830 |
For triggers other than <literal>UPDATE</literal> triggers, this will |
| 831 |
be <symbol>NULL</symbol>. |
| 832 |
</para> |
| 833 |
</listitem> |
| 834 |
</varlistentry> |
| 835 |
</variablelist> |
| 836 |
</para> |
| 837 |
|
| 838 |
<para> |
| 839 |
To allow queries issued through SPI to reference transition tables, see |
| 840 |
<xref linkend="spi-spi-register-trigger-data"/>. |
| 841 |
</para> |
| 842 |
|
| 843 |
<para> |
| 844 |
A trigger function must return either a |
| 845 |
<structname>HeapTuple</structname> pointer or a <symbol>NULL</symbol> pointer |
| 846 |
(<emphasis>not</emphasis> an SQL null value, that is, do not set <parameter>isNull</parameter> true). |
| 847 |
Be careful to return either |
| 848 |
<structfield>tg_trigtuple</structfield> or <structfield>tg_newtuple</structfield>, |
| 849 |
as appropriate, if you don't want to modify the row being operated on. |
| 850 |
</para> |
| 851 |
</sect1> |
| 852 |
|
| 853 |
<sect1 id="trigger-example"> |
| 854 |
<title>A Complete Trigger Example</title> |
| 855 |
|
| 856 |
<para> |
| 857 |
Here is a very simple example of a trigger function written in C. |
| 858 |
(Examples of triggers written in procedural languages can be found |
| 859 |
in the documentation of the procedural languages.) |
| 860 |
</para> |
| 861 |
|
| 862 |
<para> |
| 863 |
The function <function>trigf</function> reports the number of rows in the |
| 864 |
table <structname>ttest</structname> and skips the actual operation if the |
| 865 |
command attempts to insert a null value into the column |
| 866 |
<structfield>x</structfield>. (So the trigger acts as a not-null constraint but |
| 867 |
doesn't abort the transaction.) |
| 868 |
</para> |
| 869 |
|
| 870 |
<para> |
| 871 |
First, the table definition: |
| 872 |
<programlisting> |
| 873 |
CREATE TABLE ttest ( |
| 874 |
x integer |
| 875 |
); |
| 876 |
</programlisting> |
| 877 |
</para> |
| 878 |
|
| 879 |
<para> |
| 880 |
This is the source code of the trigger function: |
| 881 |
<programlisting><![CDATA[ |
| 882 |
#include "postgres.h" |
| 883 |
#include "fmgr.h" |
| 884 |
#include "executor/spi.h" /* this is what you need to work with SPI */ |
| 885 |
#include "commands/trigger.h" /* ... triggers ... */ |
| 886 |
#include "utils/rel.h" /* ... and relations */ |
| 887 |
|
| 888 |
PG_MODULE_MAGIC; |
| 889 |
|
| 890 |
PG_FUNCTION_INFO_V1(trigf); |
| 891 |
|
| 892 |
Datum |
| 893 |
trigf(PG_FUNCTION_ARGS) |
| 894 |
{ |
| 895 |
TriggerData *trigdata = (TriggerData *) fcinfo->context; |
| 896 |
TupleDesc tupdesc; |
| 897 |
HeapTuple rettuple; |
| 898 |
char *when; |
| 899 |
bool checknull = false; |
| 900 |
bool isnull; |
| 901 |
int ret, i; |
| 902 |
|
| 903 |
/* make sure it's called as a trigger at all */ |
| 904 |
if (!CALLED_AS_TRIGGER(fcinfo)) |
| 905 |
elog(ERROR, "trigf: not called by trigger manager"); |
| 906 |
|
| 907 |
/* tuple to return to executor */ |
| 908 |
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) |
| 909 |
rettuple = trigdata->tg_newtuple; |
| 910 |
else |
| 911 |
rettuple = trigdata->tg_trigtuple; |
| 912 |
|
| 913 |
/* check for null values */ |
| 914 |
if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event) |
| 915 |
&& TRIGGER_FIRED_BEFORE(trigdata->tg_event)) |
| 916 |
checknull = true; |
| 917 |
|
| 918 |
if (TRIGGER_FIRED_BEFORE(trigdata->tg_event)) |
| 919 |
when = "before"; |
| 920 |
else |
| 921 |
when = "after "; |
| 922 |
|
| 923 |
tupdesc = trigdata->tg_relation->rd_att; |
| 924 |
|
| 925 |
/* connect to SPI manager */ |
| 926 |
SPI_connect(); |
| 927 |
|
| 928 |
/* get number of rows in table */ |
| 929 |
ret = SPI_exec("SELECT count(*) FROM ttest", 0); |
| 930 |
|
| 931 |
if (ret < 0) |
| 932 |
elog(ERROR, "trigf (fired %s): SPI_exec returned %d", when, ret); |
| 933 |
|
| 934 |
/* count(*) returns int8, so be careful to convert */ |
| 935 |
i = DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0], |
| 936 |
SPI_tuptable->tupdesc, |
| 937 |
1, |
| 938 |
&isnull)); |
| 939 |
|
| 940 |
elog (INFO, "trigf (fired %s): there are %d rows in ttest", when, i); |
| 941 |
|
| 942 |
SPI_finish(); |
| 943 |
|
| 944 |
if (checknull) |
| 945 |
{ |
| 946 |
SPI_getbinval(rettuple, tupdesc, 1, &isnull); |
| 947 |
if (isnull) |
| 948 |
rettuple = NULL; |
| 949 |
} |
| 950 |
|
| 951 |
return PointerGetDatum(rettuple); |
| 952 |
} |
| 953 |
]]> |
| 954 |
</programlisting> |
| 955 |
</para> |
| 956 |
|
| 957 |
<para> |
| 958 |
After you have compiled the source code (see <xref |
| 959 |
linkend="dfunc"/>), declare the function and the triggers: |
| 960 |
<programlisting> |
| 961 |
CREATE FUNCTION trigf() RETURNS trigger |
| 962 |
AS '<replaceable>filename</replaceable>' |
| 963 |
LANGUAGE C; |
| 964 |
|
| 965 |
CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest |
| 966 |
FOR EACH ROW EXECUTE FUNCTION trigf(); |
| 967 |
|
| 968 |
CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest |
| 969 |
FOR EACH ROW EXECUTE FUNCTION trigf(); |
| 970 |
</programlisting> |
| 971 |
</para> |
| 972 |
|
| 973 |
<para> |
| 974 |
Now you can test the operation of the trigger: |
| 975 |
<screen> |
| 976 |
=> INSERT INTO ttest VALUES (NULL); |
| 977 |
INFO: trigf (fired before): there are 0 rows in ttest |
| 978 |
INSERT 0 0 |
| 979 |
|
| 980 |
-- Insertion skipped and AFTER trigger is not fired |
| 981 |
|
| 982 |
=> SELECT * FROM ttest; |
| 983 |
x |
| 984 |
--- |
| 985 |
(0 rows) |
| 986 |
|
| 987 |
=> INSERT INTO ttest VALUES (1); |
| 988 |
INFO: trigf (fired before): there are 0 rows in ttest |
| 989 |
INFO: trigf (fired after ): there are 1 rows in ttest |
| 990 |
^^^^^^^^ |
| 991 |
remember what we said about visibility. |
| 992 |
INSERT 167793 1 |
| 993 |
vac=> SELECT * FROM ttest; |
| 994 |
x |
| 995 |
--- |
| 996 |
1 |
| 997 |
(1 row) |
| 998 |
|
| 999 |
=> INSERT INTO ttest SELECT x * 2 FROM ttest; |
| 1000 |
INFO: trigf (fired before): there are 1 rows in ttest |
| 1001 |
INFO: trigf (fired after ): there are 2 rows in ttest |
| 1002 |
^^^^^^ |
| 1003 |
remember what we said about visibility. |
| 1004 |
INSERT 167794 1 |
| 1005 |
=> SELECT * FROM ttest; |
| 1006 |
x |
| 1007 |
--- |
| 1008 |
1 |
| 1009 |
2 |
| 1010 |
(2 rows) |
| 1011 |
|
| 1012 |
=> UPDATE ttest SET x = NULL WHERE x = 2; |
| 1013 |
INFO: trigf (fired before): there are 2 rows in ttest |
| 1014 |
UPDATE 0 |
| 1015 |
=> UPDATE ttest SET x = 4 WHERE x = 2; |
| 1016 |
INFO: trigf (fired before): there are 2 rows in ttest |
| 1017 |
INFO: trigf (fired after ): there are 2 rows in ttest |
| 1018 |
UPDATE 1 |
| 1019 |
vac=> SELECT * FROM ttest; |
| 1020 |
x |
| 1021 |
--- |
| 1022 |
1 |
| 1023 |
4 |
| 1024 |
(2 rows) |
| 1025 |
|
| 1026 |
=> DELETE FROM ttest; |
| 1027 |
INFO: trigf (fired before): there are 2 rows in ttest |
| 1028 |
INFO: trigf (fired before): there are 1 rows in ttest |
| 1029 |
INFO: trigf (fired after ): there are 0 rows in ttest |
| 1030 |
INFO: trigf (fired after ): there are 0 rows in ttest |
| 1031 |
^^^^^^ |
| 1032 |
remember what we said about visibility. |
| 1033 |
DELETE 2 |
| 1034 |
=> SELECT * FROM ttest; |
| 1035 |
x |
| 1036 |
--- |
| 1037 |
(0 rows) |
| 1038 |
</screen> |
| 1039 |
|
| 1040 |
</para> |
| 1041 |
|
| 1042 |
<para> |
| 1043 |
There are more complex examples in |
| 1044 |
<filename>src/test/regress/regress.c</filename> and |
| 1045 |
in <xref linkend="contrib-spi"/>. |
| 1046 |
</para> |
| 1047 |
</sect1> |
| 1048 |
</chapter> |