Postgresql trigger not update - stack depth limit reached [duplicate]

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?

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. — Adrian Klaver
– Adrian Klaver, Commented Mar 16, 2022 at 19:47
you right, @AdrianKlaver. I fix that and it works, it was my first trigger — Gonzalo
– Gonzalo, Commented Mar 17, 2022 at 20:18

user330315 · Accepted Answer · 2022-03-16 19:41:20Z

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

answered Mar 16, 2022 at 19:41

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Gonzalo Over a year ago

thanks, that was the problem

Collectives™ on Stack Overflow

Postgresql trigger not update - stack depth limit reached [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related