Date: Mon, 29 Jan 2018 10:28:32 -0500
From: Bruce Momjian <bruce@momjian.us>
To: "Ian R. Campbell" <ian.campbell@thepathcentral.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
	Thomas Munro <thomas.munro@enterprisedb.com>,
	ian@thepathcentral.com, pgsql-docs@postgresql.org,
	Andrew Gierth <andrew@tao11.riddles.org.uk>
Subject: Re: Trigger behaviour not as stated
Message-ID: <20180129152832.GA11613@momjian.us>
References: <20180124181008.GK17109@momjian.us>
 <20180124220701.GP17109@momjian.us>
 <CAEepm=1cAO8y4dFv_xd=43iP7f4T3A2hYAzjgjQagHtt7H87Fw@mail.gmail.com>
 <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>
 <CAOC8YUdZ-J42bUz2SFXsQjnAiv5YfkDR5rupN0pGP=ZLY-wt8Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="/04w6evG8XlLl3ft"
Content-Disposition: inline
In-Reply-To: 
 <CAOC8YUdZ-J42bUz2SFXsQjnAiv5YfkDR5rupN0pGP=ZLY-wt8Q@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk


--/04w6evG8XlLl3ft
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Mon, Jan 29, 2018 at 11:32:34AM +0000, Ian R. Campbell wrote:
> 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).

Ian, that is not true based on my testing.  Running that attached script
that I already posted shows:

	test=> INSERT INTO parent VALUES (1, 'one');
	NOTICE:  Called by parent INSERT
	INSERT 0 1
	test=> INSERT INTO child VALUES (2, 'two');
-->	NOTICE:  Called by child INSERT
	INSERT 0 1

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

--/04w6evG8XlLl3ft
Content-Type: application/x-sql
Content-Disposition: attachment; filename="trigger.sql"
Content-Transfer-Encoding: quoted-printable

CREATE TABLE parent(id integer, val text);=0ACREATE TABLE child() INHERITS =
(parent);=0A=0ACREATE OR REPLACE FUNCTION inh_trigger() RETURNS trigger=0A =
  LANGUAGE plpgsql AS=0A$$BEGIN=0A   RAISE NOTICE 'Called by parent %', TG_=
OP;=0A   RETURN CASE WHEN TG_OP =3D 'DELETE' THEN OLD ELSE NEW END;=0AEND;$=
$;=0A=0ACREATE TRIGGER inh_trigger BEFORE INSERT OR UPDATE OR DELETE ON par=
ent=0A   FOR EACH ROW EXECUTE PROCEDURE inh_trigger();=0A=0ACREATE OR REPLA=
CE FUNCTION inh_trigger2() RETURNS trigger=0A   LANGUAGE plpgsql AS=0A$$BEG=
IN=0A   RAISE NOTICE 'Called by child %', TG_OP;=0A   RETURN CASE WHEN TG_O=
P =3D 'DELETE' THEN OLD ELSE NEW END;=0AEND;$$;=0A=0ACREATE TRIGGER inh_tri=
gger2 BEFORE INSERT OR UPDATE OR DELETE ON child=0A   FOR EACH ROW EXECUTE =
PROCEDURE inh_trigger2();=0A=0AINSERT INTO parent VALUES (1, 'one');=0AINSE=
RT INTO child VALUES (2, 'two');=0A=0AUPDATE parent SET val =3D 'changed' W=
HERE id =3D 1;=0AUPDATE parent SET val =3D 'changed' WHERE id =3D 2;=0A=0AD=
ELETE FROM parent WHERE id =3D 1;=0ADELETE FROM parent WHERE id =3D 2;=0A
--/04w6evG8XlLl3ft--