数据库实验九：方案二触发器

班级表被update或insert的之前（before）检查

BEFORE INSERT

CREATE TRIGGER insert_before_c BEFORE INSERT ON class FOR EACH ROW 
 BEGIN
  DECLARE gradekey_x INT;
  DECLARE collegekey_x INT;
  DECLARE majorkey_x INT;
  DECLARE done1 INT DEFAULT 0;
  DECLARE done2 INT DEFAULT 0;
  DECLARE done3 INT DEFAULT 0;
  IF LENGTH(new.classkey) != 8 THEN 
   SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '输入班级号的长度非法';
  ELSE
   SET gradekey_x = SUBSTRING(new.classkey,1,2);
   SET collegekey_x = SUBSTRING(new.classkey,3,2);
   SET majorkey_x = SUBSTRING(new.classkey,5,2);
  END IF;
  
  #done123值默认为0，如果对一个classkey分解处理，发现能找到这个年级、这个学院、这个专业(前六位合法)，
  SELECT 1 INTO done1 WHERE EXISTS(SELECT gradekey FROM grade WHERE gradekey=gradekey_x);
  SELECT 1 INTO done2 WHERE EXISTS(SELECT collegekey FROM college WHERE collegekey=collegekey_x);
  SELECT 1 INTO done3 WHERE EXISTS(SELECT majorkey FROM major WHERE majorkey=majorkey_x);
 
  
  IF done1=1 && done2=1 && done3=1 THEN 
   SELECT '输入班级号正确' INTO @e1;
  ELSE 
  ELSE
	IF done1=0 THEN 
	SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '输入年级编号内容非法';
	ELSE  
	IF done2=0 THEN 
	SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '输入学院编号内容非法';
	ELSE
	IF done3=0 THEN 
	SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '输入专业编号内容非法';
	END IF;
    END IF;
	END IF;
	END IF;	
  
  END

BEFORE UPDATE（更新的话，新班级号可能和旧班级号一样）

CREATE TRIGGER update_before_c BEFORE UPDATE ON class FOR EACH ROW 
 BEGIN
  DECLARE gradekey_x INT;
  DECLARE collegekey_x INT;
  DECLARE majorkey_x INT;
  DECLARE done1 INT DEFAULT 0;
  DECLARE done2 INT DEFAULT 0;
  DECLARE done3 INT DEFAULT 0;
  DECLARE done4 INT DEFAULT 0;
  DECLARE done5 INT DEFAULT 1;
  IF LENGTH(new.classkey) != 8 THEN 
   SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '输入班级号的长度非法';
  ELSE
   SET gradekey_x = SUBSTRING(new.classkey,1,2);
   SET collegekey_x = SUBSTRING(new.classkey,3,2);
   SET majorkey_x = SUBSTRING(new.classkey,5,2);
  END IF;
  
  #done123值默认为0，如果对一个新classkey分解处理，发现能找到这个年级、这个学院、这个专业(前六位要合法),则置1
  #同时保证：1、旧班级号存在 2、如果新班级号不等于旧班级号则新班级号不能存在
  #（不然的话Update之后岂不是有两个一样的班级号，那么执行的时候一定会报错)
  SELECT 1 INTO done1 WHERE EXISTS(SELECT gradekey FROM grade WHERE gradekey=gradekey_x);
  SELECT 1 INTO done2 WHERE EXISTS(SELECT collegekey FROM college WHERE collegekey=collegekey_x);
  SELECT 1 INTO done3 WHERE EXISTS(SELECT majorkey FROM major WHERE majorkey=majorkey_x);
  SELECT 1 INTO done4 WHERE EXISTS(SELECT classkey FROM class WHERE classkey=old.classkey);
  IF done4=1 && new.classkey != old.classkey THEN
   SELECT 0 INTO done5 WHERE EXISTS(SELECT classkey FROM class WHERE classkey=new.classkey);
  END IF;
  
  IF done1=1 && done2=1 && done3=1 && done4=1 && done5=1 THEN 
   SELECT '输入班级号正确' INTO @e2;
  ELSE 
   SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '输入班级号内容非法';
  END IF;
  
  END

