CREATE VIEW medianotas as
SELECT AVG(n.nota) AS media, a.id
FROM notas n RIGHT JOIN alumnos a
ON n.fk_alumno = a.id
GROUP BY a.id;
UPDATE alumnos a
SET a.notamedia = (SELECT mn.media
FROM medianotas mn WHERE mn.id = a.id);
DROP VIEW if EXISTS medianotasmod;
CREATE VIEW medianotasmod as
SELECT AVG(n.nota) AS media, m.id
FROM notas n RIGHT JOIN modulos m
ON n.fk_modulo = m.id
GROUP BY m.id;
UPDATE modulos m
SET m.notamedia = (SELECT mn.media
FROM medianotasmod mn WHERE mn.id = m.id);
CREATE DEFINER=`3eva`@`%` TRIGGER `notas_after_update` AFTER UPDATE ON `notas` FOR EACH ROW BEGIN
if NEW.fk_alumno <> OLD.fk_alumno then
SELECT nombre INTO @aluviejo FROM alumnos WHERE id=OLD.fk_alumno;
SELECT nombre INTO @alunuevo FROM alumnos WHERE id=NEW.fk_alumno;
INSERT INTO LOGS VALUES (NULL,NOW(),
CONCAT_WS("-",USER(),"modificado nombre alumno en notas:",
@aluviejo,"->",@alunuevo));
END if;
if NEW.fk_modulo <> OLD.fk_modulo then
SELECT nombre INTO @moduloviejo FROM modulos WHERE id=old.fk_modulo;
SELECT nombre INTO @modulonuevo FROM modulos WHERE id=new.fk_modulo;
INSERT INTO LOGS VALUES (NULL,NOW(),
CONCAT_WS("-",USER(),"modificado nombre modulo en notas:",
@moduloviejo,"->",@modulonuevo));
END if;
if NEW.nota <> OLD.nota then
INSERT INTO LOGS VALUES (NULL,NOW(),
CONCAT_WS("-",USER(),"modificado nota en notas:",
OLD.nota,"->",NEW.nota));
END if;
END
CREATE DEFINER=`3eva`@`%` TRIGGER `notas_before_insert` BEFORE INSERT ON `notas` FOR EACH ROW BEGIN
if NEW.nota <0 then
SET NEW.nota:=0;
else
if NEW.nota >10 then
SET NEW.nota := 10;
END if;
END if;
END
CREATE DEFINER=`3eva`@`%` TRIGGER `notas_before_update` BEFORE UPDATE ON `notas` FOR EACH ROW BEGIN
if not(NEW.nota = OLD.nota) then
if NEW.nota <0 then
SET NEW.nota:=0;
else
if NEW.nota >10 then
SET NEW.nota := 10;
END if;
END if;
END if;
END
CREATE DEFINER=`3eva`@`%` TRIGGER `registra_notas` AFTER INSERT ON `notas` FOR EACH ROW BEGIN
SELECT nombre INTO @alu FROM alumnos WHERE id=NEW.fk_alumno;
SELECT nombre INTO @modulo FROM modulos WHERE id=NEW.fk_modulo;
INSERT INTO LOGS VALUES (NULL,NOW(),
CONCAT_WS("-",USER(),"insertando nota",@alu,@modulo,NEW.nota));
END
CREATE DEFINER=`eva3`@`%` TRIGGER `notas_after_update` AFTER UPDATE ON `notas` FOR EACH ROW BEGIN
DECLARE COMBI INT;
SET COMBI := 0;
IF NEW.fk_alumno != OLD.fk_alumno then
set COMBI:= COMBI +1;
END IF;
IF NEW.fk_modulo != OLD.fk_modulo then
set COMBI:= COMBI +2;
END IF;
IF NEW.nota != OLD.nota then
set COMBI:= COMBI + 4;
END IF;
if (combi MOD 2) = 1 OR combi>=4 then
UPDATE alumnos a SET a.notamedia = (SELECT AVG(n.nota)
FROM notas n WHERE n.fk_alumno = NEW.fk_alumno)
WHERE a.id = NEW.fk_alumno;
UPDATE alumnos a SET notamedia = (SELECT AVG(n.nota)
FROM notas n WHERE n.fk_alumno = old.fk_alumno)
WHERE a.id = old.fk_alumno;
END if;
if combi>=2 then
UPDATE modulos m SET m.notamedia = (SELECT AVG(n.nota)
FROM notas n WHERE n.fk_modulo = NEW.fk_modulo)
WHERE m.id = NEW.fk_modulo;
UPDATE modulos m SET m.notamedia = (SELECT AVG(n.nota)
FROM notas n WHERE n.fk_modulo = old.fk_modulo)
WHERE m.id = old.fk_modulo;
END if;
END