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,
Best Answer
You should strictly check for the end of the loop inside the loop
This would also eliminate have to check the count
Give it a Try !!!
Looking at your pseudcode
It may not be a good idea to perform an
UPDATE
on my_table when you are right in the middle of an afterDELETE
trigger on the same table. Also, notice you are also causing a DELETE underif (some_field is null) then
on the same table.You are probably better off writing this trigger as a Stored Procedure and manually using the Call to it instead of nesting UPDATE of a table inside DELETE on the same table.