SQL Server2008触发器练习题.doc

触发器练习题1、创建触发器在学生信息管理系统中，学生信息表包含列“学号”、“姓名”、“性别”、“出生年月”、“班级号”；班级信息表中包含列“班级号”、“班级名称”、“人数”；课程信息表包含列“课程代号”、“课程名称”；学生成绩表包含列“学号”、列“课程代号”、列“成绩”，已用约束保证成绩的范围为0100分。（用附录中的脚本创建）-1）在student上创建INSERT触发器stu_insert，要求在student表中插入记录时(要求每次只能插入一条记录)，这个触发器都将更新class表中的class_nun列。并测试触发器stu_insert。create trigger stu_inserton student for insertasif rowcount1begin RAISERROR(You cannot insert more than one student at a time., 16, 1) ROLLBACK TRAN return-注意此处的return语句不能省略，因为在触发器脚本中的ROLLBACK TRAN语句之后还存在语句，那么将会执行这些语句，而为了其后的语句不执行，必须加入return语句endupdate classset class_num=class_num+1where 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表中插入记录时(允许插入多条记录)，这个触发器都将更新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 allselect 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 count(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 不允许修改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-在数据库中创建三个表的结构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, 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(0601006,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 i