Movatterモバイル変換

A trigger is a specification that the database should automatically execute a particular function whenever a certain type of operation is performed. Triggers can be attached to tables (partitioned or not), views, and foreign tables.

On tables and foreign tables, triggers can be defined to execute either before or after anyINSERT,UPDATE, orDELETE operation, either once per modified row, or once perSQL statement.UPDATE triggers can moreover be set to fire only if certain columns are mentioned in theSET clause of theUPDATE statement. Triggers can also fire forTRUNCATE statements. If a trigger event occurs, the trigger's function is called at the appropriate time to handle the event.

On views, triggers can be defined to execute instead ofINSERT,UPDATE, orDELETE operations. SuchINSTEAD OF triggers are fired once for each row that needs to be modified in the view. It is the responsibility of the trigger's function to perform the necessary modifications to the view's underlying base table(s) and, where appropriate, return the modified row as it will appear in the view. Triggers on views can also be defined to execute once perSQL statement, before or afterINSERT,UPDATE, orDELETE operations. However, such triggers are fired only if there is also anINSTEAD OF trigger on the view. Otherwise, any statement targeting the view must be rewritten into a statement affecting its underlying base table(s), and then the triggers that will be fired are the ones attached to the base table(s).

The trigger function must be defined before the trigger itself can be created. The trigger function must be declared as a function taking no arguments and returning typetrigger. (The trigger function receives its input through a specially-passedTriggerData structure, not in the form of ordinary function arguments.)

Once a suitable trigger function has been created, the trigger is established withCREATE TRIGGER. The same trigger function can be used for multiple triggers.

PostgreSQL offers bothper-row triggers andper-statement triggers. With a per-row trigger, the trigger function is invoked once for each row that is affected by the statement that fired the trigger. In contrast, a per-statement trigger is invoked only once when an appropriate statement is executed, regardless of the number of rows affected by that statement. In particular, a statement that affects zero rows will still result in the execution of any applicable per-statement triggers. These two types of triggers are sometimes calledrow-level triggers andstatement-level triggers, respectively. Triggers onTRUNCATE may only be defined at statement level, not per-row.

Triggers are also classified according to whether they firebefore,after, orinstead of the operation. These are referred to asBEFORE triggers,AFTER triggers, andINSTEAD OF triggers respectively. Statement-levelBEFORE triggers naturally fire before the statement starts to do anything, while statement-levelAFTER triggers fire at the very end of the statement. These types of triggers may be defined on tables, views, or foreign tables. Row-levelBEFORE triggers fire immediately before a particular row is operated on, while row-levelAFTER triggers fire at the end of the statement (but before any statement-levelAFTER triggers). These types of triggers may only be defined on tables and foreign tables, not views;BEFORE row-level triggers may not be defined on partitioned tables.INSTEAD OF triggers may only be defined on views, and only at row level; they fire immediately as each row in the view is identified as needing to be operated on.

A statement that targets a parent table in an inheritance or partitioning hierarchy does not cause the statement-level triggers of affected child tables to be fired; only the parent table's statement-level triggers are fired. However, row-level triggers of any affected child tables will be fired.

