PostgreSQL trigger how to detect a column value explicitely modified
| От | PALAYRET Jacques |
|---|---|
| Тема | PostgreSQL trigger how to detect a column value explicitely modified |
| Дата | |
| Msg-id | 616435641.255753519.1762260528389.JavaMail.zimbra@meteo.fr обсуждение исходный текст |
| Ответы |
Re: PostgreSQL trigger how to detect a column value explicitely modified
Re: PostgreSQL trigger how to detect a column value explicitely modified Re: PostgreSQL trigger how to detect a column value explicitely modified |
| Список | pgsql-general |
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 c2=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.
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 c2=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
id | c2 | c3
-----+----+----
100 | 1 | 2
UPDATE tb
SET c2=2
;
TABLE tb ;
id | c2 | c3
-----+----+----
100 | 0 | 2
---------- ---------- ---------- ---------- ----------
UPDATE tb
SET c3=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 :
UPDATE tb
SET c3=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
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
jacques.palayret@meteo.fr
Fixe : +33 561078319
PALAYRET Jacques
DCSC/GDC
jacques.palayret@meteo.fr
Fixe : +33 561078319
В списке pgsql-general по дате отправления: