Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
sql-createtrigger.html461 linesDownload Raw Back to html
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>
codekingpro/portable-devtools · Team Ai