codekingpro/portable-devtools
115k
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 16.3 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">3CREATE [ 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> )11 12<span class="phrase">where <em class="replaceable"><code>event</code></em> can be one of:</span>13 14 INSERT15 UPDATE [ OF <em class="replaceable"><code>column_name</code></em> [, ... ] ]16 DELETE17 TRUNCATE18</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 a21 new trigger, or replace an existing trigger. The22 trigger will be associated with the specified table, view, or foreign table23 and will execute the specified24 function <em class="replaceable"><code>function_name</code></em> when25 certain operations are performed on that table.26 </p><p>27 To replace the current definition of an existing trigger, use28 <code class="command">CREATE OR REPLACE TRIGGER</code>, specifying the existing29 trigger's name and parent table. All other properties are replaced.30 </p><p>31 The trigger can be specified to fire before the32 operation is attempted on a row (before constraints are checked and33 the <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or34 <code class="command">DELETE</code> is attempted); or after the operation has35 completed (after constraints are checked and the36 <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or37 <code class="command">DELETE</code> has completed); or instead of the operation38 (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 skip40 the operation for the current row, or change the row being inserted (for41 <code class="command">INSERT</code> and <code class="command">UPDATE</code> operations42 only). If the trigger fires after the event, all changes, including43 the effects of other triggers, are <span class="quote">“<span class="quote">visible</span>”</span>44 to the trigger.45 </p><p>46 A trigger that is marked <code class="literal">FOR EACH ROW</code> is called47 once for every row that the operation modifies. For example, a48 <code class="command">DELETE</code> that affects 10 rows will cause any49 <code class="literal">ON DELETE</code> triggers on the target relation to be50 called 10 separate times, once for each deleted row. In contrast, a51 trigger that is marked <code class="literal">FOR EACH STATEMENT</code> only52 executes once for any given operation, regardless of how many rows53 it modifies (in particular, an operation that modifies zero rows54 will still result in the execution of any applicable <code class="literal">FOR55 EACH STATEMENT</code> triggers).56 </p><p>57 Triggers that are specified to fire <code class="literal">INSTEAD OF</code> the trigger58 event must be marked <code class="literal">FOR EACH ROW</code>, and can only be defined59 on views. <code class="literal">BEFORE</code> and <code class="literal">AFTER</code> triggers on a view60 must be marked as <code class="literal">FOR EACH STATEMENT</code>.61 </p><p>62 In addition, triggers may be defined to fire for63 <code class="command">TRUNCATE</code>, though only64 <code class="literal">FOR EACH STATEMENT</code>.65 </p><p>66 The following table summarizes which types of triggers may be used on67 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 should71 be fired. In row-level triggers the <code class="literal">WHEN</code> condition can72 examine the old and/or new values of columns of the row. Statement-level73 triggers can also have <code class="literal">WHEN</code> conditions, although the feature74 is not so useful for them since the condition cannot refer to any values75 in the table.76 </p><p>77 If multiple triggers of the same kind are defined for the same event,78 they will be fired in alphabetical order by name.79 </p><p>80 When the <code class="literal">CONSTRAINT</code> option is specified, this command creates a81 <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 trigger83 except that the timing of the trigger firing can be adjusted using84 <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 plain86 tables (not foreign tables). They87 can be fired either at the end of the statement causing the triggering88 event, or at the end of the containing transaction; in the latter case they89 are said to be <em class="firstterm">deferred</em>. A pending deferred-trigger firing90 can also be forced to happen immediately by using <code class="command">SET91 CONSTRAINTS</code>. Constraint triggers are expected to raise an exception92 when the constraints they implement are violated.93 </p><p>94 The <code class="literal">REFERENCING</code> option enables collection95 of <em class="firstterm">transition relations</em>, which are row sets that include all96 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 for99 an <code class="literal">AFTER</code> trigger that is not a constraint trigger; also, if100 the trigger is an <code class="literal">UPDATE</code> trigger, it must not specify101 a <em class="replaceable"><code>column_name</code></em> list.102 <code class="literal">OLD TABLE</code> may only be specified once, and only for a trigger103 that can fire on <code class="literal">UPDATE</code> or <code class="literal">DELETE</code>; it creates a104 transition relation containing the <em class="firstterm">before-images</em> of all rows105 updated or deleted by the statement.106 Similarly, <code class="literal">NEW TABLE</code> may only be specified once, and only for107 a trigger that can fire on <code class="literal">UPDATE</code> or <code class="literal">INSERT</code>;108 it creates a transition relation containing the <em class="firstterm">after-images</em>109 of all rows updated or inserted by the statement.110 </p><p>111 <code class="command">SELECT</code> does not modify any rows so you cannot112 create <code class="command">SELECT</code> triggers. Rules and views may provide113 workable solutions to problems that seem to need <code class="command">SELECT</code>114 triggers.115 </p><p>116 Refer to <a class="xref" href="triggers.html" title="Chapter 39. Triggers">Chapter 39</a> for more information about triggers.117 </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>118 The name to give the new trigger. This must be distinct from119 the name of any other trigger for the same table.120 The name cannot be schema-qualified — the trigger inherits the121 schema of its table. For a constraint trigger, this is also the name to122 use when modifying the trigger's behavior using123 <code class="command">SET CONSTRAINTS</code>.124 </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>125 Determines whether the function is called before, after, or instead of126 the event. A constraint trigger can only be specified as127 <code class="literal">AFTER</code>.128 </p></dd><dt><span class="term"><em class="replaceable"><code>event</code></em></span></dt><dd><p>129 One of <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>,130 <code class="literal">DELETE</code>, or <code class="literal">TRUNCATE</code>;131 this specifies the event that will fire the trigger. Multiple132 events can be specified using <code class="literal">OR</code>, except when133 transition relations are requested.134 </p><p>135 For <code class="literal">UPDATE</code> events, it is possible to136 specify a list of columns using this syntax:137</p><pre class="synopsis">138UPDATE OF <em class="replaceable"><code>column_name1</code></em> [, <em class="replaceable"><code>column_name2</code></em> ... ]139</pre><p>140 The trigger will only fire if at least one of the listed columns141 is mentioned as a target of the <code class="command">UPDATE</code> command142 or if one of the listed columns is a generated column that depends on a143 column that is the target of the <code class="command">UPDATE</code>.144 </p><p>145 <code class="literal">INSTEAD OF UPDATE</code> events do not allow a list of columns.146 A column list cannot be specified when requesting transition relations,147 either.148 </p></dd><dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt><dd><p>149 The name (optionally schema-qualified) of the table, view, or foreign150 table the trigger is for.151 </p></dd><dt><span class="term"><em class="replaceable"><code>referenced_table_name</code></em></span></dt><dd><p>152 The (possibly schema-qualified) name of another table referenced by the153 constraint. This option is used for foreign-key constraints and is not154 recommended for general use. This can only be specified for155 constraint triggers.156 </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>157 The default timing of the trigger.158 See the <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a> documentation for details of159 these constraint options. This can only be specified for constraint160 triggers.161 </p></dd><dt><span class="term"><code class="literal">REFERENCING</code></span></dt><dd><p>162 This keyword immediately precedes the declaration of one or two163 relation names that provide access to the transition relations of the164 triggering statement.165 </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>166 This clause indicates whether the following relation name is for the167 before-image transition relation or the after-image transition168 relation.169 </p></dd><dt><span class="term"><em class="replaceable"><code>transition_relation_name</code></em></span></dt><dd><p>170 The (unqualified) name to be used within the trigger for this171 transition relation.172 </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>173 This specifies whether the trigger function should be fired174 once for every row affected by the trigger event, or just once175 per SQL statement. If neither is specified, <code class="literal">FOR EACH176 STATEMENT</code> is the default. Constraint triggers can only177 be specified <code class="literal">FOR EACH ROW</code>.178 </p></dd><dt><span class="term"><em class="replaceable"><code>condition</code></em></span></dt><dd><p>179 A Boolean expression that determines whether the trigger function180 will actually be executed. If <code class="literal">WHEN</code> is specified, the181 function will only be called if the <em class="replaceable"><code>condition</code></em> returns <code class="literal">true</code>.182 In <code class="literal">FOR EACH ROW</code> triggers, the <code class="literal">WHEN</code>183 condition can refer to columns of the old and/or new row values184 by writing <code class="literal">OLD.<em class="replaceable"><code>column_name</code></em></code> or185 <code class="literal">NEW.<em class="replaceable"><code>column_name</code></em></code> respectively.186 Of course, <code class="literal">INSERT</code> triggers cannot refer to <code class="literal">OLD</code>187 and <code class="literal">DELETE</code> triggers cannot refer to <code class="literal">NEW</code>.188 </p><p><code class="literal">INSTEAD OF</code> triggers do not support <code class="literal">WHEN</code>189 conditions.190 </p><p>191 Currently, <code class="literal">WHEN</code> expressions cannot contain192 subqueries.193 </p><p>194 Note that for constraint triggers, evaluation of the <code class="literal">WHEN</code>195 condition is not deferred, but occurs immediately after the row update196 operation is performed. If the condition does not evaluate to true then197 the trigger is not queued for deferred execution.198 </p></dd><dt><span class="term"><em class="replaceable"><code>function_name</code></em></span></dt><dd><p>199 A user-supplied function that is declared as taking no arguments200 and returning type <code class="literal">trigger</code>, which is executed when201 the trigger fires.202 </p><p>203 In the syntax of <code class="literal">CREATE TRIGGER</code>, the keywords204 <code class="literal">FUNCTION</code> and <code class="literal">PROCEDURE</code> are205 equivalent, but the referenced function must in any case be a function,206 not a procedure. The use of the keyword <code class="literal">PROCEDURE</code>207 here is historical and deprecated.208 </p></dd><dt><span class="term"><em class="replaceable"><code>arguments</code></em></span></dt><dd><p>209 An optional comma-separated list of arguments to be provided to210 the function when the trigger is executed. The arguments are211 literal string constants. Simple names and numeric constants212 can be written here, too, but they will all be converted to213 strings. Please check the description of the implementation214 language of the trigger function to find out how these arguments215 can be accessed within the function; it might be different from216 normal function arguments.217 </p></dd></dl></div></div><div class="refsect1" id="SQL-CREATETRIGGER-NOTES"><h2>Notes</h2><p>218 To create or replace a trigger on a table, the user must have the219 <code class="literal">TRIGGER</code> privilege on the table. The user must220 also have <code class="literal">EXECUTE</code> privilege on the trigger function.221 </p><p>222 Use <a class="link" href="sql-droptrigger.html" title="DROP TRIGGER"><code class="command">DROP TRIGGER</code></a> to remove a trigger.223 </p><p>224 Creating a row-level trigger on a partitioned table will cause an225 identical <span class="quote">“<span class="quote">clone</span>”</span> trigger to be created on each of its226 existing partitions; and any partitions created or attached later will have227 an identical trigger, too. If there is a conflictingly-named trigger on a228 child partition already, an error occurs unless <code class="command">CREATE OR REPLACE229 TRIGGER</code> is used, in which case that trigger is replaced with a230 clone trigger. When a partition is detached from its parent, its clone231 triggers are removed.232 </p><p>233 A column-specific trigger (one defined using the <code class="literal">UPDATE OF234 <em class="replaceable"><code>column_name</code></em></code> syntax) will fire when any235 of its columns are listed as targets in the <code class="command">UPDATE</code>236 command's <code class="literal">SET</code> list. It is possible for a column's value237 to change even when the trigger is not fired, because changes made to the238 row's contents by <code class="literal">BEFORE UPDATE</code> triggers are not considered.239 Conversely, a command such as <code class="literal">UPDATE ... SET x = x ...</code>240 will fire a trigger on column <code class="literal">x</code>, even though the column's241 value did not change.242 </p><p>243 In a <code class="literal">BEFORE</code> trigger, the <code class="literal">WHEN</code> condition is244 evaluated just before the function is or would be executed, so using245 <code class="literal">WHEN</code> is not materially different from testing the same246 condition at the beginning of the trigger function. Note in particular247 that the <code class="literal">NEW</code> row seen by the condition is the current value,248 as possibly modified by earlier triggers. Also, a <code class="literal">BEFORE</code>249 trigger's <code class="literal">WHEN</code> condition is not allowed to examine the250 system columns of the <code class="literal">NEW</code> row (such as <code class="literal">ctid</code>),251 because those won't have been set yet.252 </p><p>253 In an <code class="literal">AFTER</code> trigger, the <code class="literal">WHEN</code> condition is254 evaluated just after the row update occurs, and it determines whether an255 event is queued to fire the trigger at the end of statement. So when an256 <code class="literal">AFTER</code> trigger's <code class="literal">WHEN</code> condition does not return257 true, it is not necessary to queue an event nor to re-fetch the row at end258 of statement. This can result in significant speedups in statements that259 modify many rows, if the trigger only needs to be fired for a few of the260 rows.261 </p><p>262 In some cases it is possible for a single SQL command to fire more than263 one kind of trigger. For instance an <code class="command">INSERT</code> with264 an <code class="literal">ON CONFLICT DO UPDATE</code> clause may cause both insert and265 update operations, so it will fire both kinds of triggers as needed.266 The transition relations supplied to triggers are267 specific to their event type; thus an <code class="command">INSERT</code> trigger268 will see only the inserted rows, while an <code class="command">UPDATE</code>269 trigger will see only the updated rows.270 </p><p>271 Row updates or deletions caused by foreign-key enforcement actions, such272 as <code class="literal">ON UPDATE CASCADE</code> or <code class="literal">ON DELETE SET NULL</code>, are273 treated as part of the SQL command that caused them (note that such274 actions are never deferred). Relevant triggers on the affected table will275 be fired, so that this provides another way in which an SQL command might276 fire triggers not directly matching its type. In simple cases, triggers277 that request transition relations will see all changes caused in their278 table by a single original SQL command as a single transition relation.279 However, there are cases in which the presence of an <code class="literal">AFTER ROW</code>280 trigger that requests transition relations will cause the foreign-key281 enforcement actions triggered by a single SQL command to be split into282 multiple steps, each with its own transition relation(s). In such cases,283 any statement-level triggers that are present will be fired once per284 creation of a transition relation set, ensuring that the triggers see285 each affected row in a transition relation once and only once.286 </p><p>287 Statement-level triggers on a view are fired only if the action on the288 view is handled by a row-level <code class="literal">INSTEAD OF</code> trigger.289 If the action is handled by an <code class="literal">INSTEAD</code> rule, then290 whatever statements are emitted by the rule are executed in place of the291 original statement naming the view, so that the triggers that will be292 fired are those on tables named in the replacement statements.293 Similarly, if the view is automatically updatable, then the action is294 handled by automatically rewriting the statement into an action on the295 view's base table, so that the base table's statement-level triggers are296 the ones that are fired.297 </p><p>298 Modifying a partitioned table or a table with inheritance children fires299 statement-level triggers attached to the explicitly named table, but not300 statement-level triggers for its partitions or child tables. In contrast,301 row-level triggers are fired on the rows in affected partitions or302 child tables, even if they are not explicitly named in the query.303 If a statement-level trigger has been defined with transition relations304 named by a <code class="literal">REFERENCING</code> clause, then before and after305 images of rows are visible from all affected partitions or child tables.306 In the case of inheritance children, the row images include only columns307 that are present in the table that the trigger is attached to.308 </p><p>309 Currently, row-level triggers with transition relations cannot be defined310 on partitions or inheritance child tables. Also, triggers on partitioned311 tables may not be <code class="literal">INSTEAD OF</code>.312 </p><p>313 Currently, the <code class="literal">OR REPLACE</code> option is not supported for314 constraint triggers.315 </p><p>316 Replacing an existing trigger within a transaction that has already317 performed updating actions on the trigger's table is not recommended.318 Trigger firing decisions, or portions of firing decisions, that have319 already been made will not be reconsidered, so the effects could be320 surprising.321 </p><p>322 There are a few built-in trigger functions that can be used to323 solve common problems without having to write your own trigger code;324 see <a class="xref" href="functions-trigger.html" title="9.28. Trigger Functions">Section 9.28</a>.325 </p></div><div class="refsect1" id="SQL-CREATETRIGGER-EXAMPLES"><h2>Examples</h2><p>326 Execute the function <code class="function">check_account_update</code> whenever327 a row of the table <code class="literal">accounts</code> is about to be updated:328 329</p><pre class="programlisting">330CREATE TRIGGER check_update331 BEFORE UPDATE ON accounts332 FOR EACH ROW333 EXECUTE FUNCTION check_account_update();334</pre><p>335 336 Modify that trigger definition to only execute the function if337 column <code class="literal">balance</code> is specified as a target in338 the <code class="command">UPDATE</code> command:339 340</p><pre class="programlisting">341CREATE OR REPLACE TRIGGER check_update342 BEFORE UPDATE OF balance ON accounts343 FOR EACH ROW344 EXECUTE FUNCTION check_account_update();345</pre><p>346 347 This form only executes the function if column <code class="literal">balance</code>348 has in fact changed value:349 350</p><pre class="programlisting">351CREATE TRIGGER check_update352 BEFORE UPDATE ON accounts353 FOR EACH ROW354 WHEN (OLD.balance IS DISTINCT FROM NEW.balance)355 EXECUTE FUNCTION check_account_update();356</pre><p>357 358 Call a function to log updates of <code class="literal">accounts</code>, but only if359 something changed:360 361</p><pre class="programlisting">362CREATE TRIGGER log_update363 AFTER UPDATE ON accounts364 FOR EACH ROW365 WHEN (OLD.* IS DISTINCT FROM NEW.*)366 EXECUTE FUNCTION log_account_update();367</pre><p>368 369 Execute the function <code class="function">view_insert_row</code> for each row to insert370 rows into the tables underlying a view:371 372</p><pre class="programlisting">373CREATE TRIGGER view_insert374 INSTEAD OF INSERT ON my_view375 FOR EACH ROW376 EXECUTE FUNCTION view_insert_row();377</pre><p>378 379 Execute the function <code class="function">check_transfer_balances_to_zero</code> for each380 statement to confirm that the <code class="literal">transfer</code> rows offset to a net of381 zero:382 383</p><pre class="programlisting">384CREATE TRIGGER transfer_insert385 AFTER INSERT ON transfer386 REFERENCING NEW TABLE AS inserted387 FOR EACH STATEMENT388 EXECUTE FUNCTION check_transfer_balances_to_zero();389</pre><p>390 391 Execute the function <code class="function">check_matching_pairs</code> for each row to392 confirm that changes are made to matching pairs at the same time (by the393 same statement):394 395</p><pre class="programlisting">396CREATE TRIGGER paired_items_update397 AFTER UPDATE ON paired_items398 REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab399 FOR EACH ROW400 EXECUTE FUNCTION check_matching_pairs();401</pre><p>402 </p><p>403 <a class="xref" href="trigger-example.html" title="39.4. A Complete Trigger Example">Section 39.4</a> contains a complete example of a trigger404 function written in C.405 </p></div><div class="refsect1" id="SQL-CREATETRIGGER-COMPATIBILITY"><h2>Compatibility</h2><p>406 The <code class="command">CREATE TRIGGER</code> statement in407 <span class="productname">PostgreSQL</span> implements a subset of the408 <acronym class="acronym">SQL</acronym> standard. The following functionalities are currently409 missing:410 411 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>412 While transition table names for <code class="literal">AFTER</code> triggers are413 specified using the <code class="literal">REFERENCING</code> clause in the standard way,414 the row variables used in <code class="literal">FOR EACH ROW</code> triggers may not be415 specified in a <code class="literal">REFERENCING</code> clause. They are available in a416 manner that is dependent on the language in which the trigger function417 is written, but is fixed for any one language. Some languages418 effectively behave as though there is a <code class="literal">REFERENCING</code> clause419 containing <code class="literal">OLD ROW AS OLD NEW ROW AS NEW</code>.420 </p></li><li class="listitem"><p>421 The standard allows transition tables to be used with422 column-specific <code class="literal">UPDATE</code> triggers, but then the set of rows423 that should be visible in the transition tables depends on the424 trigger's column list. This is not currently implemented by425 <span class="productname">PostgreSQL</span>.426 </p></li><li class="listitem"><p>427 <span class="productname">PostgreSQL</span> only allows the execution428 of a user-defined function for the triggered action. The standard429 allows the execution of a number of other SQL commands, such as430 <code class="command">CREATE TABLE</code>, as the triggered action. This431 limitation is not hard to work around by creating a user-defined432 function that executes the desired commands.433 </p></li></ul></div><p>434 </p><p>435 SQL specifies that multiple triggers should be fired in436 time-of-creation order. <span class="productname">PostgreSQL</span> uses437 name order, which was judged to be more convenient.438 </p><p>439 SQL specifies that <code class="literal">BEFORE DELETE</code> triggers on cascaded440 deletes fire <span class="emphasis"><em>after</em></span> the cascaded <code class="literal">DELETE</code> completes.441 The <span class="productname">PostgreSQL</span> behavior is for <code class="literal">BEFORE442 DELETE</code> to always fire before the delete action, even a cascading443 one. This is considered more consistent. There is also nonstandard444 behavior if <code class="literal">BEFORE</code> triggers modify rows or prevent445 updates during an update that is caused by a referential action. This can446 lead to constraint violations or stored data that does not honor the447 referential constraint.448 </p><p>449 The ability to specify multiple actions for a single trigger using450 <code class="literal">OR</code> is a <span class="productname">PostgreSQL</span> extension of451 the SQL standard.452 </p><p>453 The ability to fire triggers for <code class="command">TRUNCATE</code> is a454 <span class="productname">PostgreSQL</span> extension of the SQL standard, as is the455 ability to define statement-level triggers on views.456 </p><p>457 <code class="command">CREATE CONSTRAINT TRIGGER</code> is a458 <span class="productname">PostgreSQL</span> extension of the <acronym class="acronym">SQL</acronym>459 standard.460 So is the <code class="literal">OR REPLACE</code> option.461 </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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE TYPE</td></tr></table></div></body></html>