0

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.

Improve this question
5
  • Please show the actual setup (core table, trigger function, trigger) that is failing you. Ideally in a fiddle to play with. Not just the trigger. The presented alternative function is missing the language declaration LANGUAGE plpgsql, so invalid syntax. Commented May 16 at 2:56
  • The statements in your fiddle don't insert anything into uniteobservation, so how can an update modify a row? It should be a self-contained test case. Commented May 16 at 7:37
  • you can't update the 2 records I have inserted ? Or you want to insert some rows as well ? Commented May 16 at 7:41
  • fiddle updated with some insert commands if it can help : dbfiddle.uk/KqjXDVdY?hide=320 Commented May 16 at 7:48
  • At some point in the big, long fiddle a note claims "geom_l93 NULL but trigger ran", but it's not immediately apparent why. There is too much unrelated noise going on. For a public question, please provide a minimal reproducible example (MRE) or SSCCE. See: en.wikipedia.org/wiki/Minimal_reproducible_example If you are not prepared to put in the work, consider hiring a consultant to work through your original situation. Commented May 19 at 0:26

1 Answer 1

Reset to default
1

As you can see in this demo & fiddle, it works for me:

CREATE TABLE uniteobservation (
  id serial
, geom_l93 point
, index_per text
);

CREATE OR REPLACE FUNCTION check_geom_l93_trigger()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.index_per := 'Trigger executed';
   RETURN NEW;
END
$func$;

CREATE OR REPLACE TRIGGER check_periode_hierarchie
BEFORE INSERT OR UPDATE ON uniteobservation
FOR EACH ROW
WHEN (NEW.geom_l93 IS NOT NULL)
EXECUTE FUNCTION check_geom_l93_trigger();

INSERT INTO uniteobservation (id, geom_l93) VALUES (1, '(1,2)');

INSERT INTO uniteobservation (id, geom_l93) VALUES (2, null);

INSERT INTO uniteobservation (id, geom_l93) VALUES (3, null);
UPDATE uniteobservation SET geom_l93 = '(3,2)' WHERE id = 3;


TABLE uniteobservation;
id geom_l93 index_per
1 (1,2) Trigger executed
2 null null
3 (3,2) Trigger executed

fiddle

Something must be going on that's not in your question.

Improve this answer
1
  • I added a fiddle in my message. I am not familiar with fiddle so I am not sure that all my comments are readable. So I repeat here : you can use any values among République, Haut-empire, Bas-empire, Antiquité tardive for index_periode column. Thanks Commented May 16 at 7:17

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.