If anINSERT contains anON CONFLICT DO UPDATE clause, it is possible that the effects of row-levelBEFOREINSERT triggers and row-levelBEFOREUPDATE triggers can both be applied in a way that is apparent from the final state of the updated row, if anEXCLUDED column is referenced. There need not be anEXCLUDED column reference for both sets of row-levelBEFORE triggers to execute, though. The possibility of surprising outcomes should be considered when there are bothBEFOREINSERT andBEFOREUPDATE row-level triggers that change a row being inserted/updated (this can be problematic even if the modifications are more or less equivalent, if they're not also idempotent). Note that statement-levelUPDATE triggers are executed whenON CONFLICT DO UPDATE is specified, regardless of whether or not any rows were affected by theUPDATE (and regardless of whether the alternativeUPDATE path was ever taken). AnINSERT with anON CONFLICT DO UPDATE clause will execute statement-levelBEFOREINSERT triggers first, then statement-levelBEFOREUPDATE triggers, followed by statement-levelAFTERUPDATE triggers and finally statement-levelAFTERINSERT triggers.

If anUPDATE on a partitioned table causes a row to move to another partition, it will be performed as aDELETE from the original partition followed by anINSERT into the new partition. In this case, all row-levelBEFOREUPDATE triggers and all row-levelBEFOREDELETE triggers are fired on the original partition. Then all row-levelBEFOREINSERT triggers are fired on the destination partition. The possibility of surprising outcomes should be considered when all these triggers affect the row being moved. As far asAFTER ROW triggers are concerned,AFTERDELETE andAFTERINSERT triggers are applied; butAFTERUPDATE triggers are not applied because theUPDATE has been converted to aDELETE and anINSERT. As far as statement-level triggers are concerned, none of theDELETE orINSERT triggers are fired, even if row movement occurs; only theUPDATE triggers defined on the target table used in theUPDATE statement will be fired.

Trigger functions invoked by per-statement triggers should always returnNULL. Trigger functions invoked by per-row triggers can return a table row (a value of typeHeapTuple) to the calling executor, if they choose. A row-level trigger fired before an operation has the following choices:

A row-levelBEFORE trigger that does not intend to cause either of these behaviors must be careful to return as its result the same row that was passed in (that is, theNEW row forINSERT andUPDATE triggers, theOLD row forDELETE triggers).

A row-levelINSTEAD OF trigger should either returnNULL to indicate that it did not modify any data from the view's underlying base tables, or it should return the view row that was passed in (theNEW row forINSERT andUPDATE operations, or theOLD row forDELETE operations). A nonnull return value is used to signal that the trigger performed the necessary data modifications in the view. This will cause the count of the number of rows affected by the command to be incremented. ForINSERT andUPDATE operations only, the trigger may modify theNEW row before returning it. This will change the data returned byINSERT RETURNING orUPDATE RETURNING, and is useful when the view will not show exactly the same data that was provided.

The return value is ignored for row-level triggers fired after an operation, and so they can returnNULL.

If more than one trigger is defined for the same event on the same relation, the triggers will be fired in alphabetical order by trigger name. In the case ofBEFORE andINSTEAD OF triggers, the possibly-modified row returned by each trigger becomes the input to the next trigger. If anyBEFORE orINSTEAD OF trigger returnsNULL, the operation is abandoned for that row and subsequent triggers are not fired (for that row).

A trigger definition can also specify a BooleanWHEN condition, which will be tested to see whether the trigger should be fired. In row-level triggers theWHEN condition can examine the old and/or new values of columns of the row. (Statement-level triggers can also haveWHEN conditions, although the feature is not so useful for them.) In aBEFORE trigger, theWHEN condition is evaluated just before the function is or would be executed, so usingWHEN is not materially different from testing the same condition at the beginning of the trigger function. However, in anAFTER trigger, theWHEN condition is evaluated just after the row update occurs, and it determines whether an event is queued to fire the trigger at the end of statement. So when anAFTER trigger'sWHEN condition does not return true, it is not necessary to queue an event nor to re-fetch the row at end of statement. This can result in significant speedups in statements that modify many rows, if the trigger only needs to be fired for a few of the rows.INSTEAD OF triggers do not supportWHEN conditions.

Typically, row-levelBEFORE triggers are used for checking or modifying the data that will be inserted or updated. For example, aBEFORE trigger might be used to insert the current time into atimestamp column, or to check that two elements of the row are consistent. Row-levelAFTER triggers are most sensibly used to propagate the updates to other tables, or make consistency checks against other tables. The reason for this division of labor is that anAFTER trigger can be certain it is seeing the final value of the row, while aBEFORE trigger cannot; there might be otherBEFORE triggers firing after it. If you have no specific reason to make a triggerBEFORE orAFTER, theBEFORE case is more efficient, since the information about the operation doesn't have to be saved until end of statement.

If a trigger function executes SQL commands then these commands might fire triggers again. This is known as cascading triggers. There is no direct limitation on the number of cascade levels. It is possible for cascades to cause a recursive invocation of the same trigger; for example, anINSERT trigger might execute a command that inserts an additional row into the same table, causing theINSERT trigger to be fired again. It is the trigger programmer's responsibility to avoid infinite recursion in such scenarios.

When a trigger is being defined, arguments can be specified for it. The purpose of including arguments in the trigger definition is to allow different triggers with similar requirements to call the same function. As an example, there could be a generalized trigger function that takes as its arguments two column names and puts the current user in one and the current time stamp in the other. Properly written, this trigger function would be independent of the specific table it is triggering on. So the same function could be used forINSERT events on any table with suitable columns, to automatically track creation of records in a transaction table for example. It could also be used to track last-update events if defined as anUPDATE trigger.

Each programming language that supports triggers has its own method for making the trigger input data available to the trigger function. This input data includes the type of trigger event (e.g.,INSERT orUPDATE) as well as any arguments that were listed inCREATE TRIGGER. For a row-level trigger, the input data also includes theNEW row forINSERT andUPDATE triggers, and/or theOLD row forUPDATE andDELETE triggers.

By default, statement-level triggers do not have any way to examine the individual row(s) modified by the statement. But anAFTER STATEMENT trigger can request thattransition tables be created to make the sets of affected rows available to the trigger.AFTER ROW triggers can also request transition tables, so that they can see the total changes in the table as well as the change in the individual row they are currently being fired for. The method for examining the transition tables again depends on the programming language that is being used, but the typical approach is to make the transition tables act like read-only temporary tables that can be accessed by SQL commands issued within the trigger function.