I'm executing a loop that iterates over a cursor's results. The code is inside a trigger function, and the part that matters looks like that:
EDIT: Sorry, stupid mistake. The trigger is executed after deleting on "one_table". This is not the same table in which I perform the 'delete' or 'update' operations inside the trigger's code (see second listing below)
create trigger my_trigger after delete on one_table for each row begin declare my_value int; declare num_rows int default 0; declare done int default false; declare my_cursor cursor for select value from table where condition; declare continue handler for sqlstate '02000' set done = 1; open my_cursor; select found_rows() into num_rows; -- This is just for debugging insert into log_table(key, value) values('foo', num_rows); if num_rows > 0 then: repeat fetch my_cursor into my_value; -- Do stuff until done end repeat; end if; close my_cursor; end
The loop should be executed 11 times, because the query returns 11 values. This is checked by the 'insert' clause. The var 'num_rows' is equal to 11. But the problem is the loop is only executed 3 times.
These numbers (11, 3) are not important. If I change the query to return a different amount of results, the problem remains: the loop ends before scheduled.
Does it make any sense? I may do something inside the loop (the 'do stuff' part) that causes the end of the loop. It's the only thing that sounds logic for me.
EDIT: I include the 'Do stuff' part. The problem is probably caused because, inside this part, some 'select' sentences are executed. When one of those sentences returns an empty result, the defined handler is executed, setting done to 'true' and breaking the loop.
The "Do stuff" part is like this:
select some_value into some_field from other_table where some_conditions; if (some_field is null) then delete from my_table where my_condition; else update my_table set key1 = value1 where condition1;
Thanks in advance, and best regards,