lundi 26 août 2019

How can I automatically update a table if certain condition is met

I want to run a trigger, but get a strange error message.

I am using postgressql. I want to run this update:

UPDATE tablename
SET status=5
WHERE id=new.id
AND status=4;

... Whenever someone tries to set a status to 4.

So basically I want to have status 4 only once per "id". And to ensure that, I want to set all other states that have the same id and have status=4 to status=5.

So I put this into a function:

CREATE FUNCTION public.statusfunction()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF 
AS $BODY$

BEGIN
UPDATE tablename
SET status=5
WHERE id=new.id
AND status=4;
END;

$BODY$;
;

and execute this functin with a trigger:

CREATE TRIGGER statustrigger
    BEFORE INSERT OR UPDATE 
    ON public.tablename
    FOR EACH ROW
    EXECUTE PROCEDURE public.statusfunction();

But when I test this triggerfunction, and try to update a status to 4 where a dataset with the same status and id already exist, I get this error message:

ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL statement "UPDATE public.tablename 
SET status=5
WHERE id=new.id
AND status=4"
PL/pgSQL function statusfunction() line 3 at SQL statement

Any idea what i did wrong? And should I include a RETURN at the end? Or is a triggerfunction not the best solution for this problem?

Aucun commentaire:

Enregistrer un commentaire