public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dominique Devienne <[email protected]>
To: PALAYRET Jacques <[email protected]>
Cc: [email protected]
Subject: Re: PostgreSQL trigger how to detect a column value explicitely modified
Date: Tue, 4 Nov 2025 16:00:42 +0100
Message-ID: <CAFCRh--trX7oRCiyrxc_QWwjJL4SgnT_vLC4rdWWv1H3PZOKuA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

On Tue, Nov 4, 2025 at 1:49 PM PALAYRET Jacques
<[email protected]> wrote:
> In a trigger body, is there a simple way to know if a column value has been 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 not).
  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

view thread (3+ messages)  latest in thread

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: PostgreSQL trigger how to detect a column value explicitely modified
  In-Reply-To: <CAFCRh--trX7oRCiyrxc_QWwjJL4SgnT_vLC4rdWWv1H3PZOKuA@mail.gmail.com>

* 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