（一）实验任务一：
1、 创建触发器
在学生信息管理系统中，学生信息表包含列“学号”、“姓名”、“性别”、“出生年月”、“班级号”；班级信息表中包含列“班级号”、“班级名称”、“人数”；课程信息表包含列“课程代号”、“课程名称”；学生成绩表包含列“学号”、列“课程代号”、列“成绩”，已用约束保证成绩的范围为0~100分。（用附录中的脚本创建）
--1）在student上创建INSERT触发器stu_insert，要求在student表中插入记录时(要求每次只能插入一条记录)，这个触发器都将更新class表中的class_nun列。并测试触发器stu_insert。
create trigger stu_insert
on student for insert
as
if @@rowcount>1
begin
RAISERROR('You cannot insert more than one student at a time.', 16, 1)
ROLLBACK TRAN
return
--注意此处的return语句不能省略，因为在触发器脚本中的ROLLBACK TRAN语句之后还存在语句，那么将会执行这些语句，而为了其后的语句不执行，必须加入return语句
end
update class
set class_num=class_num+1
where class_id=(select class_id from inserted)
print 'class表中数据更新成功'
go
--测试1
insert into student
select '0601012','丽','女','1986-07-11', '0601' union all
select '0601013','梅','女','1988-02-07', '0601'
--测试2
insert into student values('0602011','文','女','1986-09-21', '0602')
--2）修改题1中创建的INSERT触发器stu_insert，要求在student表中插入记录时(允许插入多条记录)，这个触发器都将更新class表中的class_nun列。并测试触发器stu_insert。
alter trigger stu_insert
on student for insert
as
update class
set class_num=class_num+(select count(class_id) from inserted where class.class_id=inserted.class_id )
print 'class表中数据更新成功'
go
--测试1
insert into student
select '0601012','丽','女','1986-07-11', '0601' union all
select '0601013','梅','女','1988-02-07', '0601'
--测试2
insert into student values('0602011','文','女','1986-09-21', '0602')
--3）在student上创建DELETE触发器stu_delete，要求在student表中删除记录时，这个触发器都将更新class表中的class_nun列。并测试触发器stu_delete。
create trigger stu_delete
on student for delete
as
update class
set class_num=class_num-(select count(class_id) from deleted where class.class_id=deleted.class_id )
go
--测试1
delete from student where stu_id='0601001'
--测试2
delete from student
--4）为防止其他人修改成绩，在score上创建UPDATE触发器sc_update，要求不能更新score表中的score列。测试触发器sc_update。
create trigger sc_update
on score for update
as
if update(score)
begin
print '不允许修改score列'
rollback tran
end
go
--尝试修改score列
update score
set score=99
2、查看触发器相关信息:使用系统存储过程sp_help，sp_helptext
，sp_helptrigger查看触发器相关信息。
exec sp_help
exec sp_help sc_update
exec sp_helptext sc_update
exec sp_helptrigger student
exec sp_helptrigger student ,'insert'
--附录:
--创建数据库,准备数据
create database student_score
GO
--在数据库中创建三个表的结构
use student_score
GO
create table student
( stu_id char(8) primary key,
stu_name char(10),
stu_sex char(2),
stu_birthday smalldatetime,
class_id char(6)
)
go
create table class
( class_id char(6) primary key,
class_name varchar(30),
class_num int,
)
create table course
( course_id char(3) primary key,
course_name varchar(30),
)
go
create table score
( stu_id char(8),
course_id char(3),
score int check(score>=0 and score<=100)
primary key(stu_id,course_id)
)
go
--往表中插入数据(student,course,score)
insert into student values('0601001','李玉','女','1987-05-06', '0601')
insert into student values('0601002','鲁敏','女','1988-06-28', '0601')
insert into student values('0601003','李小路','女','1987-01-08', '0601')
insert into student values('0601004','鲁斌','男','1988-04-21', '0601')
insert into student values('0601005','王宁静','女','1986-05-29', '0601')
insert into student values('0601006','张明明','男','1987-02-24', '0601')
insert into student values('0601007','刘晓玲','女','1988-12-21', '0601')
insert into student values('0601008','周晓','男','1986-04-27', '0601')
insert into student values('0601009','易国梁','男','1985-11-26', '0601')
insert into student values('0601010','季风','男','1986-09-21', '0601')
insert into class values('0501','计算机办公应用', 40)
insert into class values('0502','网络构建', 43)
insert into class values('0503','图形图像', 48)
insert into class values('0601','可视化', 41)
insert into class values('0602','数据库', 38)
insert into class values('0603','网络管理', 45)
insert into class values('0604','多媒体', 40)
insert into class values('0701','计算机办公应用', 39)
insert into class values('0702','WEB应用', 38)
insert into class values('0703','网络构建', 40)
insert into course values('001','计算机应用基础')
insert into course values('002','关系数据基础')
insert into course values('003','程序设计基础')
insert into course values('004','数据结构')
insert into course values('005','网页设计')
insert into course values('006','网站设计')
insert into course values('007','SQL Server 2000关系数据库')
insert into course values('008','SQL Server 2000程序设计')
insert into course values('009','计算机网络')
insert into course values('010','Windows Server 配置')
insert into score values('0601001','001',78)
insert into score values('0601002','001',88)
insert into score values('0601003','001',65)
insert into score values('0601004','001',76)
insert into score values('0601005','001',56)
insert into score values('0
601006','001',87)
insert into score values('0601007','001',67)
insert into score values('0601008','001',95)
insert into score values('0601009','001',98)
insert into score values('0601010','001',45)
insert into score values('0601001','002',48)
insert into score values('0601002','002',68)
insert into score values('0601003','002',95)
insert into score values('0601004','002',86)
insert into score values('0601005','002',76)
insert into score values('0601006','002',57)
insert into score values('0601007','002',77)
insert into score values('0601008','002',85)
insert into score values('0601009','002',98)
insert into score values('0601010','002',75)
insert into score values('0601001','003',88)
insert into score values('0601002','003',78)
insert into score values('0601003','003',65)
insert into score values('0601004','003',56)
insert into score values('0601005','003',96)
insert into score values('0601006','003',87)
insert into score values('0601007','003',77)
insert into score values('0601008','003',65)
insert into score values('0601009','003',98)
insert into score values('0601010','003',75)
insert into score values('0601001','004',74)
insert into score values('0601002','004',68)
insert into score values('0601003','004',95)
insert into score values('0601004','004',86)
insert into score values('0601005','004',76)
insert into score values('0601006','004',67)
insert into score values('0601007','004',77)
insert into score values('0601008','004',85)
insert into score values('0601009','004',98)
insert into score values('0601010','004',75)
insert into score values('0601001','005',74)
insert into score values('0601002','005',68)
insert into score values('0601005','005',76)
insert into score values('0601008','005',85)
insert into score values('0601009','005',98)
insert into score values('0601010','005',75)
insert into score values('0601002','006',88)
insert into score values('0601003','006',95)
insert into score values('0601006','006',77)
insert into score values('0601008','006',85)
insert into score values('0601010','006',55)
insert into score values('0601001','007',84)
insert into score values('0601002','007',68)
insert into score values('0601003','007',95)
insert into score values('0601004','008',86)
insert into score values('0601005','008',76)
insert into score values('0601006','008',67)
insert into score values('0601007','009',67)
insert into score values('0601008','009',85)
insert into score values('0601009','010',98)
insert into score values('0601010','010',75)