I have the following schema:
CREATE FUNCTION myfunc() Returns trigger as $myfunc$ DECLARE someVal integer; BEGIN SELECT Count(*) into someVal FROM MyTable WHERE someAttribute = OLD.someAttribute; IF someVal = 100 then -- Recursion, don't do this -- DELETE FROM MyTable -- WHERE someAttribute = OLD.someAttribute; -- delete from MyTable2 DELETE FROM MyTable2 WHERE someAttribute = OLD.someAttribute; END IF; RETURN OLD; END; $myfunc$ LANGUAGE plpgsql;
I have oversimplified what I did in my code, so let me clarify it.
I am registering this function with the following code:
CREATE TRIGGER myfunc BEFORE DELETE ON MyTable FOR EACH ROW EXECUTE PROCEDURE myfunc();
So, when someone deletes a value in my relationship and if some condition occurs, I want to delete other values as well. Think about this: I have a hundred 10's in my database. If the user deletes one of them, it will be 99 and I want to delete all 99 of them because less than 100 attributes are no use to me (don't say why I have said I have oversimplified this).
So, If I delete from the same table inside the
IF condition, I am encountering infinite loop, and the program stops when the stack overflows. What should I do? I need to delete those values from