public inbox for [email protected]  

help / color / mirror / Atom feed

From: PALAYRET Jacques <[email protected]>
To: [email protected]
Subject: PostgreSQL  trigger how to detect a column value explicitely modified
Date: Tue, 4 Nov 2025 12:48:48 +0000 (GMT)
Message-ID: <[email protected]> (raw)

Hello, 

In a trigger body, is there a simple way to know if a column value has been explicitely modified ? 
Explicitely modified ; in others words, typically indicated in the SET clause of the UPDATE. 
A simple way ; I mean without analysing the SQL statement which can be very difficult according to the statement. 

# My trigger function executed by trigger BEFORE UPDATE ON a table tb (with columns id, c2, c3 ; of integer type) FOR EACH ROW : 
CREATE OR REPLACE FUNCTION func_tg_upd() 
RETURNS trigger LANGUAGE plpgsql 
AS $function$ 
BEGIN 
if NEW.c2 != 1 
then 
NEW.c2 := 0 ; 
end if; 
return NEW; 
END; 
$function$ 
; 

# Actions of the trigger 
UPDATE tb 
SET c 2 =2 
WHERE ... -- old c2 value can be 2 or another value 
; 
=> c2 will be changed to 0 by the trigger, OK , because I explicitely indicated the new value of c2 to a value different from 1. 
The the old value of c2 could be different or not. 
Example : 
TABLE tb ; 
id | c2 | c3 
-----+----+---- 
100 | 1 | 2 

UPDATE tb 
SET c2=2 
; 
TABLE tb ; 
id | c2 | c3 
-----+----+---- 
100 | 0 | 2 

---------- ---------- ---------- ---------- ---------- 

UPDATE tb 
SET c 3 =3 
WHERE ... -- and c2=2 ( previous value of c2 is 2 ) 
; 
=> c2 will be changed to 0 by the trigger, but I don't want that action ( NOT OK for me ) because I didn't explicitely indicate the new value 2 to c2 . 
Example : 
TABLE tb ; 
id | c2 | c3 
-----+----+---- 
100 | 2 | 2 

UPDATE tb 
SET c3=3 
; 
TABLE tb ; 
id | c2 | c3 
-----+----+---- 
100 | 0 | 3 


Regards 
----- Météo-France ----- 
PALAYRET Jacques 
DCSC/GDC 
[email protected] 
Fixe : +33 561078319 

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]
  Subject: Re: PostgreSQL  trigger how to detect a column value explicitely modified
  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