宿舍楼表在更新宿舍楼活动人数的时候，活动人数应始终小于宿舍楼入住总人数且大于等于零（默认不允许串宿舍或者是串到别的宿舍楼的也是不在自己宿舍楼，现实也是我的卡根本划不开别的男生宿舍楼）

CREATE TRIGGER update_before_d BEFORE UPDATE ON dormitory FOR EACH ROW 
 BEGIN
 IF new.living_stunum > old.dorm_stunum OR new.living_stunum < 0  THEN 
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '更新数据错误，无法更新宿舍楼表活动人数';
 END IF;
 END

更新宿舍房间信息表的房间入住人数的时候，判断不能使之超过容纳人数以及要>=0

CREATE TRIGGER update_before_r BEFORE UPDATE ON room FOR EACH ROW 
 BEGIN
 IF new.room_living_stunum <= old.room_max_stunum && new.room_living_stunum >= 0 THEN 
  SELECT '更新宿舍房间信息表中宿舍已入住人数成功！' INTO @e4;
 ELSE
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '更新数据错误，无法更新宿舍信息表此宿舍已入住人数';
 END IF;
 END 

向student表插入一条新的学生信息时

before 检查这个学号、班级号、宿舍楼号和房间号是否存在, 检查对应的房间入住人数是否已满

CREATE TRIGGER insert_before_s BEFORE INSERT ON student FOR EACH ROW 
 BEGIN
 DECLARE done1 INT DEFAULT 0;
 DECLARE done2 INT DEFAULT 0;
 DECLARE done3 INT DEFAULT 0;
 
 
 SELECT 1 INTO done1 WHERE EXISTS(SELECT classkey FROM class WHERE classkey=new.classkey);
 SELECT 1 INTO done2 WHERE EXISTS(SELECT dormkey,roomkey FROM room WHERE dormkey=new.dormkey && roomkey=new.roomkey);
 
 SELECT 1 INTO done3 WHERE EXISTS(SELECT room_max_stunum > room_living_stunum FROM room WHERE roomkey=new.roomkey && dormkey=new.dormkey);
 
 
 IF done2=0 THEN 
	SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '宿舍楼号或房间号非法';
 END IF;
 IF done1=0 THEN 
  SELECT '班级编号输入错误（没有这个班）' INTO @e7;
	SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '班级号非法';
 END IF;
 IF done3=0 THEN 
	  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该房间已经住满';
 END IF;
 
 IF done1=1 && done2=1 && done3=1  THEN 
  SELECT '允许插入此条新的学生信息' INTO @e9;
 ELSE
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '无法插入此条新的学生信息';
 END IF;
 
 END

after 插入新的学生信息之后： 房间入住人数信息更新，宿舍楼表更新入住总人数，班级表更新班级学生人数,学生进出宿舍信息表插入一条该学生进出宿舍信息，进出时间为插入时间（自动更新时间戳），状态位置默认置0（代表不在宿舍）

CREATE TRIGGER insert_after_s AFTER INSERT ON student FOR EACH ROW 
 BEGIN
 UPDATE room SET room_living_stunum = room_living_stunum + 1 WHERE roomkey=new.roomkey && dormkey=new.dormkey;
 UPDATE dormitory SET dorm_stunum = dorm_stunum + 1 WHERE dormkey=new.dormkey;
 UPDATE class SET class_stunum = class_stunum + 1 WHERE classkey=new.classkey;
 INSERT stu_dormitory(stukey,dormkey) VALUES(new.stukey,new.dormkey); 
 END

更新一条学生信息之后：班级编号更新：不同于旧班级号则旧班级人数减一，新班级人数加一；如果宿舍楼编号更新：旧宿舍人数减一，，新宿舍楼人数加一,学生进出信息表的宿舍编号跟着更新；宿舍房间号更新：旧宿舍房间人数减一，新宿舍房间人数加一；

