1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE TRIGGER</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createtransform.html" title="CREATE TRANSFORM" /><link rel="next" href="sql-createtype.html" title="CREATE TYPE" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE TRIGGER</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createtransform.html" title="CREATE TRANSFORM">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createtype.html" title="CREATE TYPE">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATETRIGGER"><div class="titlepage"></div><a id="id-1.9.3.93.1" class="indexterm"></a><a id="id-1.9.3.93.2" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE TRIGGER</span></h2><p>CREATE TRIGGER — define a new trigger</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
3 CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <em class="replaceable"><code>name</code></em> { BEFORE | AFTER | INSTEAD OF } { <em class="replaceable"><code>event</code></em> [ OR ... ] }
4 ON <em class="replaceable"><code>table_name</code></em>
5 [ FROM <em class="replaceable"><code>referenced_table_name</code></em> ]
6 [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
7 [ REFERENCING { { OLD | NEW } TABLE [ AS ] <em class="replaceable"><code>transition_relation_name</code></em> } [ ... ] ]
8 [ FOR [ EACH ] { ROW | STATEMENT } ]
9 [ WHEN ( <em class="replaceable"><code>condition</code></em> ) ]
10 EXECUTE { FUNCTION | PROCEDURE } <em class="replaceable"><code>function_name</code></em> ( <em class="replaceable"><code>arguments</code></em> )
12 <span class="phrase">where <em class="replaceable"><code>event</code></em> can be one of:</span>
15 UPDATE [ OF <em class="replaceable"><code>column_name</code></em> [, ... ] ]
18 </pre></div><div class="refsect1" id="id-1.9.3.93.6"><h2>Description</h2><p>
19 <code class="command">CREATE TRIGGER</code> creates a new trigger.
20 <code class="command">CREATE OR REPLACE TRIGGER</code> will either create a
21 new trigger, or replace an existing trigger. The
22 trigger will be associated with the specified table, view, or foreign table
23 and will execute the specified
24 function <em class="replaceable"><code>function_name</code></em> when
25 certain operations are performed on that table.
27 To replace the current definition of an existing trigger, use
28 <code class="command">CREATE OR REPLACE TRIGGER</code>, specifying the existing
29 trigger's name and parent table. All other properties are replaced.
31 The trigger can be specified to fire before the
32 operation is attempted on a row (before constraints are checked and
33 the <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or
34 <code class="command">DELETE</code> is attempted); or after the operation has
35 completed (after constraints are checked and the
36 <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or
37 <code class="command">DELETE</code> has completed); or instead of the operation
38 (in the case of inserts, updates or deletes on a view).
39 If the trigger fires before or instead of the event, the trigger can skip
40 the operation for the current row, or change the row being inserted (for
41 <code class="command">INSERT</code> and <code class="command">UPDATE</code> operations
42 only). If the trigger fires after the event, all changes, including
43 the effects of other triggers, are <span class="quote">“<span class="quote">visible</span>”</span>
46 A trigger that is marked <code class="literal">FOR EACH ROW</code> is called
47 once for every row that the operation modifies. For example, a
48 <code class="command">DELETE</code> that affects 10 rows will cause any
49 <code class="literal">ON DELETE</code> triggers on the target relation to be
50 called 10 separate times, once for each deleted row. In contrast, a
51 trigger that is marked <code class="literal">FOR EACH STATEMENT</code> only
52 executes once for any given operation, regardless of how many rows
53 it modifies (in particular, an operation that modifies zero rows
54 will still result in the execution of any applicable <code class="literal">FOR
55 EACH STATEMENT</code> triggers).
57 Triggers that are specified to fire <code class="literal">INSTEAD OF</code> the trigger
58 event must be marked <code class="literal">FOR EACH ROW</code>, and can only be defined
59 on views. <code class="literal">BEFORE</code> and <code class="literal">AFTER</code> triggers on a view
60 must be marked as <code class="literal">FOR EACH STATEMENT</code>.
62 In addition, triggers may be defined to fire for
63 <code class="command">TRUNCATE</code>, though only
64 <code class="literal">FOR EACH STATEMENT</code>.
66 The following table summarizes which types of triggers may be used on
67 tables, views, and foreign tables:
68 </p><div class="informaltable" id="SUPPORTED-TRIGGER-TYPES"><table class="informaltable" border="1"><colgroup><col /><col /><col /><col /></colgroup><thead><tr><th>When</th><th>Event</th><th>Row-level</th><th>Statement-level</th></tr></thead><tbody><tr><td rowspan="2" align="center"><code class="literal">BEFORE</code></td><td align="center"><code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code></td><td align="center">Tables and foreign tables</td><td align="center">Tables, views, and foreign tables</td></tr><tr><td align="center"><code class="command">TRUNCATE</code></td><td align="center">—</td><td align="center">Tables and foreign tables</td></tr><tr><td rowspan="2" align="center"><code class="literal">AFTER</code></td><td align="center"><code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code></td><td align="center">Tables and foreign tables</td><td align="center">Tables, views, and foreign tables</td></tr><tr><td align="center"><code class="command">TRUNCATE</code></td><td align="center">—</td><td align="center">Tables and foreign tables</td></tr><tr><td rowspan="2" align="center"><code class="literal">INSTEAD OF</code></td><td align="center"><code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code></td><td align="center">Views</td><td align="center">—</td></tr><tr><td align="center"><code class="command">TRUNCATE</code></td><td align="center">—</td><td align="center">—</td></tr></tbody></table></div><p>
69 Also, a trigger definition can specify a Boolean <code class="literal">WHEN</code>
70 condition, which will be tested to see whether the trigger should
71 be fired. In row-level triggers the <code class="literal">WHEN</code> condition can
72 examine the old and/or new values of columns of the row. Statement-level
73 triggers can also have <code class="literal">WHEN</code> conditions, although the feature
74 is not so useful for them since the condition cannot refer to any values
77 If multiple triggers of the same kind are defined for the same event,
78 they will be fired in alphabetical order by name.
80 When the <code class="literal">CONSTRAINT</code> option is specified, this command creates a
81 <em class="firstterm">constraint trigger</em>.<a id="id-1.9.3.93.6.12.3" class="indexterm"></a>
82 This is the same as a regular trigger
83 except that the timing of the trigger firing can be adjusted using
84 <a class="link" href="sql-set-constraints.html" title="SET CONSTRAINTS"><code class="command">SET CONSTRAINTS</code></a>.
85 Constraint triggers must be <code class="literal">AFTER ROW</code> triggers on plain
86 tables (not foreign tables). They
87 can be fired either at the end of the statement causing the triggering
88 event, or at the end of the containing transaction; in the latter case they
89 are said to be <em class="firstterm">deferred</em>. A pending deferred-trigger firing
90 can also be forced to happen immediately by using <code class="command">SET
91 CONSTRAINTS</code>. Constraint triggers are expected to raise an exception
92 when the constraints they implement are violated.
94 The <code class="literal">REFERENCING</code> option enables collection
95 of <em class="firstterm">transition relations</em>, which are row sets that include all
96 of the rows inserted, deleted, or modified by the current SQL statement.
97 This feature lets the trigger see a global view of what the statement did,
98 not just one row at a time. This option is only allowed for
99 an <code class="literal">AFTER</code> trigger on a plain table (not a foreign table).
100 The trigger should not be a constraint trigger. Also, if the trigger is
101 an <code class="literal">UPDATE</code> trigger, it must not specify
102 a <em class="replaceable"><code>column_name</code></em> list when using
104 <code class="literal">OLD TABLE</code> may only be specified once, and only for a trigger
105 that can fire on <code class="literal">UPDATE</code> or <code class="literal">DELETE</code>; it creates a
106 transition relation containing the <em class="firstterm">before-images</em> of all rows
107 updated or deleted by the statement.
108 Similarly, <code class="literal">NEW TABLE</code> may only be specified once, and only for
109 a trigger that can fire on <code class="literal">UPDATE</code> or <code class="literal">INSERT</code>;
110 it creates a transition relation containing the <em class="firstterm">after-images</em>
111 of all rows updated or inserted by the statement.
113 <code class="command">SELECT</code> does not modify any rows so you cannot
114 create <code class="command">SELECT</code> triggers. Rules and views may provide
115 workable solutions to problems that seem to need <code class="command">SELECT</code>
118 Refer to <a class="xref" href="triggers.html" title="Chapter 37. Triggers">Chapter 37</a> for more information about triggers.
119 </p></div><div class="refsect1" id="id-1.9.3.93.7"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>
120 The name to give the new trigger. This must be distinct from
121 the name of any other trigger for the same table.
122 The name cannot be schema-qualified — the trigger inherits the
123 schema of its table. For a constraint trigger, this is also the name to
124 use when modifying the trigger's behavior using
125 <code class="command">SET CONSTRAINTS</code>.
126 </p></dd><dt><span class="term"><code class="literal">BEFORE</code><br /></span><span class="term"><code class="literal">AFTER</code><br /></span><span class="term"><code class="literal">INSTEAD OF</code></span></dt><dd><p>
127 Determines whether the function is called before, after, or instead of
128 the event. A constraint trigger can only be specified as
129 <code class="literal">AFTER</code>.
130 </p></dd><dt><span class="term"><em class="replaceable"><code>event</code></em></span></dt><dd><p>
131 One of <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>,
132 <code class="literal">DELETE</code>, or <code class="literal">TRUNCATE</code>;
133 this specifies the event that will fire the trigger. Multiple
134 events can be specified using <code class="literal">OR</code>, except when
135 transition relations are requested.
137 For <code class="literal">UPDATE</code> events, it is possible to
138 specify a list of columns using this syntax:
139 </p><pre class="synopsis">
140 UPDATE OF <em class="replaceable"><code>column_name1</code></em> [, <em class="replaceable"><code>column_name2</code></em> ... ]
142 The trigger will only fire if at least one of the listed columns
143 is mentioned as a target of the <code class="command">UPDATE</code> command
144 or if one of the listed columns is a generated column that depends on a
145 column that is the target of the <code class="command">UPDATE</code>.
147 <code class="literal">INSTEAD OF UPDATE</code> events do not allow a list of columns.
148 A column list cannot be specified when requesting transition relations,
150 </p></dd><dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt><dd><p>
151 The name (optionally schema-qualified) of the table, view, or foreign
152 table the trigger is for.
153 </p></dd><dt><span class="term"><em class="replaceable"><code>referenced_table_name</code></em></span></dt><dd><p>
154 The (possibly schema-qualified) name of another table referenced by the
155 constraint. This option is used for foreign-key constraints and is not
156 recommended for general use. This can only be specified for
158 </p></dd><dt><span class="term"><code class="literal">DEFERRABLE</code><br /></span><span class="term"><code class="literal">NOT DEFERRABLE</code><br /></span><span class="term"><code class="literal">INITIALLY IMMEDIATE</code><br /></span><span class="term"><code class="literal">INITIALLY DEFERRED</code></span></dt><dd><p>
159 The default timing of the trigger.
160 See the <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a> documentation for details of
161 these constraint options. This can only be specified for constraint
163 </p></dd><dt><span class="term"><code class="literal">REFERENCING</code></span></dt><dd><p>
164 This keyword immediately precedes the declaration of one or two
165 relation names that provide access to the transition relations of the
166 triggering statement.
167 </p></dd><dt><span class="term"><code class="literal">OLD TABLE</code><br /></span><span class="term"><code class="literal">NEW TABLE</code></span></dt><dd><p>
168 This clause indicates whether the following relation name is for the
169 before-image transition relation or the after-image transition
171 </p></dd><dt><span class="term"><em class="replaceable"><code>transition_relation_name</code></em></span></dt><dd><p>
172 The (unqualified) name to be used within the trigger for this
174 </p></dd><dt><span class="term"><code class="literal">FOR EACH ROW</code><br /></span><span class="term"><code class="literal">FOR EACH STATEMENT</code></span></dt><dd><p>
175 This specifies whether the trigger function should be fired
176 once for every row affected by the trigger event, or just once
177 per SQL statement. If neither is specified, <code class="literal">FOR EACH
178 STATEMENT</code> is the default. Constraint triggers can only
179 be specified <code class="literal">FOR EACH ROW</code>.
180 </p></dd><dt><span class="term"><em class="replaceable"><code>condition</code></em></span></dt><dd><p>
181 A Boolean expression that determines whether the trigger function
182 will actually be executed. If <code class="literal">WHEN</code> is specified, the
183 function will only be called if the <em class="replaceable"><code>condition</code></em> returns <code class="literal">true</code>.
184 In <code class="literal">FOR EACH ROW</code> triggers, the <code class="literal">WHEN</code>
185 condition can refer to columns of the old and/or new row values
186 by writing <code class="literal">OLD.<em class="replaceable"><code>column_name</code></em></code> or
187 <code class="literal">NEW.<em class="replaceable"><code>column_name</code></em></code> respectively.
188 Of course, <code class="literal">INSERT</code> triggers cannot refer to <code class="literal">OLD</code>
189 and <code class="literal">DELETE</code> triggers cannot refer to <code class="literal">NEW</code>.
190 </p><p><code class="literal">INSTEAD OF</code> triggers do not support <code class="literal">WHEN</code>
193 Currently, <code class="literal">WHEN</code> expressions cannot contain
196 Note that for constraint triggers, evaluation of the <code class="literal">WHEN</code>
197 condition is not deferred, but occurs immediately after the row update
198 operation is performed. If the condition does not evaluate to true then
199 the trigger is not queued for deferred execution.
200 </p></dd><dt><span class="term"><em class="replaceable"><code>function_name</code></em></span></dt><dd><p>
201 A user-supplied function that is declared as taking no arguments
202 and returning type <code class="literal">trigger</code>, which is executed when
205 In the syntax of <code class="literal">CREATE TRIGGER</code>, the keywords
206 <code class="literal">FUNCTION</code> and <code class="literal">PROCEDURE</code> are
207 equivalent, but the referenced function must in any case be a function,
208 not a procedure. The use of the keyword <code class="literal">PROCEDURE</code>
209 here is historical and deprecated.
210 </p></dd><dt><span class="term"><em class="replaceable"><code>arguments</code></em></span></dt><dd><p>
211 An optional comma-separated list of arguments to be provided to
212 the function when the trigger is executed. The arguments are
213 literal string constants. Simple names and numeric constants
214 can be written here, too, but they will all be converted to
215 strings. Please check the description of the implementation
216 language of the trigger function to find out how these arguments
217 can be accessed within the function; it might be different from
218 normal function arguments.
219 </p></dd></dl></div></div><div class="refsect1" id="SQL-CREATETRIGGER-NOTES"><h2>Notes</h2><p>
220 To create or replace a trigger on a table, the user must have the
221 <code class="literal">TRIGGER</code> privilege on the table. The user must
222 also have <code class="literal">EXECUTE</code> privilege on the trigger function.
224 Use <a class="link" href="sql-droptrigger.html" title="DROP TRIGGER"><code class="command">DROP TRIGGER</code></a> to remove a trigger.
226 Creating a row-level trigger on a partitioned table will cause an
227 identical <span class="quote">“<span class="quote">clone</span>”</span> trigger to be created on each of its
228 existing partitions; and any partitions created or attached later will have
229 an identical trigger, too. If there is a conflictingly-named trigger on a
230 child partition already, an error occurs unless <code class="command">CREATE OR REPLACE
231 TRIGGER</code> is used, in which case that trigger is replaced with a
232 clone trigger. When a partition is detached from its parent, its clone
233 triggers are removed.
235 A column-specific trigger (one defined using the <code class="literal">UPDATE OF
236 <em class="replaceable"><code>column_name</code></em></code> syntax) will fire when any
237 of its columns are listed as targets in the <code class="command">UPDATE</code>
238 command's <code class="literal">SET</code> list. It is possible for a column's value
239 to change even when the trigger is not fired, because changes made to the
240 row's contents by <code class="literal">BEFORE UPDATE</code> triggers are not considered.
241 Conversely, a command such as <code class="literal">UPDATE ... SET x = x ...</code>
242 will fire a trigger on column <code class="literal">x</code>, even though the column's
243 value did not change.
245 In a <code class="literal">BEFORE</code> trigger, the <code class="literal">WHEN</code> condition is
246 evaluated just before the function is or would be executed, so using
247 <code class="literal">WHEN</code> is not materially different from testing the same
248 condition at the beginning of the trigger function. Note in particular
249 that the <code class="literal">NEW</code> row seen by the condition is the current value,
250 as possibly modified by earlier triggers. Also, a <code class="literal">BEFORE</code>
251 trigger's <code class="literal">WHEN</code> condition is not allowed to examine the
252 system columns of the <code class="literal">NEW</code> row (such as <code class="literal">ctid</code>),
253 because those won't have been set yet.
255 In an <code class="literal">AFTER</code> trigger, the <code class="literal">WHEN</code> condition is
256 evaluated just after the row update occurs, and it determines whether an
257 event is queued to fire the trigger at the end of statement. So when an
258 <code class="literal">AFTER</code> trigger's <code class="literal">WHEN</code> condition does not return
259 true, it is not necessary to queue an event nor to re-fetch the row at end
260 of statement. This can result in significant speedups in statements that
261 modify many rows, if the trigger only needs to be fired for a few of the
264 In some cases it is possible for a single SQL command to fire more than
265 one kind of trigger. For instance an <code class="command">INSERT</code> with
266 an <code class="literal">ON CONFLICT DO UPDATE</code> clause may cause both insert and
267 update operations, so it will fire both kinds of triggers as needed.
268 The transition relations supplied to triggers are
269 specific to their event type; thus an <code class="command">INSERT</code> trigger
270 will see only the inserted rows, while an <code class="command">UPDATE</code>
271 trigger will see only the updated rows.
273 Row updates or deletions caused by foreign-key enforcement actions, such
274 as <code class="literal">ON UPDATE CASCADE</code> or <code class="literal">ON DELETE SET NULL</code>, are
275 treated as part of the SQL command that caused them (note that such
276 actions are never deferred). Relevant triggers on the affected table will
277 be fired, so that this provides another way in which an SQL command might
278 fire triggers not directly matching its type. In simple cases, triggers
279 that request transition relations will see all changes caused in their
280 table by a single original SQL command as a single transition relation.
281 However, there are cases in which the presence of an <code class="literal">AFTER ROW</code>
282 trigger that requests transition relations will cause the foreign-key
283 enforcement actions triggered by a single SQL command to be split into
284 multiple steps, each with its own transition relation(s). In such cases,
285 any statement-level triggers that are present will be fired once per
286 creation of a transition relation set, ensuring that the triggers see
287 each affected row in a transition relation once and only once.
289 Statement-level triggers on a view are fired only if the action on the
290 view is handled by a row-level <code class="literal">INSTEAD OF</code> trigger.
291 If the action is handled by an <code class="literal">INSTEAD</code> rule, then
292 whatever statements are emitted by the rule are executed in place of the
293 original statement naming the view, so that the triggers that will be
294 fired are those on tables named in the replacement statements.
295 Similarly, if the view is automatically updatable, then the action is
296 handled by automatically rewriting the statement into an action on the
297 view's base table, so that the base table's statement-level triggers are
298 the ones that are fired.
300 Modifying a partitioned table or a table with inheritance children fires
301 statement-level triggers attached to the explicitly named table, but not
302 statement-level triggers for its partitions or child tables. In contrast,
303 row-level triggers are fired on the rows in affected partitions or
304 child tables, even if they are not explicitly named in the query.
305 If a statement-level trigger has been defined with transition relations
306 named by a <code class="literal">REFERENCING</code> clause, then before and after
307 images of rows are visible from all affected partitions or child tables.
308 In the case of inheritance children, the row images include only columns
309 that are present in the table that the trigger is attached to.
311 Currently, row-level triggers with transition relations cannot be defined
312 on partitions or inheritance child tables. Also, triggers on partitioned
313 tables may not be <code class="literal">INSTEAD OF</code>.
315 Currently, the <code class="literal">OR REPLACE</code> option is not supported for
318 Replacing an existing trigger within a transaction that has already
319 performed updating actions on the trigger's table is not recommended.
320 Trigger firing decisions, or portions of firing decisions, that have
321 already been made will not be reconsidered, so the effects could be
324 There are a few built-in trigger functions that can be used to
325 solve common problems without having to write your own trigger code;
326 see <a class="xref" href="functions-trigger.html" title="9.29. Trigger Functions">Section 9.29</a>.
327 </p></div><div class="refsect1" id="SQL-CREATETRIGGER-EXAMPLES"><h2>Examples</h2><p>
328 Execute the function <code class="function">check_account_update</code> whenever
329 a row of the table <code class="literal">accounts</code> is about to be updated:
331 </p><pre class="programlisting">
332 CREATE TRIGGER check_update
333 BEFORE UPDATE ON accounts
335 EXECUTE FUNCTION check_account_update();
338 Modify that trigger definition to only execute the function if
339 column <code class="literal">balance</code> is specified as a target in
340 the <code class="command">UPDATE</code> command:
342 </p><pre class="programlisting">
343 CREATE OR REPLACE TRIGGER check_update
344 BEFORE UPDATE OF balance ON accounts
346 EXECUTE FUNCTION check_account_update();
349 This form only executes the function if column <code class="literal">balance</code>
350 has in fact changed value:
352 </p><pre class="programlisting">
353 CREATE TRIGGER check_update
354 BEFORE UPDATE ON accounts
356 WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
357 EXECUTE FUNCTION check_account_update();
360 Call a function to log updates of <code class="literal">accounts</code>, but only if
363 </p><pre class="programlisting">
364 CREATE TRIGGER log_update
365 AFTER UPDATE ON accounts
367 WHEN (OLD.* IS DISTINCT FROM NEW.*)
368 EXECUTE FUNCTION log_account_update();
371 Execute the function <code class="function">view_insert_row</code> for each row to insert
372 rows into the tables underlying a view:
374 </p><pre class="programlisting">
375 CREATE TRIGGER view_insert
376 INSTEAD OF INSERT ON my_view
378 EXECUTE FUNCTION view_insert_row();
381 Execute the function <code class="function">check_transfer_balances_to_zero</code> for each
382 statement to confirm that the <code class="literal">transfer</code> rows offset to a net of
385 </p><pre class="programlisting">
386 CREATE TRIGGER transfer_insert
387 AFTER INSERT ON transfer
388 REFERENCING NEW TABLE AS inserted
390 EXECUTE FUNCTION check_transfer_balances_to_zero();
393 Execute the function <code class="function">check_matching_pairs</code> for each row to
394 confirm that changes are made to matching pairs at the same time (by the
397 </p><pre class="programlisting">
398 CREATE TRIGGER paired_items_update
399 AFTER UPDATE ON paired_items
400 REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
402 EXECUTE FUNCTION check_matching_pairs();
405 <a class="xref" href="trigger-example.html" title="37.4. A Complete Trigger Example">Section 37.4</a> contains a complete example of a trigger
406 function written in C.
407 </p></div><div class="refsect1" id="SQL-CREATETRIGGER-COMPATIBILITY"><h2>Compatibility</h2><p>
408 The <code class="command">CREATE TRIGGER</code> statement in
409 <span class="productname">PostgreSQL</span> implements a subset of the
410 <acronym class="acronym">SQL</acronym> standard. The following functionalities are currently
413 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
414 While transition table names for <code class="literal">AFTER</code> triggers are
415 specified using the <code class="literal">REFERENCING</code> clause in the standard way,
416 the row variables used in <code class="literal">FOR EACH ROW</code> triggers may not be
417 specified in a <code class="literal">REFERENCING</code> clause. They are available in a
418 manner that is dependent on the language in which the trigger function
419 is written, but is fixed for any one language. Some languages
420 effectively behave as though there is a <code class="literal">REFERENCING</code> clause
421 containing <code class="literal">OLD ROW AS OLD NEW ROW AS NEW</code>.
422 </p></li><li class="listitem"><p>
423 The standard allows transition tables to be used with
424 column-specific <code class="literal">UPDATE</code> triggers, but then the set of rows
425 that should be visible in the transition tables depends on the
426 trigger's column list. This is not currently implemented by
427 <span class="productname">PostgreSQL</span>.
428 </p></li><li class="listitem"><p>
429 <span class="productname">PostgreSQL</span> only allows the execution
430 of a user-defined function for the triggered action. The standard
431 allows the execution of a number of other SQL commands, such as
432 <code class="command">CREATE TABLE</code>, as the triggered action. This
433 limitation is not hard to work around by creating a user-defined
434 function that executes the desired commands.
435 </p></li></ul></div><p>
437 SQL specifies that multiple triggers should be fired in
438 time-of-creation order. <span class="productname">PostgreSQL</span> uses
439 name order, which was judged to be more convenient.
441 SQL specifies that <code class="literal">BEFORE DELETE</code> triggers on cascaded
442 deletes fire <span class="emphasis"><em>after</em></span> the cascaded <code class="literal">DELETE</code> completes.
443 The <span class="productname">PostgreSQL</span> behavior is for <code class="literal">BEFORE
444 DELETE</code> to always fire before the delete action, even a cascading
445 one. This is considered more consistent. There is also nonstandard
446 behavior if <code class="literal">BEFORE</code> triggers modify rows or prevent
447 updates during an update that is caused by a referential action. This can
448 lead to constraint violations or stored data that does not honor the
449 referential constraint.
451 The ability to specify multiple actions for a single trigger using
452 <code class="literal">OR</code> is a <span class="productname">PostgreSQL</span> extension of
455 The ability to fire triggers for <code class="command">TRUNCATE</code> is a
456 <span class="productname">PostgreSQL</span> extension of the SQL standard, as is the
457 ability to define statement-level triggers on views.
459 <code class="command">CREATE CONSTRAINT TRIGGER</code> is a
460 <span class="productname">PostgreSQL</span> extension of the <acronym class="acronym">SQL</acronym>
462 So is the <code class="literal">OR REPLACE</code> option.
463 </p></div><div class="refsect1" id="id-1.9.3.93.11"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-altertrigger.html" title="ALTER TRIGGER"><span class="refentrytitle">ALTER TRIGGER</span></a>, <a class="xref" href="sql-droptrigger.html" title="DROP TRIGGER"><span class="refentrytitle">DROP TRIGGER</span></a>, <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a>, <a class="xref" href="sql-set-constraints.html" title="SET CONSTRAINTS"><span class="refentrytitle">SET CONSTRAINTS</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createtransform.html" title="CREATE TRANSFORM">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createtype.html" title="CREATE TYPE">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE TRANSFORM </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE TYPE</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob