Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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,

share|improve this question
    
+1 for solving your own problem and doing the needed research. By the way, welcome to the DBA StackExchange. –  RolandoMySQLDBA Aug 23 '12 at 11:47

2 Answers 2

You should strictly check for the end of the loop inside the loop

create trigger my_trigger after delete on my_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; 

-- This is just for debugging 
insert into log_table(key, value) values('foo', num_rows); 

cursor_loop:repeat 
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value; 
    -- Do stuff 
    until done
end repeat; 

close my_cursor; 

end 

This would also eliminate have to check the count

Give it a Try !!!

Looking at your pseudcode

create trigger my_trigger after delete on my_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; 

-- This is just for debugging 
insert into log_table(key, value) values('foo', num_rows); 

cursor_loop:repeat 
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value; 
    -- Do stuff 

    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;

    until done
end repeat; 

close my_cursor; 

end 

It may not be a good idea to perform an UPDATE on my_table when you are right in the middle of an after DELETE trigger on the same table. Also, notice you are also causing a DELETE under if (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.

share|improve this answer
    
Thanks for your response. It didnt' work, but I think I've found the problem: inside the "Do stuff" part there's some 'select' sentences. If one of these sentences returns an empty result, the declared handler is executed and the loop is interrupted. Trying to figure out how to avoid that... –  jorgeas80 Aug 22 '12 at 17:57
    
Please insert the select stuff in the question so I can have a look... –  RolandoMySQLDBA Aug 22 '12 at 17:58
    
I've included it, cutting the irrelevant parts. –  jorgeas80 Aug 22 '12 at 18:08
    
Oh, sorry. The 'after delete' trigger is executed over one table different from the table in which I execute the 'update'/'delete' operations. Already edited that. –  jorgeas80 Aug 23 '12 at 8:47
up vote 1 down vote accepted

Solved. As said, the problem was the inner 'SELECT INTO...' sentence returns 0 records, triggering the CONTINUE HANDLER and setting done to TRUE. I fixed it by putting 'set done = false' just before fetching data from cursor, as suggested at http://dev.mysql.com/doc/refman/5.0/en/cursors.html (comment posted by David Bergan on February 23 2012 10:00pm).

So, the code finally looks like

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 not found set done = true; 

open my_cursor; 

my_loop: loop

    set done = false;

    fetch my_cursor into my_value; 

    if done then
      leave my_loop;
    end if;


    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;
    end if;
end loop my_loop; 

close my_cursor; 

end

Thanks to RolandoMySQLDBA for his answers.

share|improve this answer
1  
+1 for posting the research and the final code you made !!! –  RolandoMySQLDBA Aug 23 '12 at 11:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.