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