T-SQL语句创建存储过程和触发器练习

源网页http://wenku.baidu.com/link?url=KsdJJz1oFTEwg-wwfYZESHbmCLl0e7cSUtScV3e6wv7py1XxhZK8-ZZ-YXLDdEf6TvkWCiXIVZlDIPZayaHnK8n7c552drzXZ5p_-Fq_ACq

实验过程： 
一、在student数据库上练习创建并调用课堂讲授的存储过程和触发器。 
1.创建一个instead of触发器，要求实现一下功能：在t_student表上创建一个删除类型的触发器notallowdelete,当上除记录时，显示不允许删除的提示信息 use students go 
if exists(select name from sysobjects 
          where name=‘notallowdelete‘ and type=‘tr‘)    drop trigger notallowdelete go 
CREATE trigger notallowdelete

if(@@error<>0)---------- @@代表系统变量 set @errorvalue=@@error return @errorvalue end 

执行该存储过程: 
declare @nl as int, @num as char(10), @returnvalue as int set @num=‘S99002‘ 
exec stu_age @num,@nl output 
print ‘学号为‘+rtrim(cast(@num as char(10)))+‘的学生的年龄是‘+cast(@nl as 
char(2))+‘岁‘ 执行结果： 
 
3.创建一个名为stu_info的存储过程，要求:输入学号，查询学生所在的班级、学
生姓名，课程名和选课成绩。 
CREATE proc stu_info  @xh as char(10) as begin 
select substring(s_number,4,1) as ‘班级‘,s_name as ‘姓名‘,c_name as ‘课程名‘,score as ‘
成绩‘ 
from t_student inner join t_score 
on s_number=s_num inner join course on c_number=c_num where @xh=s_number end GO 
执行该存储过程： exec stu_info‘S99001‘ 
 
4.求一个数的阶乘（没有返回值） CREATE proc jiecheng @i as int as  
declare @result as int declare @ii as int set @result=1 set @ii=@i while @i>1 begin 
set @result=@result*@i 

set @i=@i-1  if @i>1 continue else  begin  print @ii 
 print rtrim(cast(@ii as char(2)))+‘的阶乘为：‘------该输出必须使用转换数据类型
的函数cast,否则就会出现如下错误： 
 print @result   end end GO 
执行该存储过程： declare @data as int set @data=5 
exec jiecheng @data 执行结果： 5 
5的阶乘为： 120 
5.求一个数的阶乘，一个输入，一个输出。(带有输出参数的) CREATE proc jiecheng 
 @i as int,@result as int output as  
declare @ii as int set @result=1 set @ii=@i while @i>0 begin 
set @result=@result*@i set @i=@i-1 if @i>1  continue else break end GO 
执行该存储过程: 
declare @data as int,@sum as int set @data=5 
exec jiecheng @data,@sum output 
print rtrim(cast(@data as char(2)))+‘的阶乘为:‘ print @sum 

执行结果为： 5的阶乘为: 120 
(6)带有默认值的存储过程。 
输入学号，查询学生所在的班级、学生姓名，课程名和选课成绩。（stu_info1） CREATE proc stu_info1 @num as char(10)=‘S99001‘ as 
select substring(s_number,4,1) as ‘班级‘,        s_name as ‘姓名‘,        c_name as ‘课程名‘,        score as ‘成绩‘ 
from t_student inner join t_score 
on s_number=s_num inner join course on c_number=c_num where @num=s_number GO 
执行存储过程: exec stu_info1 执行结果： 
 
当不输入指定的学号时，数据库自动给@num赋值为：S99001   
三、在BBS数据库上设计所需触发器 
? 发表主帖，用户积分加10分，版块发帖量加1。 
CREATE trigger publish  on BBSTopic for insert as 
begin 
declare @yhID as int 
declare @bkID as int 
select @yhID=TUID , @bkID=TSID from inserted update BBSUser  set UPoint=UPoint+10 where @yhID=UID update BBSSession 
set STopicCount=STopicCount+1

where @bkID=SID end  
执行插入语句后： 
Insert 
IntoBBSTopic(TID,TSID,TUID,TTopic,TContents,TClickCount,TFlag,TLastClickT,TReplyCount) 
values(‘6‘,‘2‘,‘1‘,‘买卖商场 ‘) 
执行该操作后，BBSUser表中的UID=1的用户‘火云邪神’的积分UPoint加10；BBSSseeion表中的SClickCount加1。           
跟帖，用户积分加1，主帖的回复数量加1、更新最后回复
时间，版块发帖量加1。  
create trigger gt on BBSReply 
for insert,update as begin 
declare @yhID as int,@ztID as int,@bkID as int 
select @yhID=RUID,@ztID=RTID,@bkID=RSID from inserted update BBSUser set UPoint=UPoint+1 where @yhID=UID update BBSTopic 
set TReplyCount=TReplyCount+1 where @ztID=TID 
update BBSSession 
set STopicCount=STopicCount+1 where @bkID=SID end 
执行插入语句： insert into 
BBSReply(RID,RTID,RSID,RUID,RTopic,RContents) values(‘6‘,‘2‘,‘1‘,‘2‘,‘就业难‘,‘机会不平等‘) 
在BBSUser表中2号用户的UPoint就会加1;在BBSTopic表中2号主帖的回复量（TReplyCount）就会变为7；在BBSSession表中版块发帖量（STopicCount）就会变为1601. 
 

? 删除跟帖，用户积分减20分，版块发帖量减1。 
create trigger scgt on BBSReply for delete,update as begin 
declare @yhID as int,@bkID as int 
select @yhID=RUID ,@bkID=RSID from deleted update BBSUser set UPoint=UPoint-20 where @yhID=UID 
update BBSSession 
set STopicCount=STopicCount-1 where @bkID=SID end 
删除主帖，用户积分减50分，版块发帖量减1，删除所有跟帖。  
create  trigger sczt on BBSTopic for delete,update as begin 
declare @yhID as int declare @bkID as int 
declare @gtID as int 
select @yhID=TUID,@bkID=TSID from deleted update BBSUser set UPoint=UPoint-50 where @yhID=UID 
update BBSSession 
set STopicCount=STopicCount-1 delete 
from BBSReply 
where RTID=(select TID from deleted )