eventos gbd

create event nombre-evento
on shedule
at {date and time}
every{x}{second|minute|..}
do
sentencias sql

* la x es un numero

ejrecicio pag 126 4.7

1)use ebanca;
 create event if not exits comision_rojos
on shedule every 2 minute
starts '2014-01-24 12:07:00'
ends '2014-01-24 12:17:00'
do
update ebanca.cuenta set saldo=1.02*saldo where saldo <0;

//o
AT CURRENT_TIMESTAMP
28/1/14
2 CREAR TABLA TEMPRAL TENGA ID MOVIEMTO INT cuenta int catntidad in fecha date

DELIMITER $$

DROP event IF EXISTS `ebanca`.`472` $$
CREATE event `ebanca`.`472`  on shedule
every  1 day
starts '2014-01-28 23:00:00'
disable
do
BEGIN
declare vmov vcuenta int;
declare vfecha date;
declare vcant double;
declare fin_cursor bool;

declare cmov cursor for select idmov ,cod cuenta, cantidad fechahora
from movimiento where date (now())=fehahora and cantidad > 1000
declare continue HANDLER for not found set fin_cursor=1;
set fien_cursor=0;
open cmov;
fetch cmov into cmov , vcuenta , vcant,vfecha
while(finedecursor=0 do
insert into tmp-mov values(vmov ,vcuenta,vcant ,vfecha
fetch cmov into vmov , vcuenta vcant ,vfecha
end while;
close cmov;
END $$

DELIMITER ;

ejercicio 4 pg 127

DELIMITER $$

DROP trigger IF EXISTS `ebanca`.`427` $$
CREATE trigger `ebanca`.`427` after update on cuenta
for each row
BEGIN
declare vdni int;
if (new.saldo>0) then
select dni into vdni from cliente
where codigo_cliente = (select cod_cliente from cuenta);
insert into nrojos values(vdni ,old.cod_cuenta,now(), new.saldo);

end if;

END $$