From: | PG Doc comments form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-docs(at)lists(dot)postgresql(dot)org |
Cc: | josh(dot)ag(at)paladin(dot)insure |
Subject: | Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior' |
Date: | 2022-05-02 17:40:18 |
Message-ID: | 165151321896.685.16545084197561479198@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2022-05-02 22:21:41 | Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior' |
Previous Message | David G. Johnston | 2022-04-29 15:05:36 | Re: Documentation Suggestion |