Question

创建触发器当新增、修改或删除学生时自动修改相应班级的学生人数？

大神解答！！！！急！！！后天要交作业了 这是我已经在数据库中创建好的表//插入表student create table student(num int(10) not null unique primary key auto_increment,name varchar(20) not null,age int(10) not null, sex varchar(4) not null, alter sex enum(‘男’,’女’) not naddress varchar(50) not null,idnumber int(50) not null)engine=innodb default charset =utf8 collate=utf8_bin;//插入表coursecreate table course (C_id int(10) not null primary key unique auto_increment, course varchar(40) not null,C_teacher varchar(20) not null)engine=innodb default charset=utf8 collate=utf8_bin;//插入表 Awardpunishcreate table awardpunish(a_id int(10) primary key not null unique,award varchar(50) not null,punish varchar(50) not null)engine=innodb default charset=utf8 collate=utf8_bin;//插入表 scorecreate table score(a_id int(10) primary key not null unique,num int(10) not null unique,score int(10) not null,constraint score_fk foreign key(num)references student (num))engine=innodb default charset=utf8 collate=utf8_bin;//插入表markcreate table mark(num int(10) not null,c_id int(10) not null,grade int not null default 0 ,primary key(num,c_id))engine=MyISAM default charset=utf8 collate=utf8_bin;//插入表classcreate table class(b_id int(4) not null primary key,mentor varchar(4) not null,c_number int(4) not null)engine=innodb default charset utf8 collate=utf8_bin;//插入表departmentcreate table department(D_id int(10) not null unique primary key,director varchar(20) not null,D_name varchar(40) not null) engine=innodb default charset=utf8 collate=utf8_bin; //插入表majorcreate table major(M_id int(10) not null unique primary key,M_name varchar(40) not null unique,M_number int(10) not null) engine=innodb default charset=utf8 collate=utf8_bin;

Answer 1

按照你的意思 似乎只需要根据成绩表的变化来修改课程表中的人数，可以这样写：
create trigger tri_adjust_renshu
on grade
for insert,delete
as
begin
update course set renshu=renshu+1
where cno in {select cno from inserted} --删除时 inserted是空集，插入时非空
update course set renshu=renshu-1
where cno in {select cno from deleted} --插入时 inserted是空集，删除时非空
end
go