1、触发器练习题1、 创建触发器在学生信息管理系统中，学生信息表包含列“学号” 、 “姓名” 、 “性别” 、 “出生年月” 、 “班级号” ；班级信息表中包含列“班级号” 、 “班级名称” 、 “人数” ；课程信息表包含列“课程代号” 、 “课程名称” ；学生成绩表包含列“学号” 、列“课程代号” 、列“成绩” ，已用约束保证成绩的范围为 0100 分。 （用附录中的脚本创建）-1）在 student 上创建 INSERT 触发器 stu_insert，要求在 student 表中插入记录时(要求每次只能插入一条记录)，这个触发器都将更新 class 表中的 class_nun 列。并测试触发器
2、stu_insert。create trigger stu_inserton student for insertasif rowcount1beginRAISERROR(You cannot insert more than one student at a time., 16, 1)ROLLBACK TRANreturn-注意此处的 return 语句不能省略，因为在触发器脚本中的 ROLLBACK TRAN 语句之后还存在语句，那么将会执行这些语句，而为了其后的语句不执行，必须加入 return 语句endupdate classset class_num=class_num+1wher
3、e class_id=(select class_id from inserted)print class 表中数据更新成功go-测试 1insert into student select 0601012,丽,女,1986-07-11, 0601 union allselect 0601013,梅,女,1988-02-07, 0601-测试 2insert into student values(0602011,文,女,1986-09-21, 0602)-2）修改题 1 中创建的 INSERT 触发器 stu_insert，要求在 student 表中插入记录时(允许插入多条记录)，这个触发
4、器都将更新 class 表中的 class_nun 列。并测试触发器 stu_insert。alter trigger stu_inserton student for insertasupdate classset class_num=class_num+(select count(class_id) from inserted where class.class_id=inserted.class_id )print class 表中数据更新成功go-测试 1insert into student select 0601012,丽,女,1986-07-11, 0601 union alls
5、elect 0601013,梅,女,1988-02-07, 0601-测试 2insert into student values(0602011,文,女,1986-09-21, 0602)-3）在 student 上创建 DELETE 触发器 stu_delete，要求在 student 表中删除记录时，这个触发器都将更新 class 表中的 class_nun 列。并测试触发器 stu_delete。create trigger stu_deleteon student for deleteasupdate classset class_num=class_num-(select coun
6、t(class_id) from deleted where class.class_id=deleted.class_id )go-测试 1delete from student where stu_id=0601001 -测试 2delete from student-4）为防止其他人修改成绩，在 score 上创建 UPDATE 触发器 sc_update，要求不能更新score 表中的 score 列。测试触发器 sc_update。create trigger sc_updateon score for updateasif update(score)beginprint 不允许修改
7、 score 列rollback tranendgo-尝试修改 score 列update scoreset score=992、查看触发器相关信息:使用系统存储过程 sp_help，sp_helptext，sp_helptrigger 查看触发器相关信息。exec sp_helpexec sp_help sc_updateexec sp_helptext sc_updateexec sp_helptrigger student exec sp_helptrigger student ,insert-附录:-创建数据库,准备数据create database student_scoreGO-在
8、数据库中创建三个表的结构use student_scoreGOcreate table student( stu_id char(8) primary key,stu_name char(10),stu_sex char(2),stu_birthday smalldatetime,class_id char(6)gocreate table class( class_id char(6) primary key,class_name varchar(30),class_num int,)create table course( course_id char(3) primary key,cou
9、rse_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 in
10、to 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
11、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(060
12、1,可视化, 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 cou
13、rse 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,计算
14、机网络)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(0601006,001,87)insert i
15、nto 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(060
16、1004,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 i
17、nto 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(060
18、1009,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 i
19、nto 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(060
20、1008,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 i
21、nto 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)
