CURSOR MYSQL TRIGGER

Question

It's the first time I use cursors and im having issues with the variable 'done'. This variable handles whether the loop iterates over the cursosr or not. When the trigger its executed, the following error happens:

ERROR 1193 (HY000): Unknown system variable 'done'

As far as i see in the mysql cursor documentation, the 'done' variable declaration is correct. Anyone sees the problem? The trigger:

delimiter //

CREATE TRIGGER `prestamo_positivo` AFTER UPDATE ON `prestamo`
FOR EACH ROW BEGIN
DECLARE aux VARCHAR(10);
DECLARE aux2 CHAR(10);
DECLARE c1 CURSOR FOR SELECT id_cliente FROM PRESTATARIO;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET aux=(SELECT A.id_cliente FROM PRESTATARIO A, PRESTAMO B WHERE A.numero_prestamo=OLD.numero);
IF(NEW.cantidad = 0) THEN
    DELETE FROM `prestamo` WHERE numero=OLD.numero;
    OPEN c1;
    c1_loop: LOOP
        fletch c1 into aux2;
        IF done THEN LEAVE c1_loop; END IF; 
        IF(aux = aux2) THEN
            INSERT INTO `mensaje`(mensaje, id_usuario) VALUES ("Renegociar prestamos del            cliente",aux);
        END IF;
    END LOOP c1_loop;
    CLOSE c1;
END IF;
END; //

isn't it declared in line 6 @Akina? What's wrong with deleting @P.Salmon? — deadlock, May 18 '20 at 13:41
You cannot action the table which fired the trigger in the trigger. And done is not DECLAREd — P.Salmon, May 18 '20 at 13:48
No. Line 6 declares cursor with the action over this variable which must be declared above. — Akina, May 18 '20 at 13:55

Shadow · Accepted Answer · 2020-05-18T13:54:23.333

If you check the sample code you linked in the question, you can see that done variable is explicitly declared before the continue handler, so you need to do the same. The below excerpt is from the sample code from the link you specified:

DECLARE done INT DEFAULT FALSE; //<= declare done variable here
DECLARE a CHAR(16);
DECLARE b, c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

Also, as P.Salmon has already remarked, you cannot modify the table the trigger is declared on, therefore

DELETE FROM `prestamo` WHERE numero=OLD.numero;

line will fail with error 1442.

CURSOR MYSQL TRIGGER

1 Answers1