MIME-Version: 1.0
References: <616435641.255753519.1762260528389.JavaMail.zimbra@meteo.fr>
In-Reply-To: <616435641.255753519.1762260528389.JavaMail.zimbra@meteo.fr>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Tue, 4 Nov 2025 16:00:42 +0100
Message-ID: <CAFCRh--trX7oRCiyrxc_QWwjJL4SgnT_vLC4rdWWv1H3PZOKuA@mail.gmail.com>
Subject: Re: PostgreSQL trigger how to detect a column value explicitely modified
To: PALAYRET Jacques <jacques.palayret@meteo.fr>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh--trX7oRCiyrxc_QWwjJL4SgnT_vLC4rdWWv1H3PZOKuA%40mail.gmail.com>
Precedence: bulk

On Tue, Nov 4, 2025 at 1:49=E2=80=AFPM PALAYRET Jacques
<jacques.palayret@meteo.fr> wrote:
> In a trigger body, is there a simple way to know if a column value has be=
en explicitely modified ?

Using pg_trigger_depth(), you can know whether the trigger is called
from "outer SQL" directly,
or from SQL done within another trigger (because the depth will be
larger). I didn't quite follow
your description, to be honest, but I suspect the above is what you
want (maybe :)). --DD

PS: To illustrate, we have this trigger to enforce some of our tables
are "trigger managed",
and no DMLs should be done "directly" on them (only from triggers). FWIW. -=
-DD

PPS: pg_trigger_depth() is 0 if the trigger function is called
directly (unusual).
  1 if directly called from an "outer SQL" statement (from a proc/func or n=
ot).
  2 or more if triggered from SQL done by another (possibly the same)
"triggered" trigger.

CREATE FUNCTION trigger_managed_tf()
RETURNS TRIGGER
AS $$
BEGIN
    IF pg_trigger_depth() < 2 THEN
        RAISE EXCEPTION 'Direct insert/update/delete are not allowed
on the % table.', TG_TABLE_NAME;
    END IF;
    RETURN COALESCE (NEW, OLD);
END
$$ LANGUAGE plpgsql