In my Postgres 15 database, I have a table with several geometric columns. I want a trigger to run on the field
index_per
when a specific geometric field is not null: geom_l93
.
I have created a function and a trigger:
`
CREATE OR REPLACE TRIGGER check_periode_hierarchie
BEFORE INSERT OR UPDATE ON activite.uniteobservation
FOR EACH ROW
WHEN (NEW.geom_l93 is not null)
EXECUTE FUNCTION activite.valider_periodes_hierarchie()
`
I want the trigger to run **only** when geom_l93
is **not NULL
**, on an INSERT
or UPDATE
.
But, when I insert or update with NULL
in the column geom_l93
, the trigger runs anyway.
I tried and failed to solve this issue with some intermediate functions and triggers like:
CREATE OR REPLACE FUNCTION activite.check_geom_l93_trigger()
RETURNS trigger AS
$$
BEGIN
IF NEW.geom_l93 IS NOT NULL THEN
PERFORM activite.valider_periodes_hierarchie();
END IF;
RETURN NEW;
END;
$$
CREATE or replace TRIGGER check_periode_hierarchie
BEFORE INSERT OR UPDATE ON activite.uniteobservation
FOR EACH ROW
EXECUTE FUNCTION activite.check_geom_l93_trigger();
EDIT : I did my best with a fiddle : https://dbfiddle.uk/YytPzEZq?hide=40
EDIT 2 : I disabled other existing triggers on the table one by one to see if there was a "ninja bug", but each time the trigger works on insert whatever the geometric column.
Asked by Leehan
(205 rep)
May 15, 2025, 07:12 PM
Last activity: May 16, 2025, 08:26 AM
Last activity: May 16, 2025, 08:26 AM