帮我写10条宿舍管理系统的触发器，字段你来取，给出详细代码

好的，以下是10条针对宿舍管理系统的触发器，每个触发器都有对应的详细代码。值得注意的是，这里只是提供一些示例触发器，具体触发器的设计应该根据你的具体需求和数据库结构进行。

1. 当有学生添加到宿舍时，自动更新宿舍的剩余床位数。

CREATE TRIGGER update_dormitory_on_student_insert AFTER INSERT ON student
FOR EACH ROW
BEGIN
   UPDATE dormitory SET remaining_beds = remaining_beds - 1 WHERE dorm_id = NEW.dorm_id;
END;

2. 当有学生从宿舍中删除时，自动更新宿舍的剩余床位数。

CREATE TRIGGER update_dormitory_on_student_delete AFTER DELETE ON student
FOR EACH ROW
BEGIN
   UPDATE dormitory SET remaining_beds = remaining_beds + 1 WHERE dorm_id = OLD.dorm_id;
END;

3. 当有学生从宿舍A转移到宿舍B时，自动更新宿舍A和宿舍B的剩余床位数。

CREATE TRIGGER update_dormitory_on_student_update AFTER UPDATE ON student
FOR EACH ROW
BEGIN
   IF OLD.dorm_id <> NEW.dorm_id THEN
      UPDATE dormitory SET remaining_beds = remaining_beds + 1 WHERE dorm_id = OLD.dorm_id;
      UPDATE dormitory SET remaining_beds = remaining_beds - 1 WHERE dorm_id = NEW.dorm_id;
   END IF;
END;

4. 当宿舍的最大床位数被修改时，自动更新该宿舍的剩余床位数。

CREATE TRIGGER update_dormitory_on_max_beds_update AFTER UPDATE ON dormitory
FOR EACH ROW
BEGIN
   UPDATE dormitory SET remaining_beds = NEW.max_beds - (NEW.max_beds - remaining_beds) WHERE dorm_id = NEW.dorm_id;
END;

5. 当有新的宿舍被添加时，自动添加一个对应的房间。

CREATE TRIGGER add_room_on_dormitory_insert AFTER INSERT ON dormitory
FOR EACH ROW
BEGIN
   INSERT INTO room (room_num, dorm_id) VALUES (1, NEW.dorm_id);
END;

6. 当有新的房间被添加到宿舍中时，自动更新宿舍的房间数。

CREATE TRIGGER update_dormitory_rooms_on_room_insert AFTER INSERT ON room
FOR EACH ROW
BEGIN
   UPDATE dormitory SET num_rooms = num_rooms + 1 WHERE dorm_id = NEW.dorm_id;
END;

7. 当有房间从宿舍中删除时，自动更新宿舍的房间数。

CREATE TRIGGER update_dormitory_rooms_on_room_delete AFTER DELETE ON room
FOR EACH ROW
BEGIN
   UPDATE dormitory SET num_rooms = num_rooms - 1 WHERE dorm_id = OLD.dorm_id;
END;

8. 当有新的维修请求被添加时，自动将该请求分配给一个负责人。

CREATE TRIGGER assign_maintenance_on_maintenance_insert AFTER INSERT ON maintenance
FOR EACH ROW
BEGIN
   UPDATE maintenance SET assigned_to = (SELECT staff_id FROM staff WHERE job_title = 'Maintenance' ORDER BY RAND() LIMIT 1) WHERE maintenance_id = NEW.maintenance_id;
END;

9. 当有新的楼层被添加到宿舍中时，自动添加对应数量的房间和床位数。

CREATE TRIGGER add_rooms_on_floor_insert AFTER INSERT ON floor
FOR EACH ROW
BEGIN
   DECLARE @i INT DEFAULT 0;
   WHILE @i < NEW.num_rooms DO
      INSERT INTO room (room_num, dorm_id) VALUES ((SELECT COUNT(*) FROM room WHERE dorm_id = NEW.dorm_id) + 1, NEW.dorm_id);
      SET @i = @i + 1;
   END WHILE;
   UPDATE dormitory SET max_beds = max_beds + NEW.num_rooms * NEW.beds_per_room, remaining_beds = remaining_beds + NEW.num_rooms * NEW.beds_per_room WHERE dorm_id = NEW.dorm_id;
END;

10. 当有房间被删除时，自动更新宿舍的床位数。

CREATE TRIGGER update_dormitory_on_room_delete AFTER DELETE ON room
FOR EACH ROW
BEGIN
   UPDATE dormitory SET max_beds = max_beds - (SELECT beds_per_room FROM floor WHERE floor_id = OLD.floor_id) WHERE dorm_id = OLD.dorm_id;
   UPDATE dormitory SET remaining_beds = remaining_beds - (SELECT beds_per_room FROM floor WHERE floor_id = OLD.floor_id) WHERE dorm_id = OLD.dorm_id;
END;