DekkerBass

triggers.sql entr-3

Dec 26th, 2020 (edited)
801
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --REGLAS DE NEGOCIO
  2.  
  3. --RN-001
  4.  
  5. DELIMITER //
  6. CREATE OR REPLACE TRIGGER triggerLimitTeachingLoad
  7.     BEFORE INSERT ON TeachersGroups FOR EACH ROW
  8.     BEGIN
  9.             DECLARE acYear INT ;
  10.             DECLARE totalCredits INT;
  11.             SET acYear=(SELECT YEAR FROM TeachersGroups NATURAL JOIN
  12.         groups WHERE NEW.groupId=groupId);
  13.             SET totalCredits=(SELECT SUM(teachingLoad) FROM ViewTgg
  14.         WHERE (YEAR=acYear AND NEW.teacherId=teacherId));
  15.                 if ((totalCredits + NEW.teachingLoad)>24)then
  16.             SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Profesor no puede
  17.                 imartir mas de 24 creditos en un curso academico';
  18.                 END if ;
  19.             END //
  20. DELIMITER ;
  21.  
  22. --RN-003
  23. DELIMITER //
  24. CREATE OR REPLACE TRIGGER triggerTeachDepartment
  25.     BEFORE INSERT ON TeachersGroups FOR EACH ROW
  26.     BEGIN
  27.             DECLARE depIdTe INT ;
  28.             DECLARE depIdGr INT;
  29.             SET depIdTe=(SELECT departmentId FROM TeachersGroups NATURAL JOIN
  30.         teachers WHERE NEW.teacherId=teacherId);
  31.             SET depIdGr=(SELECT departmentId FROM teachersgroups NATURAL JOIN
  32.             groups NATURAL JOIN subjects WHERE NEW.groupId=groupId);
  33.                 if ((depIdTe !=depIdGr)then
  34.             SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Profesores que imparten
  35.             una asignatura deben pertenecer al departamento que la asume';
  36.                 END if ;
  37.             END //
  38. DELIMITER ;
  39.  
  40.  
  41. --RN-005
  42. DELIMITER //
  43. CREATE OR REPLACE TRIGGER triggerAppointmentsOK
  44.     BEFORE INSERT ON Appointments FOR EACH ROW
  45.     BEGIN
  46.             DECLARE dW VARCHAR (30);
  47.             DECLARE st TIME ;
  48.             DECLARE et TIME ;
  49.             SET dW=(SELECT dayWeek FROM ViewTutApp WHERE tutorilId=NEW.tutorialId);
  50.             SET st=(SELECT startTime FROM ViewTutApp WHERE tutorialId=NEW.tutorialId);
  51.             SET et=(SELECT endTime FROM ViewTutApp WHERE tutorialId=NEW.tutorialId);
  52.        
  53.                 if (DAYNAME(NEW.dateAppointment)!=dw OR st>NEW.hourAppointment or
  54.                     et<NEW.hourAppointment) then  
  55.             SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Una cita para tutoria tiene una hora y debe de
  56.             estar entra la hora del comienzo y de fin de la tutoria que corresponde
  57.             asi como por una fecha';
  58.                 END if ;
  59.             END //
  60. DELIMITER ;
  61.  
  62.  
  63. --RN-006
  64.  
  65. DELIMITER //
  66. CREATE OR REPLACE TRIGGER triggerwithHonoursInsert
  67.     BEFORE INSERT ON Grades FOR EACH ROW
  68.     BEGIN
  69.        
  70.                 if (NEW.withHonours=1 AND NEW.value<9.0) then  
  71.             SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Para obtener matricula hay que sacar almenos un 9';
  72.                 END if ;
  73.             END //
  74.  
  75.  
  76.  
  77. CREATE OR REPLACE TRIGGER triggerwithHonoursUpdate
  78.     BEFORE INSERT ON Grades FOR EACH ROW
  79.     BEGIN
  80.        
  81.                 if (NEW.withHonours=1 AND NEW.value<9.0) then  
  82.             SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Para obtener matricula hay que sacar almenos un 9';
  83.                 END if ;
  84.             END //
  85. DELIMITER;
  86.  
  87. --RN-007
  88.  
  89. DELIMITER //
  90. CREATE OR REPLACE TRIGGER triggerUniqueQualiSubject
  91.     BEFORE INSERT ON qualification FOR EACH ROW
  92.     BEGIN
  93.         DECLARE subject INT ;
  94.         DECLARE groupYear INT ;
  95.         DECLARE subjectGrades INT ;
  96.         SELECT subjectId,YEAR INTO subject,groupYear FROM groups WHERE groupId=new.groupId;
  97.             SET subjectGrades=(SELECT COUNT (*) FROM qualification,groups
  98.                 WHERE (qualification.studentId=NEW.studentId AND qualification.groupId=groups.groupId  AND qualification.gradeCall=NEW.gradeCall
  99.                     AND groups.year=groupYear AND groups.subjectId=subjectId));
  100.             if (subjectsGrades>0) then
  101.                 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Alumno no puede tener varias notas asociadas a la una misma asginatura
  102.                             en la misma convocatoria,el mismo año';
  103.             END if:
  104.             END //
  105.  
  106.  --RN-008
  107.  
  108.  
  109. DELIMITER //
  110. CREATE OR REPLACE TRIGGER triggerRestAge
  111.     BEFORE INSERT ON Students FOR EACH ROW
  112.     BEGIN
  113.             DECLARE age INT;
  114.             SET age =(SELECT YEAR(CURDATE())-YEAR (NEW.birthDateSt)+
  115.        
  116.                 if (DATE_FORMAT(CURDATE(),'%m-%d') > DATE_FORMAT(NEW.birthDateSt,'%m-%d'),0,-1)FROM students);
  117.                 if (age<16 smd NEW.accessMethod='Selectividad') then  
  118.             SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Un Alumno menor de 16 años no puede acceder por selectividad';
  119.                 END if ;
  120.             END //
  121.            
  122.            
  123. --RN-009
  124.  DELIMITER //
  125.  CREATE OR REPLACE TRIGGER QualiStudentGroup
  126.         BEFORE INSERT ON qualification FOR EACH ROW
  127.         BEGIN
  128.                 DECLARE isInGr INT;
  129.                 SET isInGr=(SELECT COUNT(*) FROM groupsstudents WHERE studentId=NEW.studentId AND groupId=NEW.groupId)
  130.                 if (isInGr<1) then
  131.                 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Un alumno no puede tener notas en grupos a los que no pertenece';
  132.                 END if ;
  133.         END //
  134. DELIMITER;
  135.  
  136.  
  137.  
  138.  
  139. --RN-010
  140.  DELIMITER //
  141.  CREATE OR REPLACE TRIGGER triggerQualifiactionChangeDiff
  142.         BEFORE INSERT ON qualification FOR EACH ROW
  143.         BEGIN
  144.                 DECLARE diff DECIMAL (4,2);
  145.                 DECLARE student ROW TYPE of students ;
  146.                 SET diff=NEW.value-OLD.value;
  147.                 if(diff>4)then
  148.                     SELECT * INTO student FROM students WHERE studentId=NEW.studentId;
  149.                     SET @error_message=CONCAT('Al alumno',student.firstName,' ',student.surname,
  150.                     'se le ha intentado subir la nota en', diff,'puntos');
  151.                     SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT=@error_message;
  152.                 END if ;
  153.         END //
  154. DELIMITER;
  155.  
RAW Paste Data