| 1 |
<!-- |
| 2 |
doc/src/sgml/ref/create_trigger.sgml |
| 3 |
PostgreSQL documentation |
| 4 |
--> |
| 5 |
|
| 6 |
<refentry id="sql-createtrigger"> |
| 7 |
<indexterm zone="sql-createtrigger"> |
| 8 |
<primary>CREATE TRIGGER</primary> |
| 9 |
</indexterm> |
| 10 |
|
| 11 |
<indexterm> |
| 12 |
<primary>transition tables</primary> |
| 13 |
<seealso>ephemeral named relation</seealso> |
| 14 |
</indexterm> |
| 15 |
|
| 16 |
<refmeta> |
| 17 |
<refentrytitle>CREATE TRIGGER</refentrytitle> |
| 18 |
<manvolnum>7</manvolnum> |
| 19 |
<refmiscinfo>SQL - Language Statements</refmiscinfo> |
| 20 |
</refmeta> |
| 21 |
|
| 22 |
<refnamediv> |
| 23 |
<refname>CREATE TRIGGER</refname> |
| 24 |
<refpurpose>define a new trigger</refpurpose> |
| 25 |
</refnamediv> |
| 26 |
|
| 27 |
<refsynopsisdiv> |
| 28 |
<synopsis> |
| 29 |
CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name</replaceable> { BEFORE | AFTER | INSTEAD OF } { <replaceable class="parameter">event</replaceable> [ OR ... ] } |
| 30 |
ON <replaceable class="parameter">table_name</replaceable> |
| 31 |
[ FROM <replaceable class="parameter">referenced_table_name</replaceable> ] |
| 32 |
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] |
| 33 |
[ REFERENCING { { OLD | NEW } TABLE [ AS ] <replaceable class="parameter">transition_relation_name</replaceable> } [ ... ] ] |
| 34 |
[ FOR [ EACH ] { ROW | STATEMENT } ] |
| 35 |
[ WHEN ( <replaceable class="parameter">condition</replaceable> ) ] |
| 36 |
EXECUTE { FUNCTION | PROCEDURE } <replaceable class="parameter">function_name</replaceable> ( <replaceable class="parameter">arguments</replaceable> ) |
| 37 |
|
| 38 |
<phrase>where <replaceable class="parameter">event</replaceable> can be one of:</phrase> |
| 39 |
|
| 40 |
INSERT |
| 41 |
UPDATE [ OF <replaceable class="parameter">column_name</replaceable> [, ... ] ] |
| 42 |
DELETE |
| 43 |
TRUNCATE |
| 44 |
</synopsis> |
| 45 |
</refsynopsisdiv> |
| 46 |
|
| 47 |
<refsect1> |
| 48 |
<title>Description</title> |
| 49 |
|
| 50 |
<para> |
| 51 |
<command>CREATE TRIGGER</command> creates a new trigger. |
| 52 |
<command>CREATE OR REPLACE TRIGGER</command> will either create a |
| 53 |
new trigger, or replace an existing trigger. The |
| 54 |
trigger will be associated with the specified table, view, or foreign table |
| 55 |
and will execute the specified |
| 56 |
function <replaceable class="parameter">function_name</replaceable> when |
| 57 |
certain operations are performed on that table. |
| 58 |
</para> |
| 59 |
|
| 60 |
<para> |
| 61 |
To replace the current definition of an existing trigger, use |
| 62 |
<command>CREATE OR REPLACE TRIGGER</command>, specifying the existing |
| 63 |
trigger's name and parent table. All other properties are replaced. |
| 64 |
</para> |
| 65 |
|
| 66 |
<para> |
| 67 |
The trigger can be specified to fire before the |
| 68 |
operation is attempted on a row (before constraints are checked and |
| 69 |
the <command>INSERT</command>, <command>UPDATE</command>, or |
| 70 |
<command>DELETE</command> is attempted); or after the operation has |
| 71 |
completed (after constraints are checked and the |
| 72 |
<command>INSERT</command>, <command>UPDATE</command>, or |
| 73 |
<command>DELETE</command> has completed); or instead of the operation |
| 74 |
(in the case of inserts, updates or deletes on a view). |
| 75 |
If the trigger fires before or instead of the event, the trigger can skip |
| 76 |
the operation for the current row, or change the row being inserted (for |
| 77 |
<command>INSERT</command> and <command>UPDATE</command> operations |
| 78 |
only). If the trigger fires after the event, all changes, including |
| 79 |
the effects of other triggers, are <quote>visible</quote> |
| 80 |
to the trigger. |
| 81 |
</para> |
| 82 |
|
| 83 |
<para> |
| 84 |
A trigger that is marked <literal>FOR EACH ROW</literal> is called |
| 85 |
once for every row that the operation modifies. For example, a |
| 86 |
<command>DELETE</command> that affects 10 rows will cause any |
| 87 |
<literal>ON DELETE</literal> triggers on the target relation to be |
| 88 |
called 10 separate times, once for each deleted row. In contrast, a |
| 89 |
trigger that is marked <literal>FOR EACH STATEMENT</literal> only |
| 90 |
executes once for any given operation, regardless of how many rows |
| 91 |
it modifies (in particular, an operation that modifies zero rows |
| 92 |
will still result in the execution of any applicable <literal>FOR |
| 93 |
EACH STATEMENT</literal> triggers). |
| 94 |
</para> |
| 95 |
|
| 96 |
<para> |
| 97 |
Triggers that are specified to fire <literal>INSTEAD OF</literal> the trigger |
| 98 |
event must be marked <literal>FOR EACH ROW</literal>, and can only be defined |
| 99 |
on views. <literal>BEFORE</literal> and <literal>AFTER</literal> triggers on a view |
| 100 |
must be marked as <literal>FOR EACH STATEMENT</literal>. |
| 101 |
</para> |
| 102 |
|
| 103 |
<para> |
| 104 |
In addition, triggers may be defined to fire for |
| 105 |
<command>TRUNCATE</command>, though only |
| 106 |
<literal>FOR EACH STATEMENT</literal>. |
| 107 |
</para> |
| 108 |
|
| 109 |
<para> |
| 110 |
The following table summarizes which types of triggers may be used on |
| 111 |
tables, views, and foreign tables: |
| 112 |
</para> |
| 113 |
|
| 114 |
<informaltable id="supported-trigger-types"> |
| 115 |
<tgroup cols="4"> |
| 116 |
<thead> |
| 117 |
<row> |
| 118 |
<entry>When</entry> |
| 119 |
<entry>Event</entry> |
| 120 |
<entry>Row-level</entry> |
| 121 |
<entry>Statement-level</entry> |
| 122 |
</row> |
| 123 |
</thead> |
| 124 |
<tbody> |
| 125 |
<row> |
| 126 |
<entry align="center" morerows="1"><literal>BEFORE</literal></entry> |
| 127 |
<entry align="center"><command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command></entry> |
| 128 |
<entry align="center">Tables and foreign tables</entry> |
| 129 |
<entry align="center">Tables, views, and foreign tables</entry> |
| 130 |
</row> |
| 131 |
<row> |
| 132 |
<entry align="center"><command>TRUNCATE</command></entry> |
| 133 |
<entry align="center">—</entry> |
| 134 |
<entry align="center">Tables and foreign tables</entry> |
| 135 |
</row> |
| 136 |
<row> |
| 137 |
<entry align="center" morerows="1"><literal>AFTER</literal></entry> |
| 138 |
<entry align="center"><command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command></entry> |
| 139 |
<entry align="center">Tables and foreign tables</entry> |
| 140 |
<entry align="center">Tables, views, and foreign tables</entry> |
| 141 |
</row> |
| 142 |
<row> |
| 143 |
<entry align="center"><command>TRUNCATE</command></entry> |
| 144 |
<entry align="center">—</entry> |
| 145 |
<entry align="center">Tables and foreign tables</entry> |
| 146 |
</row> |
| 147 |
<row> |
| 148 |
<entry align="center" morerows="1"><literal>INSTEAD OF</literal></entry> |
| 149 |
<entry align="center"><command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command></entry> |
| 150 |
<entry align="center">Views</entry> |
| 151 |
<entry align="center">—</entry> |
| 152 |
</row> |
| 153 |
<row> |
| 154 |
<entry align="center"><command>TRUNCATE</command></entry> |
| 155 |
<entry align="center">—</entry> |
| 156 |
<entry align="center">—</entry> |
| 157 |
</row> |
| 158 |
</tbody> |
| 159 |
</tgroup> |
| 160 |
</informaltable> |
| 161 |
|
| 162 |
<para> |
| 163 |
Also, a trigger definition can specify a Boolean <literal>WHEN</literal> |
| 164 |
condition, which will be tested to see whether the trigger should |
| 165 |
be fired. In row-level triggers the <literal>WHEN</literal> condition can |
| 166 |
examine the old and/or new values of columns of the row. Statement-level |
| 167 |
triggers can also have <literal>WHEN</literal> conditions, although the feature |
| 168 |
is not so useful for them since the condition cannot refer to any values |
| 169 |
in the table. |
| 170 |
</para> |
| 171 |
|
| 172 |
<para> |
| 173 |
If multiple triggers of the same kind are defined for the same event, |
| 174 |
they will be fired in alphabetical order by name. |
| 175 |
</para> |
| 176 |
|
| 177 |
<para> |
| 178 |
When the <literal>CONSTRAINT</literal> option is specified, this command creates a |
| 179 |
<firstterm>constraint trigger</firstterm>.<indexterm><primary>trigger</primary> |
| 180 |
<secondary>constraint trigger</secondary></indexterm> |
| 181 |
This is the same as a regular trigger |
| 182 |
except that the timing of the trigger firing can be adjusted using |
| 183 |
<link linkend="sql-set-constraints"><command>SET CONSTRAINTS</command></link>. |
| 184 |
Constraint triggers must be <literal>AFTER ROW</literal> triggers on plain |
| 185 |
tables (not foreign tables). They |
| 186 |
can be fired either at the end of the statement causing the triggering |
| 187 |
event, or at the end of the containing transaction; in the latter case they |
| 188 |
are said to be <firstterm>deferred</firstterm>. A pending deferred-trigger firing |
| 189 |
can also be forced to happen immediately by using <command>SET |
| 190 |
CONSTRAINTS</command>. Constraint triggers are expected to raise an exception |
| 191 |
when the constraints they implement are violated. |
| 192 |
</para> |
| 193 |
|
| 194 |
<para> |
| 195 |
The <literal>REFERENCING</literal> option enables collection |
| 196 |
of <firstterm>transition relations</firstterm>, which are row sets that include all |
| 197 |
of the rows inserted, deleted, or modified by the current SQL statement. |
| 198 |
This feature lets the trigger see a global view of what the statement did, |
| 199 |
not just one row at a time. This option is only allowed for |
| 200 |
an <literal>AFTER</literal> trigger on a plain table (not a foreign table). |
| 201 |
The trigger should not be a constraint trigger. Also, if the trigger is |
| 202 |
an <literal>UPDATE</literal> trigger, it must not specify |
| 203 |
a <replaceable class="parameter">column_name</replaceable> list when using |
| 204 |
this option. |
| 205 |
<literal>OLD TABLE</literal> may only be specified once, and only for a trigger |
| 206 |
that can fire on <literal>UPDATE</literal> or <literal>DELETE</literal>; it creates a |
| 207 |
transition relation containing the <firstterm>before-images</firstterm> of all rows |
| 208 |
updated or deleted by the statement. |
| 209 |
Similarly, <literal>NEW TABLE</literal> may only be specified once, and only for |
| 210 |
a trigger that can fire on <literal>UPDATE</literal> or <literal>INSERT</literal>; |
| 211 |
it creates a transition relation containing the <firstterm>after-images</firstterm> |
| 212 |
of all rows updated or inserted by the statement. |
| 213 |
</para> |
| 214 |
|
| 215 |
<para> |
| 216 |
<command>SELECT</command> does not modify any rows so you cannot |
| 217 |
create <command>SELECT</command> triggers. Rules and views may provide |
| 218 |
workable solutions to problems that seem to need <command>SELECT</command> |
| 219 |
triggers. |
| 220 |
</para> |
| 221 |
|
| 222 |
<para> |
| 223 |
Refer to <xref linkend="triggers"/> for more information about triggers. |
| 224 |
</para> |
| 225 |
</refsect1> |
| 226 |
|
| 227 |
<refsect1> |
| 228 |
<title>Parameters</title> |
| 229 |
|
| 230 |
<variablelist> |
| 231 |
<varlistentry> |
| 232 |
<term><replaceable class="parameter">name</replaceable></term> |
| 233 |
<listitem> |
| 234 |
<para> |
| 235 |
The name to give the new trigger. This must be distinct from |
| 236 |
the name of any other trigger for the same table. |
| 237 |
The name cannot be schema-qualified — the trigger inherits the |
| 238 |
schema of its table. For a constraint trigger, this is also the name to |
| 239 |
use when modifying the trigger's behavior using |
| 240 |
<command>SET CONSTRAINTS</command>. |
| 241 |
</para> |
| 242 |
</listitem> |
| 243 |
</varlistentry> |
| 244 |
|
| 245 |
<varlistentry> |
| 246 |
<term><literal>BEFORE</literal></term> |
| 247 |
<term><literal>AFTER</literal></term> |
| 248 |
<term><literal>INSTEAD OF</literal></term> |
| 249 |
<listitem> |
| 250 |
<para> |
| 251 |
Determines whether the function is called before, after, or instead of |
| 252 |
the event. A constraint trigger can only be specified as |
| 253 |
<literal>AFTER</literal>. |
| 254 |
</para> |
| 255 |
</listitem> |
| 256 |
</varlistentry> |
| 257 |
|
| 258 |
<varlistentry> |
| 259 |
<term><replaceable class="parameter">event</replaceable></term> |
| 260 |
<listitem> |
| 261 |
<para> |
| 262 |
One of <literal>INSERT</literal>, <literal>UPDATE</literal>, |
| 263 |
<literal>DELETE</literal>, or <literal>TRUNCATE</literal>; |
| 264 |
this specifies the event that will fire the trigger. Multiple |
| 265 |
events can be specified using <literal>OR</literal>, except when |
| 266 |
transition relations are requested. |
| 267 |
</para> |
| 268 |
|
| 269 |
<para> |
| 270 |
For <literal>UPDATE</literal> events, it is possible to |
| 271 |
specify a list of columns using this syntax: |
| 272 |
<synopsis> |
| 273 |
UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</replaceable> ... ] |
| 274 |
</synopsis> |
| 275 |
The trigger will only fire if at least one of the listed columns |
| 276 |
is mentioned as a target of the <command>UPDATE</command> command |
| 277 |
or if one of the listed columns is a generated column that depends on a |
| 278 |
column that is the target of the <command>UPDATE</command>. |
| 279 |
</para> |
| 280 |
|
| 281 |
<para> |
| 282 |
<literal>INSTEAD OF UPDATE</literal> events do not allow a list of columns. |
| 283 |
A column list cannot be specified when requesting transition relations, |
| 284 |
either. |
| 285 |
</para> |
| 286 |
</listitem> |
| 287 |
</varlistentry> |
| 288 |
|
| 289 |
<varlistentry> |
| 290 |
<term><replaceable class="parameter">table_name</replaceable></term> |
| 291 |
<listitem> |
| 292 |
<para> |
| 293 |
The name (optionally schema-qualified) of the table, view, or foreign |
| 294 |
table the trigger is for. |
| 295 |
</para> |
| 296 |
</listitem> |
| 297 |
</varlistentry> |
| 298 |
|
| 299 |
<varlistentry> |
| 300 |
<term><replaceable class="parameter">referenced_table_name</replaceable></term> |
| 301 |
<listitem> |
| 302 |
<para> |
| 303 |
The (possibly schema-qualified) name of another table referenced by the |
| 304 |
constraint. This option is used for foreign-key constraints and is not |
| 305 |
recommended for general use. This can only be specified for |
| 306 |
constraint triggers. |
| 307 |
</para> |
| 308 |
</listitem> |
| 309 |
</varlistentry> |
| 310 |
|
| 311 |
<varlistentry> |
| 312 |
<term><literal>DEFERRABLE</literal></term> |
| 313 |
<term><literal>NOT DEFERRABLE</literal></term> |
| 314 |
<term><literal>INITIALLY IMMEDIATE</literal></term> |
| 315 |
<term><literal>INITIALLY DEFERRED</literal></term> |
| 316 |
<listitem> |
| 317 |
<para> |
| 318 |
The default timing of the trigger. |
| 319 |
See the <xref linkend="sql-createtable"/> documentation for details of |
| 320 |
these constraint options. This can only be specified for constraint |
| 321 |
triggers. |
| 322 |
</para> |
| 323 |
</listitem> |
| 324 |
</varlistentry> |
| 325 |
|
| 326 |
<varlistentry> |
| 327 |
<term><literal>REFERENCING</literal></term> |
| 328 |
<listitem> |
| 329 |
<para> |
| 330 |
This keyword immediately precedes the declaration of one or two |
| 331 |
relation names that provide access to the transition relations of the |
| 332 |
triggering statement. |
| 333 |
</para> |
| 334 |
</listitem> |
| 335 |
</varlistentry> |
| 336 |
|
| 337 |
<varlistentry> |
| 338 |
<term><literal>OLD TABLE</literal></term> |
| 339 |
<term><literal>NEW TABLE</literal></term> |
| 340 |
<listitem> |
| 341 |
<para> |
| 342 |
This clause indicates whether the following relation name is for the |
| 343 |
before-image transition relation or the after-image transition |
| 344 |
relation. |
| 345 |
</para> |
| 346 |
</listitem> |
| 347 |
</varlistentry> |
| 348 |
|
| 349 |
<varlistentry> |
| 350 |
<term><replaceable class="parameter">transition_relation_name</replaceable></term> |
| 351 |
<listitem> |
| 352 |
<para> |
| 353 |
The (unqualified) name to be used within the trigger for this |
| 354 |
transition relation. |
| 355 |
</para> |
| 356 |
</listitem> |
| 357 |
</varlistentry> |
| 358 |
|
| 359 |
<varlistentry> |
| 360 |
<term><literal>FOR EACH ROW</literal></term> |
| 361 |
<term><literal>FOR EACH STATEMENT</literal></term> |
| 362 |
|
| 363 |
<listitem> |
| 364 |
<para> |
| 365 |
This specifies whether the trigger function should be fired |
| 366 |
once for every row affected by the trigger event, or just once |
| 367 |
per SQL statement. If neither is specified, <literal>FOR EACH |
| 368 |
STATEMENT</literal> is the default. Constraint triggers can only |
| 369 |
be specified <literal>FOR EACH ROW</literal>. |
| 370 |
</para> |
| 371 |
</listitem> |
| 372 |
</varlistentry> |
| 373 |
|
| 374 |
<varlistentry> |
| 375 |
<term><replaceable class="parameter">condition</replaceable></term> |
| 376 |
<listitem> |
| 377 |
<para> |
| 378 |
A Boolean expression that determines whether the trigger function |
| 379 |
will actually be executed. If <literal>WHEN</literal> is specified, the |
| 380 |
function will only be called if the <replaceable |
| 381 |
class="parameter">condition</replaceable> returns <literal>true</literal>. |
| 382 |
In <literal>FOR EACH ROW</literal> triggers, the <literal>WHEN</literal> |
| 383 |
condition can refer to columns of the old and/or new row values |
| 384 |
by writing <literal>OLD.<replaceable |
| 385 |
class="parameter">column_name</replaceable></literal> or |
| 386 |
<literal>NEW.<replaceable |
| 387 |
class="parameter">column_name</replaceable></literal> respectively. |
| 388 |
Of course, <literal>INSERT</literal> triggers cannot refer to <literal>OLD</literal> |
| 389 |
and <literal>DELETE</literal> triggers cannot refer to <literal>NEW</literal>. |
| 390 |
</para> |
| 391 |
|
| 392 |
<para><literal>INSTEAD OF</literal> triggers do not support <literal>WHEN</literal> |
| 393 |
conditions. |
| 394 |
</para> |
| 395 |
|
| 396 |
<para> |
| 397 |
Currently, <literal>WHEN</literal> expressions cannot contain |
| 398 |
subqueries. |
| 399 |
</para> |
| 400 |
|
| 401 |
<para> |
| 402 |
Note that for constraint triggers, evaluation of the <literal>WHEN</literal> |
| 403 |
condition is not deferred, but occurs immediately after the row update |
| 404 |
operation is performed. If the condition does not evaluate to true then |
| 405 |
the trigger is not queued for deferred execution. |
| 406 |
</para> |
| 407 |
</listitem> |
| 408 |
</varlistentry> |
| 409 |
|
| 410 |
<varlistentry> |
| 411 |
<term><replaceable class="parameter">function_name</replaceable></term> |
| 412 |
<listitem> |
| 413 |
<para> |
| 414 |
A user-supplied function that is declared as taking no arguments |
| 415 |
and returning type <literal>trigger</literal>, which is executed when |
| 416 |
the trigger fires. |
| 417 |
</para> |
| 418 |
|
| 419 |
<para> |
| 420 |
In the syntax of <literal>CREATE TRIGGER</literal>, the keywords |
| 421 |
<literal>FUNCTION</literal> and <literal>PROCEDURE</literal> are |
| 422 |
equivalent, but the referenced function must in any case be a function, |
| 423 |
not a procedure. The use of the keyword <literal>PROCEDURE</literal> |
| 424 |
here is historical and deprecated. |
| 425 |
</para> |
| 426 |
</listitem> |
| 427 |
</varlistentry> |
| 428 |
|
| 429 |
<varlistentry> |
| 430 |
<term><replaceable class="parameter">arguments</replaceable></term> |
| 431 |
<listitem> |
| 432 |
<para> |
| 433 |
An optional comma-separated list of arguments to be provided to |
| 434 |
the function when the trigger is executed. The arguments are |
| 435 |
literal string constants. Simple names and numeric constants |
| 436 |
can be written here, too, but they will all be converted to |
| 437 |
strings. Please check the description of the implementation |
| 438 |
language of the trigger function to find out how these arguments |
| 439 |
can be accessed within the function; it might be different from |
| 440 |
normal function arguments. |
| 441 |
</para> |
| 442 |
</listitem> |
| 443 |
</varlistentry> |
| 444 |
</variablelist> |
| 445 |
</refsect1> |
| 446 |
|
| 447 |
<refsect1 id="sql-createtrigger-notes"> |
| 448 |
<title>Notes</title> |
| 449 |
|
| 450 |
<para> |
| 451 |
To create or replace a trigger on a table, the user must have the |
| 452 |
<literal>TRIGGER</literal> privilege on the table. The user must |
| 453 |
also have <literal>EXECUTE</literal> privilege on the trigger function. |
| 454 |
</para> |
| 455 |
|
| 456 |
<para> |
| 457 |
Use <link linkend="sql-droptrigger"><command>DROP TRIGGER</command></link> to remove a trigger. |
| 458 |
</para> |
| 459 |
|
| 460 |
<para> |
| 461 |
Creating a row-level trigger on a partitioned table will cause an |
| 462 |
identical <quote>clone</quote> trigger to be created on each of its |
| 463 |
existing partitions; and any partitions created or attached later will have |
| 464 |
an identical trigger, too. If there is a conflictingly-named trigger on a |
| 465 |
child partition already, an error occurs unless <command>CREATE OR REPLACE |
| 466 |
TRIGGER</command> is used, in which case that trigger is replaced with a |
| 467 |
clone trigger. When a partition is detached from its parent, its clone |
| 468 |
triggers are removed. |
| 469 |
</para> |
| 470 |
|
| 471 |
<para> |
| 472 |
A column-specific trigger (one defined using the <literal>UPDATE OF |
| 473 |
<replaceable>column_name</replaceable></literal> syntax) will fire when any |
| 474 |
of its columns are listed as targets in the <command>UPDATE</command> |
| 475 |
command's <literal>SET</literal> list. It is possible for a column's value |
| 476 |
to change even when the trigger is not fired, because changes made to the |
| 477 |
row's contents by <literal>BEFORE UPDATE</literal> triggers are not considered. |
| 478 |
Conversely, a command such as <literal>UPDATE ... SET x = x ...</literal> |
| 479 |
will fire a trigger on column <literal>x</literal>, even though the column's |
| 480 |
value did not change. |
| 481 |
</para> |
| 482 |
|
| 483 |
<para> |
| 484 |
In a <literal>BEFORE</literal> trigger, the <literal>WHEN</literal> condition is |
| 485 |
evaluated just before the function is or would be executed, so using |
| 486 |
<literal>WHEN</literal> is not materially different from testing the same |
| 487 |
condition at the beginning of the trigger function. Note in particular |
| 488 |
that the <literal>NEW</literal> row seen by the condition is the current value, |
| 489 |
as possibly modified by earlier triggers. Also, a <literal>BEFORE</literal> |
| 490 |
trigger's <literal>WHEN</literal> condition is not allowed to examine the |
| 491 |
system columns of the <literal>NEW</literal> row (such as <literal>ctid</literal>), |
| 492 |
because those won't have been set yet. |
| 493 |
</para> |
| 494 |
|
| 495 |
<para> |
| 496 |
In an <literal>AFTER</literal> trigger, the <literal>WHEN</literal> condition is |
| 497 |
evaluated just after the row update occurs, and it determines whether an |
| 498 |
event is queued to fire the trigger at the end of statement. So when an |
| 499 |
<literal>AFTER</literal> trigger's <literal>WHEN</literal> condition does not return |
| 500 |
true, it is not necessary to queue an event nor to re-fetch the row at end |
| 501 |
of statement. This can result in significant speedups in statements that |
| 502 |
modify many rows, if the trigger only needs to be fired for a few of the |
| 503 |
rows. |
| 504 |
</para> |
| 505 |
|
| 506 |
<para> |
| 507 |
In some cases it is possible for a single SQL command to fire more than |
| 508 |
one kind of trigger. For instance an <command>INSERT</command> with |
| 509 |
an <literal>ON CONFLICT DO UPDATE</literal> clause may cause both insert and |
| 510 |
update operations, so it will fire both kinds of triggers as needed. |
| 511 |
The transition relations supplied to triggers are |
| 512 |
specific to their event type; thus an <command>INSERT</command> trigger |
| 513 |
will see only the inserted rows, while an <command>UPDATE</command> |
| 514 |
trigger will see only the updated rows. |
| 515 |
</para> |
| 516 |
|
| 517 |
<para> |
| 518 |
Row updates or deletions caused by foreign-key enforcement actions, such |
| 519 |
as <literal>ON UPDATE CASCADE</literal> or <literal>ON DELETE SET NULL</literal>, are |
| 520 |
treated as part of the SQL command that caused them (note that such |
| 521 |
actions are never deferred). Relevant triggers on the affected table will |
| 522 |
be fired, so that this provides another way in which an SQL command might |
| 523 |
fire triggers not directly matching its type. In simple cases, triggers |
| 524 |
that request transition relations will see all changes caused in their |
| 525 |
table by a single original SQL command as a single transition relation. |
| 526 |
However, there are cases in which the presence of an <literal>AFTER ROW</literal> |
| 527 |
trigger that requests transition relations will cause the foreign-key |
| 528 |
enforcement actions triggered by a single SQL command to be split into |
| 529 |
multiple steps, each with its own transition relation(s). In such cases, |
| 530 |
any statement-level triggers that are present will be fired once per |
| 531 |
creation of a transition relation set, ensuring that the triggers see |
| 532 |
each affected row in a transition relation once and only once. |
| 533 |
</para> |
| 534 |
|
| 535 |
<para> |
| 536 |
Statement-level triggers on a view are fired only if the action on the |
| 537 |
view is handled by a row-level <literal>INSTEAD OF</literal> trigger. |
| 538 |
If the action is handled by an <literal>INSTEAD</literal> rule, then |
| 539 |
whatever statements are emitted by the rule are executed in place of the |
| 540 |
original statement naming the view, so that the triggers that will be |
| 541 |
fired are those on tables named in the replacement statements. |
| 542 |
Similarly, if the view is automatically updatable, then the action is |
| 543 |
handled by automatically rewriting the statement into an action on the |
| 544 |
view's base table, so that the base table's statement-level triggers are |
| 545 |
the ones that are fired. |
| 546 |
</para> |
| 547 |
|
| 548 |
<para> |
| 549 |
Modifying a partitioned table or a table with inheritance children fires |
| 550 |
statement-level triggers attached to the explicitly named table, but not |
| 551 |
statement-level triggers for its partitions or child tables. In contrast, |
| 552 |
row-level triggers are fired on the rows in affected partitions or |
| 553 |
child tables, even if they are not explicitly named in the query. |
| 554 |
If a statement-level trigger has been defined with transition relations |
| 555 |
named by a <literal>REFERENCING</literal> clause, then before and after |
| 556 |
images of rows are visible from all affected partitions or child tables. |
| 557 |
In the case of inheritance children, the row images include only columns |
| 558 |
that are present in the table that the trigger is attached to. |
| 559 |
</para> |
| 560 |
|
| 561 |
<para> |
| 562 |
Currently, row-level triggers with transition relations cannot be defined |
| 563 |
on partitions or inheritance child tables. Also, triggers on partitioned |
| 564 |
tables may not be <literal>INSTEAD OF</literal>. |
| 565 |
</para> |
| 566 |
|
| 567 |
<para> |
| 568 |
Currently, the <literal>OR REPLACE</literal> option is not supported for |
| 569 |
constraint triggers. |
| 570 |
</para> |
| 571 |
|
| 572 |
<para> |
| 573 |
Replacing an existing trigger within a transaction that has already |
| 574 |
performed updating actions on the trigger's table is not recommended. |
| 575 |
Trigger firing decisions, or portions of firing decisions, that have |
| 576 |
already been made will not be reconsidered, so the effects could be |
| 577 |
surprising. |
| 578 |
</para> |
| 579 |
|
| 580 |
<para> |
| 581 |
There are a few built-in trigger functions that can be used to |
| 582 |
solve common problems without having to write your own trigger code; |
| 583 |
see <xref linkend="functions-trigger"/>. |
| 584 |
</para> |
| 585 |
</refsect1> |
| 586 |
|
| 587 |
<refsect1 id="sql-createtrigger-examples"> |
| 588 |
<title>Examples</title> |
| 589 |
|
| 590 |
<para> |
| 591 |
Execute the function <function>check_account_update</function> whenever |
| 592 |
a row of the table <literal>accounts</literal> is about to be updated: |
| 593 |
|
| 594 |
<programlisting> |
| 595 |
CREATE TRIGGER check_update |
| 596 |
BEFORE UPDATE ON accounts |
| 597 |
FOR EACH ROW |
| 598 |
EXECUTE FUNCTION check_account_update(); |
| 599 |
</programlisting> |
| 600 |
|
| 601 |
Modify that trigger definition to only execute the function if |
| 602 |
column <literal>balance</literal> is specified as a target in |
| 603 |
the <command>UPDATE</command> command: |
| 604 |
|
| 605 |
<programlisting> |
| 606 |
CREATE OR REPLACE TRIGGER check_update |
| 607 |
BEFORE UPDATE OF balance ON accounts |
| 608 |
FOR EACH ROW |
| 609 |
EXECUTE FUNCTION check_account_update(); |
| 610 |
</programlisting> |
| 611 |
|
| 612 |
This form only executes the function if column <literal>balance</literal> |
| 613 |
has in fact changed value: |
| 614 |
|
| 615 |
<programlisting> |
| 616 |
CREATE TRIGGER check_update |
| 617 |
BEFORE UPDATE ON accounts |
| 618 |
FOR EACH ROW |
| 619 |
WHEN (OLD.balance IS DISTINCT FROM NEW.balance) |
| 620 |
EXECUTE FUNCTION check_account_update(); |
| 621 |
</programlisting> |
| 622 |
|
| 623 |
Call a function to log updates of <literal>accounts</literal>, but only if |
| 624 |
something changed: |
| 625 |
|
| 626 |
<programlisting> |
| 627 |
CREATE TRIGGER log_update |
| 628 |
AFTER UPDATE ON accounts |
| 629 |
FOR EACH ROW |
| 630 |
WHEN (OLD.* IS DISTINCT FROM NEW.*) |
| 631 |
EXECUTE FUNCTION log_account_update(); |
| 632 |
</programlisting> |
| 633 |
|
| 634 |
Execute the function <function>view_insert_row</function> for each row to insert |
| 635 |
rows into the tables underlying a view: |
| 636 |
|
| 637 |
<programlisting> |
| 638 |
CREATE TRIGGER view_insert |
| 639 |
INSTEAD OF INSERT ON my_view |
| 640 |
FOR EACH ROW |
| 641 |
EXECUTE FUNCTION view_insert_row(); |
| 642 |
</programlisting> |
| 643 |
|
| 644 |
Execute the function <function>check_transfer_balances_to_zero</function> for each |
| 645 |
statement to confirm that the <literal>transfer</literal> rows offset to a net of |
| 646 |
zero: |
| 647 |
|
| 648 |
<programlisting> |
| 649 |
CREATE TRIGGER transfer_insert |
| 650 |
AFTER INSERT ON transfer |
| 651 |
REFERENCING NEW TABLE AS inserted |
| 652 |
FOR EACH STATEMENT |
| 653 |
EXECUTE FUNCTION check_transfer_balances_to_zero(); |
| 654 |
</programlisting> |
| 655 |
|
| 656 |
Execute the function <function>check_matching_pairs</function> for each row to |
| 657 |
confirm that changes are made to matching pairs at the same time (by the |
| 658 |
same statement): |
| 659 |
|
| 660 |
<programlisting> |
| 661 |
CREATE TRIGGER paired_items_update |
| 662 |
AFTER UPDATE ON paired_items |
| 663 |
REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab |
| 664 |
FOR EACH ROW |
| 665 |
EXECUTE FUNCTION check_matching_pairs(); |
| 666 |
</programlisting> |
| 667 |
</para> |
| 668 |
|
| 669 |
<para> |
| 670 |
<xref linkend="trigger-example"/> contains a complete example of a trigger |
| 671 |
function written in C. |
| 672 |
</para> |
| 673 |
</refsect1> |
| 674 |
|
| 675 |
<refsect1 id="sql-createtrigger-compatibility"> |
| 676 |
<title>Compatibility</title> |
| 677 |
|
| 678 |
<!-- |
| 679 |
It's not clear whether SQL/MED contemplates triggers on foreign tables. |
| 680 |
Its <drop basic column definition> General Rules do mention the possibility |
| 681 |
of a reference from a trigger column list. On the other hand, nothing |
| 682 |
overrides the fact that CREATE TRIGGER only targets base tables. For now, |
| 683 |
do not document the compatibility status of triggers on foreign tables. |
| 684 |
--> |
| 685 |
|
| 686 |
<para> |
| 687 |
The <command>CREATE TRIGGER</command> statement in |
| 688 |
<productname>PostgreSQL</productname> implements a subset of the |
| 689 |
<acronym>SQL</acronym> standard. The following functionalities are currently |
| 690 |
missing: |
| 691 |
|
| 692 |
<itemizedlist> |
| 693 |
<listitem> |
| 694 |
<para> |
| 695 |
While transition table names for <literal>AFTER</literal> triggers are |
| 696 |
specified using the <literal>REFERENCING</literal> clause in the standard way, |
| 697 |
the row variables used in <literal>FOR EACH ROW</literal> triggers may not be |
| 698 |
specified in a <literal>REFERENCING</literal> clause. They are available in a |
| 699 |
manner that is dependent on the language in which the trigger function |
| 700 |
is written, but is fixed for any one language. Some languages |
| 701 |
effectively behave as though there is a <literal>REFERENCING</literal> clause |
| 702 |
containing <literal>OLD ROW AS OLD NEW ROW AS NEW</literal>. |
| 703 |
</para> |
| 704 |
</listitem> |
| 705 |
|
| 706 |
<listitem> |
| 707 |
<para> |
| 708 |
The standard allows transition tables to be used with |
| 709 |
column-specific <literal>UPDATE</literal> triggers, but then the set of rows |
| 710 |
that should be visible in the transition tables depends on the |
| 711 |
trigger's column list. This is not currently implemented by |
| 712 |
<productname>PostgreSQL</productname>. |
| 713 |
</para> |
| 714 |
</listitem> |
| 715 |
|
| 716 |
<listitem> |
| 717 |
<para> |
| 718 |
<productname>PostgreSQL</productname> only allows the execution |
| 719 |
of a user-defined function for the triggered action. The standard |
| 720 |
allows the execution of a number of other SQL commands, such as |
| 721 |
<command>CREATE TABLE</command>, as the triggered action. This |
| 722 |
limitation is not hard to work around by creating a user-defined |
| 723 |
function that executes the desired commands. |
| 724 |
</para> |
| 725 |
</listitem> |
| 726 |
|
| 727 |
</itemizedlist> |
| 728 |
</para> |
| 729 |
|
| 730 |
<para> |
| 731 |
SQL specifies that multiple triggers should be fired in |
| 732 |
time-of-creation order. <productname>PostgreSQL</productname> uses |
| 733 |
name order, which was judged to be more convenient. |
| 734 |
</para> |
| 735 |
|
| 736 |
<para> |
| 737 |
SQL specifies that <literal>BEFORE DELETE</literal> triggers on cascaded |
| 738 |
deletes fire <emphasis>after</emphasis> the cascaded <literal>DELETE</literal> completes. |
| 739 |
The <productname>PostgreSQL</productname> behavior is for <literal>BEFORE |
| 740 |
DELETE</literal> to always fire before the delete action, even a cascading |
| 741 |
one. This is considered more consistent. There is also nonstandard |
| 742 |
behavior if <literal>BEFORE</literal> triggers modify rows or prevent |
| 743 |
updates during an update that is caused by a referential action. This can |
| 744 |
lead to constraint violations or stored data that does not honor the |
| 745 |
referential constraint. |
| 746 |
</para> |
| 747 |
|
| 748 |
<para> |
| 749 |
The ability to specify multiple actions for a single trigger using |
| 750 |
<literal>OR</literal> is a <productname>PostgreSQL</productname> extension of |
| 751 |
the SQL standard. |
| 752 |
</para> |
| 753 |
|
| 754 |
<para> |
| 755 |
The ability to fire triggers for <command>TRUNCATE</command> is a |
| 756 |
<productname>PostgreSQL</productname> extension of the SQL standard, as is the |
| 757 |
ability to define statement-level triggers on views. |
| 758 |
</para> |
| 759 |
|
| 760 |
<para> |
| 761 |
<command>CREATE CONSTRAINT TRIGGER</command> is a |
| 762 |
<productname>PostgreSQL</productname> extension of the <acronym>SQL</acronym> |
| 763 |
standard. |
| 764 |
So is the <literal>OR REPLACE</literal> option. |
| 765 |
</para> |
| 766 |
|
| 767 |
</refsect1> |
| 768 |
|
| 769 |
<refsect1> |
| 770 |
<title>See Also</title> |
| 771 |
|
| 772 |
<simplelist type="inline"> |
| 773 |
<member><xref linkend="sql-altertrigger"/></member> |
| 774 |
<member><xref linkend="sql-droptrigger"/></member> |
| 775 |
<member><xref linkend="sql-createfunction"/></member> |
| 776 |
<member><xref linkend="sql-set-constraints"/></member> |
| 777 |
</simplelist> |
| 778 |
</refsect1> |
| 779 |
</refentry> |