From a4678320471380e5159a8d6e89466d74d6ee1739 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 18 Mar 2018 15:10:28 -0400 Subject: [PATCH] Doc: note that statement-level view triggers require an INSTEAD OF trigger. If a view lacks an INSTEAD OF trigger, DML on it can only work by rewriting the command into a command on the underlying base table(s). Then we will fire triggers attached to those table(s), not those for the view. This seems appropriate from a consistency standpoint, but nowhere was the behavior explicitly documented, so let's do that. There was some discussion of throwing an error or warning if a statement trigger is created on a view without creating a row INSTEAD OF trigger. But a simple implementation of that would result in dump/restore ordering hazards. Given that it's been like this all along, and we hadn't heard a complaint till now, a documentation improvement seems sufficient. Per bug #15106 from Pu Qun. Back-patch to all supported branches. Discussion: https://postgr.es/m/152083391168.1215.16892140713507052796@wrigleys.postgresql.org --- doc/src/sgml/ref/create_trigger.sgml | 13 +++++++++++++ doc/src/sgml/trigger.sgml | 12 +++++++++--- 2 files changed, 22 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index 3d6b9f033c..784ed5b8e0 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -499,6 +499,19 @@ UPDATE OF column_name1 [, column_name2 + + Statement-level triggers on a view are fired only if the action on the + view is handled by a row-level INSTEAD OF trigger. + If the action is handled by an INSTEAD rule, then + whatever statements are emitted by the rule are executed in place of the + original statement naming the view, so that the triggers that will be + fired are those on tables named in the replacement statements. + Similarly, if the view is automatically updatable, then the action is + handled by automatically rewriting the statement into an action on the + view's base table, so that the base table's statement-level triggers are + the ones that are fired. + + Modifying a partitioned table or a table with inheritance children fires statement-level triggers attached to the explicitly named table, but not diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 8f83e6a47c..c43dbc9786 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -53,15 +53,21 @@ On views, triggers can be defined to execute instead of INSERT, UPDATE, or - DELETE operations. INSTEAD OF triggers + DELETE operations. + Such INSTEAD 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 - underlying base tables and, where appropriate, return the modified + 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 per SQL statement, before or after INSERT, UPDATE, or DELETE operations. + However, such triggers are fired only if there is also + an INSTEAD 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). -- 2.40.0