《数据库原理及应用》实验报告实验过程：一、在student数据库上练习创建并调用课堂讲授的存储过程和触发器。
1.创建一个instead of触发器，要求实现一下功能：在t_student表上创建一个删除类型的触发器notallowdelete,当上除记录时，显示不允许删除的提示信息use studentsgoif exists(select name from sysobjectswhere name='notallowdelete' and type='tr')drop trigger notallowdeletegoCREATE trigger notallowdeleteon t_studentinstead of deleteasprint'notallowdelete触发器开始执行……'print'不能执行删除操作！'2.创建一个after触发器，要求实现一下功能：在t_student表上创建一个删除类型的触发器studelete,当在t_studen t表中删除某一条记录后，在t_score表中删除与此学号对应的记录。
use studentsgoif exists(select name from sysobjectswhere name='studelete' and type='tr')drop trigger studeletegoCREATE trigger studeleteon t_studentfor deleteasprint'notallowdelete触发器开始执行……'declare @stunum char(10)print'把在t_student中删除记录的学号赋值给@stunum'selete @stunum=s_numberfrom deletedprint'开始查找并删除t_score中的相关记录……'delete from t_scorewhere s_number=@stunumprint'删除了t_score中学号为'+rtrim(@stunum)+'的记录'3.使用T_SQL语句创建一个insert触发器，功能是：当在t_score表中插入或修改s_number时，检测t_student中是否存在相应值，不存在给出信息，否则操作成功。
create trigger ins_scoon t_scorefor insert,updateasif update(s_number)begindeclare @s_num char(10)select @s_num=(select s_number from inserted)if @s_num in (select s_number from t_student)print'操作成功！'elsebeginprint'学生表中没有相关纪录！'rollback transactionend4.在student表上创建一个insert触发器，功能是：当在student表中插入数据时，显示“你插入了一条新记录！”create trigger stuinsert on t_studentfor insertasdeclare @msg char(30)set @msg=' 你插入了一条新记录！'print @msg二、创建一个AFTER触发器，要求实现一下功能：在t_score 表上创建一个插入、更新类型的触发器scorecheck,CREA TE trigger scorecheckon t_scorefor insert,updateasif update(score)print 'scorecheck触发器开始执行……'begindeclare @scorevalue realselect @scorevalue=(select score from inserted)if @scorevalue>100 or @scorevalue<0beginprint '输入有误，请确认输入的考试分数！'raiserror('1432423',16,1)rollback transactionendelseprint'操作成功！'end当在score字段中插入或修改考试分数后，检查分数是否在0到100之间。
use studentgoif exists(select name from sysobjectswhere name='scorecheck' and type='tr')drop trigger scorecheckgoCREA TE trigger scorecheckon t_scorefor insert,updateasif update(score)print 'scorecheck触发器开始执行……'begindeclare @scorevalue realselect @scorevalue=(select score from inserted)if @scorevalue>100 or @scorevalue<0print '输入有误，请确认输入的考试分数！'elseprint'操作成功！'end存储过程练习1.创建一个存储过程：要求在t_student，course，t_score表上查询成绩。
create proc StuScoreInfoasselect substring(s_number,4,1) as '班级',s_number as '学号',s_name as '姓名',sex as '性别',c_name as '课程名',score as '成绩'from t_student inner join t_scoreon s_number=s_num inner join courseon c_number=c_num执行该存储过程:exec StuScoreInfo2.创建一个带有参数stu_age的存储过程,该存储过程根据输入的学生号,t_student 中计算此学生的年龄,并根据结果返回不同的值,程序执行成功,返回整数0,出错则返回错误号.(没有实现状态返回值)create proc stu_age@xh as char(10),@age as intasbegindeclare @errorvalue as intset @errorvalue=0select @age=year(getdate())-year(birthday)from t_studentwhere @xh=s_numberif(@@error<>0)----------@@代表系统变量set @errorvalue=@@errorreturn @errorvalueend执行该存储过程:declare @nl as int, @num as char(10), @returnvalue as intset @num='S99002'exec stu_age @num,@nl outputprint '学号为'+rtrim(cast(@num as char(10)))+'的学生的年龄是'+cast(@nl as char(2))+'岁'执行结果：3.创建一个名为stu_info的存储过程，要求:输入学号，查询学生所在的班级、学生姓名，课程名和选课成绩。
CREATE proc stu_info@xh as char(10)asbeginselect substring(s_number,4,1) as '班级',s_name as '姓名',c_name as '课程名',score as '成绩'from t_student inner join t_scoreon s_number=s_num inner join courseon c_number=c_numwhere @xh=s_numberendGO执行该存储过程：exec stu_info'S99001'4.求一个数的阶乘（没有返回值）CREATE proc jiecheng@i as intasdeclare @result as intdeclare @ii as intset @result=1set @ii=@iwhile @i>1beginset @result=@result*@iset @i=@i-1if @i>1continueelsebeginprint @iiprint rtrim(cast(@ii as char(2)))+'的阶乘为：'------该输出必须使用转换数据类型的函数cast,否则就会出现如下错误：print @resultendendGO执行该存储过程：declare @data as intset @data=5exec jiecheng @data执行结果：55的阶乘为：1205.求一个数的阶乘，一个输入，一个输出。
(带有输出参数的)CREATE proc jiecheng@i as int,@result as int outputasdeclare @ii as intset @result=1set @ii=@iwhile @i>0beginset @result=@result*@iset @i=@i-1if @i>1continueelsebreakendGO执行该存储过程:declare @data as int,@sum as intset @data=5exec jiecheng @data,@sum outputprint rtrim(cast(@data as char(2)))+'的阶乘为:'print @sum执行结果为：5的阶乘为:120(6)带有默认值的存储过程。
输入学号，查询学生所在的班级、学生姓名，课程名和选课成绩。
（stu_info1）CREATE proc stu_info1@num as char(10)='S99001'asselect substring(s_number,4,1) as '班级',s_name as '姓名',c_name as '课程名',score as '成绩'from t_student inner join t_scoreon s_number=s_num inner join courseon c_number=c_numwhere @num=s_numberGO执行存储过程:exec stu_info1执行结果：当不输入指定的学号时，数据库自动给@num赋值为：S99001三、在BBS数据库上设计所需触发器发表主帖，用户积分加10分，版块发帖量加1。