I have an enum ('not created', 'in creation', 'created', 'updated', 'rejected') and a trigger in PostgreSQL which holds some of the logic for changing the status of a record on an INSERT or UPDATE:
CREATE OR REPLACE FUNCTION set_status()
RETURNS trigger AS
$BODY$
BEGIN
IF TG_OP ILIKE 'UPDATE' THEN -- this line was added after Pavel Stehule's answer
IF (NEW.field1 IS NULL) THEN
NEW.status = 'not created';
ELSEIF (NEW.field1 IS NOT NULL) THEN
IF OLD.status IN ('not created')
THEN
NEW.status = 'created'; -- point 1
ELSEIF OLD.status IN ('created', 'updated', 'rejected') -- point 2
AND NEW.status NOT IN ('not created')
THEN
NEW.status = 'updated';
ELSEIF NEW.status NOT IN ('not created', 'created', 'updated', 'rejected')
THEN
NEW.status = 'in creation';
ELSE
NEW.status = 'not created';
END IF;
END IF;
END IF; -- this line was added after Pavel Stehule's answer
RETURN NEW;
END
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER update_status_biut
BEFORE INSERT OR UPDATE
ON table
FOR EACH ROW
EXECUTE PROCEDURE set_status();
Everything works fine, except that when the status starts with a 'not created' value (it's the default for new records), it directly switch to 'updated' on UPDATE or INSERT where it should stay on 'created' the very first time the record is processed.
To me, it sounds like where I placed the "-- point 1", that the NEW.status is taking the value 'created' here, which is correct, but on the next ELSEIF block, it re-uses that value as the 'OLD.status' (?!), hence verifying the condition, which makes it switch to 'updated'.
I also verified this by trying to remove the 'created' value from the list on "-- point 2". In that case, the status stays gently on 'created' after an UPDATE/INSERT.
How to avoid that strange behaviour and keep the first value which has been met ('created' in that case) in the if-blocks and 'pass' the (following) others?
Version info (Dockerized PostgreSQL):
# psql --version
psql (PostgreSQL) 13.3 (Debian 13.3-1.pgdg100+1)
Aucun commentaire:
Enregistrer un commentaire