0

everyone! i want to do a very simple trigger that sees if some conditionals are 0 they set false on the same table, buy i am doing wrong and it s not updating

CREATE or REPLACE FUNCTION chequeo_datos() RETURNS trigger AS $$
BEGIN
IF NEW.val_cuota = 0 or NEW.val_pat = 0 THEN
    UPDATE habitat SET status=False;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER chequeo_datos AFTER INSERT OR UPDATE ON public.habitat
    FOR EACH ROW EXECUTE FUNCTION chequeo_datos();

it gives me this error : "stack depth limit reached". could you tell me what i am doing wrong?

Gonzalo
  • 11
  • 2
  • You have a recursion as the `UPDATE` trigger on the table does an `UPDATE` on the table which runs the `UPDATE` trigger which does an `UPDATE` and so on until the stack depth limit is reached. Read this [plpgsql trigger function](https://www.postgresql.org/docs/current/plpgsql-trigger.html). – Adrian Klaver Mar 16 '22 at 19:47
  • you right, @AdrianKlaver. I fix that and it works, it was my first trigger – Gonzalo Mar 17 '22 at 20:18

1 Answers1

1

Don't UPDATE the table, assign the new value to the record's column:

CREATE or REPLACE FUNCTION chequeo_datos() RETURNS trigger AS $$
BEGIN
IF NEW.val_cuota = 0 or NEW.val_pat = 0 THEN
    new.status := False;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

For that to work, you need a BEFORE trigger:

CREATE TRIGGER chequeo_datos 
    BEFORE INSERT OR UPDATE ON public.habitat
    FOR EACH ROW EXECUTE FUNCTION chequeo_datos()