sql创建触发器当增加、删除学生和修改学生班级信息时自动修改相应班级学生人数;
时间: 2023-11-24 07:08:07 浏览: 646
数据库创建触发器
好的,针对你的问题,我可以给出以下 SQL 语句示例:
创建 BEFORE INSERT 触发器,当向学生表中插入数据时,自动更新相应班级的学生人数:
```
CREATE TRIGGER update_class_student_count
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
UPDATE classes
SET student_count = student_count + 1
WHERE class_id = NEW.class_id;
END;
```
创建 BEFORE DELETE 触发器,当从学生表中删除数据时,自动更新相应班级的学生人数:
```
CREATE TRIGGER update_class_student_count
BEFORE DELETE ON students
FOR EACH ROW
BEGIN
UPDATE classes
SET student_count = student_count - 1
WHERE class_id = OLD.class_id;
END;
```
创建 AFTER UPDATE 触发器,当修改学生表中班级信息时,自动更新相应班级的学生人数:
```
CREATE TRIGGER update_class_student_count
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
IF OLD.class_id <> NEW.class_id THEN
UPDATE classes
SET student_count = student_count - 1
WHERE class_id = OLD.class_id;
UPDATE classes
SET student_count = student_count + 1
WHERE class_id = NEW.class_id;
END IF;
END;
```
以上 SQL 语句示例仅供参考,具体实现需要根据实际情况进行调整。
阅读全文