I am trying to create a trigger in Supabase with the following code that will help me update a certain value in another table. Following is the trigger code for the supabase function
BEGIN DECLARE num integer; SELECT count(*) into num FROM chapters WHERE seriesid=new.seriesid; INSERT INTO public.series(chapcount); WHERE id=new.seriesid; values(num); RETURN new; END;
However, I get the following error
Failed to create function: Syntax error at or near "*"
There are multiple errors as you can see in the manual
DECLARE section goes before the BEGIN.
And as documented in the manual the INSERT statement has no WHERE clause.
So assuming you have the rest of the function (or procedure) right (which you didn’t show us, the PL/pgSQL block needs to look like this:
DECLARE num integer; BEGIN SELECT count(*) into num FROM chapters WHERE seriesid = new.seriesid; INSERT INTO public.series(id, chapcount); values(new.seriesid, num); RETURN new; END;
It’s unclear to me what your intention with the INSERT statement is. If you are trying to change an existing row, you need an UPDATE statement:
UPDATE public.series SET chapcount = num WHERE id = new.seriesid;
Note that if the trigger is defined to be fired on the
series table, then you don’t need an UPDATE at all. Make it a
BEFORE trigger and simply assign the new count:
new.chapcount := num; return new;
Answered By – a_horse_with_no_name
Answer Checked By – Senaida (BugsFixing Volunteer)