一致数据库表关系:院系信息:院系编号﹑院系名称﹑地址; 学生基本信息:院系编号﹑学号、姓名﹑性别﹑身份证号、电话﹑所需房费; 宿舍信息:id主键,区号﹑栋号﹑房间号﹑床位数﹑租金; 住宿信息:id主键,学号﹑区号﹑栋号﹑房间号﹑床号;。请编写出3个触发器并且实现其代码
时间: 2024-02-07 13:03:41 浏览: 102
1. 在每次插入学生基本信息时,更新宿舍信息表中对应房间的剩余床位数。
```sql
CREATE TRIGGER update_dormitory_bed_count AFTER INSERT ON student_info
FOR EACH ROW
UPDATE dormitory SET remaining_bed_count = remaining_bed_count - 1 WHERE
dormitory.area_code = NEW.area_code AND
dormitory.building_number = NEW.building_number AND
dormitory.room_number = NEW.room_number;
```
2. 在每次插入住宿信息时,更新宿舍信息表中对应房间的入住人数。
```sql
CREATE TRIGGER update_dormitory_occupancy AFTER INSERT ON accommodation
FOR EACH ROW
UPDATE dormitory SET occupancy_count = occupancy_count + 1 WHERE
dormitory.area_code = NEW.area_code AND
dormitory.building_number = NEW.building_number AND
dormitory.room_number = NEW.room_number;
```
3. 在每次修改宿舍信息的床位数时,更新对应房间的剩余床位数。
```sql
CREATE TRIGGER update_remaining_bed_count AFTER UPDATE OF bed_count ON dormitory
FOR EACH ROW
UPDATE dormitory SET remaining_bed_count = bed_count - occupancy_count WHERE
dormitory.area_code = NEW.area_code AND
dormitory.building_number = NEW.building_number AND
dormitory.room_number = NEW.room_number;
```
需要注意的是,这三个触发器中的字段名、表名、触发器名称等需要根据实际情况进行修改。同时,触发器的逻辑也需要根据实际需求进行调整。
阅读全文