Ubuntu-fr

Naemesis

Update automatique MySQL

Bonjour,

Je cherche a faire un update automatique en utilisant les events MySQL.

Lorsque les données que j'utilise proviennent d'une seule table, l'update se fait correctement, mais lorsque je cherche des données dans d'autre tables, l'update ne se fait plus.

Voici le code de mon event :

delimiter |

create event test 
on schedule every 1 minute
do
begin 
  declare p,g,b,pp,pg,pb,i,ap,ag,ab,np,ng,nb,ep,eg,eb INT;
  declare done INT default 0;
  declare c cursor for select IDVILLAGE, PIERRE, GOLD, BOIS, PRODPIERRE, PRODGOLD, PRODBOIS, EPIERRE, EGOLD, EBOIS from VILLAGE;
  declare continue handler for sqlstate '02000' set done = 1;
  open c;
  repeat
    fetch c into i,p,g,b,pp,pg,pb,ep,eg,eb;
    select ab.NIVEAUAMEL into ap from AMELBAT ab, BATIMENTVILLAGE bv where ab.IDBAT=bv.IDBATVIL and bv.IDVILLAGE=iv and ab.IDAMELIORATION in (79, 82, 85);
    select ab.NIVEAUAMEL into ag from AMELBAT ab, BATIMENTVILLAGE bv where ab.IDBAT=bv.IDBATVIL and bv.IDVILLAGE=iv and ab.IDAMELIORATION in (80, 83, 86);
    select ab.NIVEAUAMEL into ab from AMELBAT ab, BATIMENTVILLAGE bv where ab.IDBAT=bv.IDBATVIL and bv.IDVILLAGE=iv and ab.IDAMELIORATION in (81, 84, 87);
    set p = p+(1+(ap/20))*pp;
    set g = g+(1+(ag/20))*pg;
    set b = b+(1+(ab/20))*pb;
    if p>ep then
      set p=ep;
    end if;
    if g>eg then
      set g=eg;
    end if;
    if b>eb then
      set b=eb;
    end if;
    update VILLAGE set PIERRE=p, BOIS=b, GOLD=g where IDVILLAGE=i;
  until done end repeat;
  close c;
end |

delimiter ;