public inbox for [email protected]  

help / color / mirror / Atom feed

From: PG Doc comments form <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'
Date: Mon, 02 May 2022 17:40:18 +0000
Message-ID: <[email protected]> (raw)

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/13/trigger-datachanges.html
Description:

This caught me out because I was looking at the wrong page, so I figured it
might be a good addition. I'm running version 'PostgreSQL 13.2 (Debian
13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6)
8.3.0, 64-bit'

If I have

CREATE TABLE test ( alpha integer, beta integer, charlie integer);
CREATE FUNCTION ab() RETURNS TRIGGER
LANGUAGE plpgsql
    AS $_$
BEGIN
    RAISE INFO '%: ab firing', clock_timestamp();
    IF NEW.alpha - OLD.alpha >= 5 THEN 
        NEW.beta = NEW.beta + 1;
    END IF;
    RETURN NEW;
END;
$_$;
CREATE FUNCTION bc() RETURNS TRIGGER
LANGUAGE plpgsql
    AS $_$
BEGIN
        RAISE INFO '%: bc firing', clock_timestamp();
    IF NEW.beta IS DISTINCT FROM OLD.beta THEN 
        NEW.charlie = NEW.charlie + 1;
    END IF;
    RETURN NEW;
END;
$_$;
CREATE TRIGGER b2_ab BEFORE UPDATE OF alpha ON test FOR EACH ROW EXECUTE
FUNCTION ab();
CREATE TRIGGER b3_bc BEFORE UPDATE OF beta ON test FOR EACH ROW EXECUTE
FUNCTION bc();

INSERT INTO test (alpha, beta, charlie) VALUES (0,0,0);

then when running

UPDATE test SET alpha = 5;

only trigger b2_ab will fire, even though it will update column beta. If i
change the declaration of b3_bc by removing the column list or including
column alpha, things work as I expected and b2_ab cascades to b3_bc.

This specific behavior is mentioned in the sql documentation reference for
CREATE TRIGGER, but I think at least a mention of this would be useful on
this page as well similar to how WHEN conditions are mentioned on both
pages.

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox