学生成绩管理系统实验报告 - 图文

（5）专业编号

create index Major on Major_Hx(Mno_hx)

（6）学生班级索引

create index Students_Class on Students_Hx(Sno_hx,CLno_hx)

（7）学生成绩索引

create index Students_Grade on Study_Hx(Sno_hx,Cno_hx,STgrade_hx desc)

5.5触发器建立

（1）当删除教师表Teachers_Hx中的教师编号记录时，需要相应地删除授课表Teach_Hx和成绩表Study_Hx里的教师编号，所以在教师表Teachers上建立触发器

SQL语句是：

create trigger hx_delete_Tno

on Teachers_Hx for delete as

delete Tno_hx

where Teach_Hx.Tno_hx=(select Tno_hx from deleted) and Study_Hx.Tno_hx=(select Tno_hx from deleted) SQL执行结果是：

（2）当更新教师表Teachers_Hx中的教师编号记录时，需要相应地更新授课表Teach_Hx和成绩表Study_hx里的教师编号，所以在教师表Teachers_Hx上建立触发器

SQL语句是：

create trigger UPDATE_Teachers_Tno ON Teachers_Hx for update as if

update (Tno_hx) begin update Teach_Hx set Tno_hx = i.Tno_hx

from deleted d,inserted i ,Teachers_Hx k where k.Tno_hx=d.Tno_hx end begin update Study_Hx

set Tno_hx = i.Tno_hx

from deleted d,inserted i ,Study_Hx c where c.Tno_hx=d.Tno_hx End

SQL执行结果是：