Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nlmjA-0003rI-Rm for pgsql-docs@arkaria.postgresql.org; Tue, 03 May 2022 07:13:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nlmj9-0008H3-PV for pgsql-docs@arkaria.postgresql.org; Tue, 03 May 2022 07:13:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nljIY-0004ls-QY for pgsql-docs@lists.postgresql.org; Tue, 03 May 2022 03:34:02 +0000 Received: from mail-il1-x12b.google.com ([2607:f8b0:4864:20::12b]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nljIW-0000Wc-QU for pgsql-docs@lists.postgresql.org; Tue, 03 May 2022 03:34:02 +0000 Received: by mail-il1-x12b.google.com with SMTP id t4so9037001ilo.12 for ; Mon, 02 May 2022 20:34:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paladin.insure; s=google; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=gidQkY87sHtWFC9+qChk3bAEqiyDOZfAEaxG9De6Ypc=; b=X8iMkaJtM39MDDWZxlK1euqCoWTA9vsFDmag6IFp9kPOaJPr9WgiW9UQN8NayiY0pb koONITTiu11Hwpp22gO5d4IPcpSr1hFG8uo57Z/GgjAKIjfnP35KCWauA0HUaJzmn9Jp pe+L4c43q72a28EJECxrYNUwC2/+luzto08xI= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=gidQkY87sHtWFC9+qChk3bAEqiyDOZfAEaxG9De6Ypc=; b=WFK8uBbx+hFSFBoTYLOSWGzRmBNxC30FXDoAqmABVgTtCPZrVJ4H7ZkifLYr3j0SZE m+AaFKe7rr3BGJL7PFr6LyYWFjvnRJeg952I47QZC8rLlNKpCcw77MDZw3goCj4HzmaZ gnXPdee7PG3l4OE5FUTEWi3VbdfaNRtR7QHjoYGX207kBi9XX+FZipzdkvdgdDhYXM5l +atF94Ag/e2j+UzVke/8gn8+GzasxMS9e2XcLUDYJSYlOnRRKuf8ixq1Op0Kmki6Ct4M 6eeoM9GDYl1BNsxy75Lhu5H93ZE3Dy3rs+4pfIzsqJZEtjkx4vHi7tBLjxr+B05TKoCI 8ANQ== X-Gm-Message-State: AOAM530pRGZSLKcQ+nqDwDi20zr3vffkEApxVn/OhGGq5etkGz+VJ/pU B9V0ezCTJtbLJFRbIzFKNYNQwgf3KAkdLmlauPgcEQ== X-Google-Smtp-Source: ABdhPJyWJti6zLMhC03NdFYK79nNx9FHv66Qr0rF9dsHLaxu9OiWjdXswqtHOdRJfIg8zeAua7XXoEfXL1DJEEwW5NU= X-Received: by 2002:a92:c24d:0:b0:2cf:216c:c57a with SMTP id k13-20020a92c24d000000b002cf216cc57amr2035433ilo.164.1651548839056; Mon, 02 May 2022 20:33:59 -0700 (PDT) MIME-Version: 1.0 References: <165151321896.685.16545084197561479198@wrigleys.postgresql.org> In-Reply-To: From: Josh Silver Date: Mon, 2 May 2022 20:33:48 -0700 Message-ID: Subject: Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior' To: "David G. Johnston" Cc: Pg Docs Content-Type: multipart/alternative; boundary="000000000000e1acda05de132dcf" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e1acda05de132dcf Content-Type: text/plain; charset="UTF-8" On Mon, May 2, 2022 at 7:11 PM David G. Johnston wrote: > On Mon, May 2, 2022 at 6:55 PM David G. Johnston < > david.g.johnston@gmail.com> wrote: > >> On Mon, May 2, 2022 at 6:33 PM Josh Silver >> wrote: >> > And, given the existing length and depth of content of that page already > I'm doubtful that introducing the corner-case of UPDATE OF (col) into that > section, in the level of detail provided in CREATE TRIGGER, is a net > positive. > I'd definitely lean towards complete over terse, especially since I don't think it's any more detailed or lengthy than the WHEN CLAUSE that's already there, but given those concerns I think at a minimum mentioning column specific triggers by name and that changes made to the row's contents by BEFORE UPDATE triggers are not considered. I'd probably leave the detail where it is, note that "UPDATE OF (col)" > exists as a distinct event type that modifies a plain UPDATE TRIGGER's > event check, and direct the reader to the specifics in CREATE TRIGGER > should they feel the need to use the more specific form. > That sounds reasonable to me, What about splitting column specific triggers up across the page, so that less space is given to them? Something like > These two types of triggers are sometimes called *row-level* triggers and > *statement-level* triggers, respectively. A row-level trigger that specifies > a list of columns is a column-specific trigger. Triggers on TRUNCATE may > only be defined at statement level, not per-row. 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 of BEFORE and INSTEAD OF triggers, the possibly-modified row > returned by each trigger becomes the input to the next trigger. Changes > made to the row's contents by BEFORE UPDATE triggers do not change which > column specific triggers will be fired, because they only consider > whether any of its columns are listed as targets in the UPDATE command's > SET list. If any BEFORE or INSTEAD OF trigger returns NULL, the > operation is abandoned for that row and subsequent triggers are not fired > (for that row). It saves the syntax for the SQL command page, and it trades directing the users to the SQL page for describing the behavior. The overview page already has a link to the SQL command page, so it saves having to re-mention it. Josh --000000000000e1acda05de132dcf Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =
=C2=A0
I'd definitely lean towards complete= over terse, especially since I don't think it's any more detailed = or lengthy than the WHEN CLAUSE that's already there, but given those c= oncerns I think at a minimum mentioning column specific=C2=A0triggers by na= me and that changes made to the row's contents by BEFORE UPDATE trigger= s are not considered.

I'd probably leave the detail w= here it is, note that=C2=A0 "UPDATE OF (col)" exists as a distinc= t event type that modifies a plain UPDATE TRIGGER's event check, and di= rect the reader to the specifics in CREATE TRIGGER should they feel the nee= d to use the more specific form.
=C2=A0<= /div>
=C2=A0That sounds reasonable to me, What about splitting column s= pecific=C2=A0triggers up across the page, so that less space is given to th= em? Something like
These two types of triggers are sometimes called=C2=A0<= /span>= row-level=C2=A0triggers and=C2=A0statement-lev= el=C2=A0triggers, respectively.=C2=A0<NEW>A row-level tr= igger that=C2=A0specifies a list of columns i= s a=C2=A0column-specific trigger.</NEW>=C2=A0Triggers = on=C2=A0TRUNC= ATE=C2=A0may only be defined at statement lev= el, not per-row.

If more than one trigger i= s defined for the same event on the same relation, the triggers will be fir= ed in alphabetical order by trigger name. In the case of=C2=A0BEFORE=C2=A0and=C2=A0INSTEAD OF=C2=A0triggers, the p= ossibly-modified row returned by each trigger becomes the input to the next= trigger.=C2=A0<NEW>Changes made to the row's contents by=C2=A0BEFORE UPDATE=C2=A0trig= gers do not change which column specific triggers will be fired, because th= ey=C2=A0only consider whether any of its colu= mns are listed as targets in the=C2=A0UPDATE=C2=A0command&= #39;s=C2=A0SE= T=C2=A0list.<= ;/NEW>=C2=A0If any=C2=A0BEFORE=C2=A0or=C2=A0INSTEAD OF=C2=A0trigger returns=C2=A0NULL, the operation is abandoned for that row and subsequent = triggers are not fired (for that row).

It saves the syntax for the SQL command page, and it trades directing th= e users to the SQL page for describing the behavior. The overview page alre= ady has a link to the SQL command page, so it saves having to re-mention it= .

Josh=C2=A0
--000000000000e1acda05de132dcf--