Sample Header Ad - 728x90

Trigger does not seem to respect condition

0 votes
1 answer
51 views
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