triggers.sql entr-3

DekkerBass

Dec 26th, 2020 (edited)

1,754

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

MySQL 4.92 KB | None | 0 0

raw download clone embed print report

--REGLAS DE NEGOCIO
--RN-001
DELIMITER //
CREATE OR REPLACE TRIGGER triggerLimitTeachingLoad
BEFORE INSERT ON TeachersGroups FOR EACH ROW
BEGIN
DECLARE acYear INT ;
DECLARE totalCredits INT;
SET acYear=(SELECT YEAR FROM TeachersGroups NATURAL JOIN
groups WHERE NEW.groupId=groupId);
SET totalCredits=(SELECT SUM(teachingLoad) FROM ViewTgg
WHERE (YEAR=acYear AND NEW.teacherId=teacherId));
if ((totalCredits + NEW.teachingLoad)>24)then
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Profesor no puede
imartir mas de 24 creditos en un curso academico';
END if ;
END //
DELIMITER ;
--RN-003
DELIMITER //
CREATE OR REPLACE TRIGGER triggerTeachDepartment
BEFORE INSERT ON TeachersGroups FOR EACH ROW
BEGIN
DECLARE depIdTe INT ;
DECLARE depIdGr INT;
SET depIdTe=(SELECT departmentId FROM TeachersGroups NATURAL JOIN
teachers WHERE NEW.teacherId=teacherId);
SET depIdGr=(SELECT departmentId FROM teachersgroups NATURAL JOIN
groups NATURAL JOIN subjects WHERE NEW.groupId=groupId);
if ((depIdTe !=depIdGr)then
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Profesores que imparten
una asignatura deben pertenecer al departamento que la asume';
END if ;
END //
DELIMITER ;
--RN-005
DELIMITER //
CREATE OR REPLACE TRIGGER triggerAppointmentsOK
BEFORE INSERT ON Appointments FOR EACH ROW
BEGIN
DECLARE dW VARCHAR (30);
DECLARE st TIME ;
DECLARE et TIME ;
SET dW=(SELECT dayWeek FROM ViewTutApp WHERE tutorilId=NEW.tutorialId);
SET st=(SELECT startTime FROM ViewTutApp WHERE tutorialId=NEW.tutorialId);
SET et=(SELECT endTime FROM ViewTutApp WHERE tutorialId=NEW.tutorialId);
if (DAYNAME(NEW.dateAppointment)!=dw OR st>NEW.hourAppointment or
et<NEW.hourAppointment) then
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Una cita para tutoria tiene una hora y debe de
estar entra la hora del comienzo y de fin de la tutoria que corresponde
asi como por una fecha';
END if ;
END //
DELIMITER ;
--RN-006
DELIMITER //
CREATE OR REPLACE TRIGGER triggerwithHonoursInsert
BEFORE INSERT ON Grades FOR EACH ROW
BEGIN
if (NEW.withHonours=1 AND NEW.value<9.0) then
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Para obtener matricula hay que sacar almenos un 9';
END if ;
END //
CREATE OR REPLACE TRIGGER triggerwithHonoursUpdate
BEFORE INSERT ON Grades FOR EACH ROW
BEGIN
if (NEW.withHonours=1 AND NEW.value<9.0) then
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Para obtener matricula hay que sacar almenos un 9';
END if ;
END //
DELIMITER;
--RN-007
DELIMITER //
CREATE OR REPLACE TRIGGER triggerUniqueQualiSubject
BEFORE INSERT ON qualification FOR EACH ROW
BEGIN
DECLARE subject INT ;
DECLARE groupYear INT ;
DECLARE subjectGrades INT ;
SELECT subjectId,YEAR INTO subject,groupYear FROM groups WHERE groupId=new.groupId;
SET subjectGrades=(SELECT COUNT (*) FROM qualification,groups
WHERE (qualification.studentId=NEW.studentId AND qualification.groupId=groups.groupId AND qualification.gradeCall=NEW.gradeCall
AND groups.year=groupYear AND groups.subjectId=subjectId));
if (subjectsGrades>0) then
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Alumno no puede tener varias notas asociadas a la una misma asginatura
en la misma convocatoria,el mismo año';
END if:
END //
--RN-008
DELIMITER //
CREATE OR REPLACE TRIGGER triggerRestAge
BEFORE INSERT ON Students FOR EACH ROW
BEGIN
DECLARE age INT;
SET age =(SELECT YEAR(CURDATE())-YEAR (NEW.birthDateSt)+
if (DATE_FORMAT(CURDATE(),'%m-%d') > DATE_FORMAT(NEW.birthDateSt,'%m-%d'),0,-1)FROM students);
if (age<16 smd NEW.accessMethod='Selectividad') then
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Un Alumno menor de 16 años no puede acceder por selectividad';
END if ;
END //
--RN-009
DELIMITER //
CREATE OR REPLACE TRIGGER QualiStudentGroup
BEFORE INSERT ON qualification FOR EACH ROW
BEGIN
DECLARE isInGr INT;
SET isInGr=(SELECT COUNT(*) FROM groupsstudents WHERE studentId=NEW.studentId AND groupId=NEW.groupId)
if (isInGr<1) then
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Un alumno no puede tener notas en grupos a los que no pertenece';
END if ;
END //
DELIMITER;
--RN-010
DELIMITER //
CREATE OR REPLACE TRIGGER triggerQualifiactionChangeDiff
BEFORE INSERT ON qualification FOR EACH ROW
BEGIN
DECLARE diff DECIMAL (4,2);
DECLARE student ROW TYPE of students ;
SET diff=NEW.value-OLD.value;
if(diff>4)then
SELECT * INTO student FROM students WHERE studentId=NEW.studentId;
SET @error_message=CONCAT('Al alumno',student.firstName,' ',student.surname,
'se le ha intentado subir la nota en', diff,'puntos');
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT=@error_message;
END if ;
END //
DELIMITER;