Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eg8kU-0004eh-SU for pgsql-docs@arkaria.postgresql.org; Mon, 29 Jan 2018 12:41:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eg8kT-00038D-UW for pgsql-docs@arkaria.postgresql.org; Mon, 29 Jan 2018 12:41:21 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eg7gC-0001ip-9L for pgsql-docs@lists.postgresql.org; Mon, 29 Jan 2018 11:32:52 +0000 Received: from mail-qt0-x231.google.com ([2607:f8b0:400d:c0d::231]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eg7g6-0004ir-OJ for pgsql-docs@postgresql.org; Mon, 29 Jan 2018 11:32:50 +0000 Received: by mail-qt0-x231.google.com with SMTP id i1so12179399qtj.8 for ; Mon, 29 Jan 2018 03:32:46 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=thepathcentral-com.20150623.gappssmtp.com; s=20150623; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=4cZ1dsrcpFq9lNXGvI+bYlbsEKWbQZJtEAM4/Punqmk=; b=gczML/NAe2T5oUNVnO5UWQTwTQaKJv/sRFe9zeqnTODrXdclBzd/il5LzZnc336Zyx geULa2n2+NcPaETcDn08v/iyOyXY7/OJI/ijGKehFum6CFkEWOR/BlbaHOdt66HBvU+A WRuU+ITmbccvbRHmIQGY2017K2F1EIWVxJJPsQK9JH6DqQJ382sPGOLi3aWbPQZumnzA PS2rgLz31HoIzHPGUNNOtH4f2UmNKu61RID7nmEFabCsQch5XSGuZfvrrJc3o2zy41Q+ RzI4vERQsVm2cgHuZgjvnf5wI6UG17MDgiXCH3gsz8MWTKgDL4uzQv80EFgOuTx0hE7R dyag== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=4cZ1dsrcpFq9lNXGvI+bYlbsEKWbQZJtEAM4/Punqmk=; b=iX44zX450oB1d0HoM2vFiOPyC5q1RJD9VI3KtsuHCEsBjAW2GtF4JyIX6yH5EZOn5m eTXVDje3E3hmEpCvPSb+LS9IsoCT6e+BSNfE6bAXi+VGASPnYjrxjx3rA8tbKX9iwx3L c+r3O46+7j/qIkn4ZGqwWgJ4/ljghxJRjT/ghv9iadIa5WfU0OrUEX6IHYKIMPkXWhQ7 U2KQmCUQrP6+XYc2Dp2G8tcdV73Hb2MNtTQGJ+SUGQe3i7PbNzQ781DtqY1ekpYrrHem yIFa3bT7q4oM1o58c3d7o+UMzqfFkGjLAyCfxv1ST5eLq5iaj8vaL/qWns6nvgUtoA5B 16ww== X-Gm-Message-State: AKwxytemCk4HKPZF6uzeSWxEpzYpE2kVZF209fD07gee6P96Tl1wEHW6 ospdUtU1cqcDBFdH5LyeZouVDpyMNrNru70a9lbq7g== X-Google-Smtp-Source: AH8x227tbClm8bfimgDmw/5AqQiTvi01x2yD72aun0JXJp/e7JobVKunBuF30Cd2FSAUgVYePDSTskdR2FF95fSPWeg= X-Received: by 10.237.58.7 with SMTP id n7mr37655118qte.194.1517225565330; Mon, 29 Jan 2018 03:32:45 -0800 (PST) MIME-Version: 1.0 References: <20171129193934.27108.30796@wrigleys.postgresql.org> <20180124181008.GK17109@momjian.us> <20180124220701.GP17109@momjian.us> <28238.1517167118@sss.pgh.pa.us> <20180128203402.GB4380@momjian.us> <15634.1517173027@sss.pgh.pa.us> <20180128225824.GA5022@momjian.us> <21622.1517181121@sss.pgh.pa.us> <20180128231719.GC5022@momjian.us> In-Reply-To: <20180128231719.GC5022@momjian.us> From: "Ian R. Campbell" Date: Mon, 29 Jan 2018 11:32:34 +0000 Message-ID: Subject: Re: Trigger behaviour not as stated To: Bruce Momjian Cc: Tom Lane , Thomas Munro , ian@thepathcentral.com, pgsql-docs@postgresql.org, Andrew Gierth Content-Type: multipart/alternative; boundary="001a11403428ddb8e30563e89a83" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --001a11403428ddb8e30563e89a83 Content-Type: text/plain; charset="UTF-8" The original confusion on this is answered in part by the following statement (taken from the answer to my SO question): "UPDATEs and DELETEs on the parent table will affect rows in the child tables (if you don't specify ONLY), but triggers will only be fired by data modifications directed directly against the table with the trigger on it " This clearly explains that a trigger attached to a parent table will not fire if the data being modified is in a child table. The second part of the confusion is that INSERT is not considered to be a row modification and will fire a BEFORE INSERT trigger on the parent table even when the data goes into a child (whereas UPDATE and DELETE will not fire a parent trigger). My proposal (adapted from Tom's): In contrast, row-level UPDATE and DELETE triggers are fired for individual row change events only on the table to which the trigger is attached. Therefore, UPDATE and DELETE triggers on a parent table will only fire when rows in the parent table are being modified. Likewise, UPDATE and DELETE triggers on a child table will only fire when rows in the child table are being modified. Note that INSERT statements do not follow these update rules, so statements run on parent tables will insert rows in child tables if the trigger function so directs. The last line may need a bit of work, but I feel the text above it is clear. Ian On Mon, 29 Jan 2018 at 07:17 Bruce Momjian wrote: > On Sun, Jan 28, 2018 at 06:12:01PM -0500, Tom Lane wrote: > > Bruce Momjian writes: > > > Oh, I am sorry. I was focused on the first part of the sentence and > > > didn't notice your change to the second part. How is this attachment? > > > > Seems same as your previous version? > > OK, new vesion that uses "explicitly named" in both modified doc lines. > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + As you are, so once was I. As I am, so you will be. + > + Ancient Roman grave inscription + > --001a11403428ddb8e30563e89a83 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
The original confusion on this is answered in part by the = following statement (taken from the answer to my SO question):

"UPDATEs = and=C2=A0DELETEs on the parent table will affect rows in the child tables (if you don'= ;t specify=C2=A0ONLY), but triggers will only be fired by data modifications directed dir= ectly against the table with the trigger on it=C2=A0"

This clearly explains that a trigger attached to a parent t= able will not fire if the data being modified is in a child table.

The second part of the confusion is that INSERT is not con= sidered to be a row modification and will fire a BEFORE INSERT trigger on t= he parent table even when the data goes into a child (whereas UPDATE and DE= LETE will not fire a parent trigger).

My proposal = (adapted from Tom's):

=C2=A0In contrast, row-level UPDATE and DELETE triggers are fir= ed for individual row change
=C2=A0 =C2=A0 events only on the table= =C2=A0to which the=C2=A0trigger is atta= ched. Therefore, UPDATE and DELE= TE triggers on
=C2=A0 = =C2=A0a parent table will only fire when rows in the parent table are being= modified. Likewise,=C2=A0
=C2=A0 =C2=A0UPDATE and DELETE triggers=C2=A0on=C2=A0a=C2=A0child table will on= ly fire when rows in the child table are being modified.
=
=C2=A0 =C2=A0Note that INSERT stat= ements do not follow these update rules, so statements run on parent tables= will=C2=A0
=C2=A0 =C2= =A0insert rows in child tables if the trigger function so directs.
=C2=A0
The last line may need a bit of work, but I f= eel the text above it is clear.

Ian
On Mon, 29 Jan 2018 at 07:17 B= ruce Momjian <bruce@momjian.us&g= t; wrote:
On Sun, Jan 28, 2018 at 0= 6:12:01PM -0500, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Oh, I am sorry.=C2=A0 I was focused on the first part of the sent= ence and
> > didn't notice your change to the second part.=C2=A0 How is th= is attachment?
>
> Seems same as your previous version?

OK, new vesion that uses "explicitly named" in both modified doc = lines.

--
=C2=A0 Bruce Momjian=C2=A0 <bruce@momjian.us>=C2=A0 =C2=A0 =C2=A0 =C2=A0 http://momjian.us
=C2=A0 EnterpriseDB=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0http://enterprisedb.com
+ As you are, so once was I.=C2=A0 As I am, so you will be. +
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 Ancient Roman grave inscription +
--001a11403428ddb8e30563e89a83--