CREATE TRIGGER update_after_s AFTER UPDATE ON student FOR EACH ROW 
 BEGIN
 IF new.classkey!=old.classkey THEN
 UPDATE  class SET class_stunum = class_stunum + 1 WHERE classkey = new.classkey;
 UPDATE  class SET class_stunum = class_stunum - 1 WHERE classkey = old.classkey;
 END IF;
 IF new.dormkey!=old.dormkey THEN
 UPDATE dormitory SET dorm_stunum = dorm_stunum + 1 WHERE dormkey = new.dormkey;
 UPDATE dormitory SET dorm_stunum = dorm_stunum - 1 WHERE dormkey = old.dormkey;
 UPDATE stu_dormitory SET dormkey = new.dormkey WHERE stukey= old.stukey;
 END IF;
 IF new.roomkey!=old.roomkey THEN
 UPDATE room SET room_living_stunum = room_living_stunum + 1 WHERE dormkey = new.dormkey && roomkey = new.roomkey;
 UPDATE room SET room_living_stunum = room_living_stunum - 1 WHERE dormkey = old.dormkey && roomkey = old.roomkey;
 END IF;
 END

删除一条新的学生信息时，房间入住人数信息更新，宿舍楼表更新入住总人数，班级表更新班级学生人数

这里必须是before，因为stu_dormitory表的stukey字段的外键引用的父表是sudent所以设置为删除时为cascade，也就是说当删除学生表一条信息的时候，会对应删除学生进出宿舍信息表的一条信息，题外话：如果不用cascade用restrict将不允许父表删除，

那么如果这里删除触发器是after的话，在执行删除student之后已经由于cascade级联地删除了学生出入信息表的对应那条信息，而这种级联操作并没有引起学生出入信息表的删除触发器工作，反而导致student表的删除触发器在after的条件下工作的时候，delete学生出入信息表的那条语句压根没执行，因为找不到stukey，它早就被级联地删除了，所以可能会出现在我们删除一条学生表的信息的时候，如果这个学生信息对应的学生出入信息表的状态位为1，也就是在宿舍，本来应该是删除学生信息，如果他在宿舍，删除对应的学生进出信息表的时候也就通过触发器将该宿舍楼活动人数减一，因为他学生信息删除了，不需要留在宿舍了（退学或者毕业啥的）

但是，如果这个触发器是after的话，上述这种我们希望的情况没有出现，而是在学生信息被删除后，宿舍活动人数没变。所以我把这个触发器变成before触发器，这样的话，在删除学生信息之前，就先删除学生出入信息表对应的信息，导致其触发器工作，判断是否在宿舍，如果在宿舍，将宿舍活动人数减一（因为学生信息即将被删除），最后才是删除student表的信息

CREATE TRIGGER delete_before_s BEFORE DELETE ON student FOR EACH ROW 
 BEGIN
 UPDATE dormitory SET dorm_stunum = dorm_stunum - 1  WHERE dormkey = old.dormkey;
 UPDATE room SET room_living_stunum = room_living_stunum - 1 WHERE dormkey = old.dormkey && roomkey = old.roomkey; 
 UPDATE class SET class_stunum = class_stunum - 1 WHERE classkey = old.classkey;
 DELETE FROM stu_dormitory WHERE stukey = old.stukey;
 END

学生进出宿舍信息表的更新与删除的触发器

更新之前：如果status被改变，用NOW函数取出当前系统时间赋给进/出时间（update触发器要更新自己的表不能写update，直接SET就行），同时如果状态位被更新为1，宿舍楼活动人数加一；状态位从1被更新为0，宿舍楼活动人数减一

CREATE TRIGGER update_before_sd BEFORE UPDATE ON stu_dormitory FOR EACH ROW 
 BEGIN
 IF new.status=1 && old.status=0 THEN
 UPDATE dormitory SET living_stunum = living_stunum + 1 WHERE dormkey = new.dormkey;
 SET new.goORget_time = NOW();
 END IF;
 IF new.status=0 && old.status=1 THEN 
 UPDATE dormitory SET living_stunum = living_stunum - 1 WHERE dormkey = new.dormkey;
 SET new.goORget_time = NOW(); 
 END IF;
 END

删除一条信息后，如果删除前状态位为1，宿舍楼活动人数减一

CREATE TRIGGER delete_after_sd AFTER DELETE ON stu_dormitory FOR EACH ROW 
 BEGIN
 IF old.status = 1 && old.status != 0 THEN
 UPDATE dormitory SET living_stunum = living_stunum - 1 WHERE dormkey = old.dormkey; 
 END IF;
 END