Postgresql trigger not update - stack depth limit reached

Question

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?

Answer 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()