IDC资讯中心
下面是上篇文章所说的存储过程，其作用已经说过，在这里就不再赘述了。请大家自己看代码吧。这个存储过程只是存储数据的过程，以后如果有时间我将讲一下读取数据。
/**********************************************************************/
/* */
/* stored procudure : up_posttopic */
/* */
/* description: 贴子存储及回复email */
/* */
/* author: bigeagle */
/* */
/* date: 2000/7/25 凌晨 */
/* */
/* history: version 1.0 by bigeagle , 2000/7/25 */
/* */
/**********************************************************************/
if exists (select * from sysobjects where id = object_id("up_posttopic"))
drop proc up_posttopic
go
create proc up_posttopic @a_intid int output ,
@a_intfatherid int , @a_intforumid int , @a_intuserid int ,
@a_strtitle varchar(255) , @a_strcontent text , @a_intfaceid tinyint ,
@a_bifemail bit , @a_bifsignature bit
as
declare @m_inttopicid int
declare @m_intlayer tinyint
declare @m_introotid int
declare @m_fordernum float
select @m_fordernum = power(2 , 30) –初始化排序基数
/*首先判断是否有这个论坛，没有则退出*/
if not exists (select * from bbscategory where categoryid = @a_intforumid)
begin
select @a_intid = 0
return(0)
end
/*判断是新发贴子还是回应主题*/
if @a_intfatherid = 0 –没有父贴子，说明是新发贴子
select @m_intlayer = 1 , @m_introotid = 0
else
begin
if not exists(select * from bbs where id = @a_intfatherid) –如果没发现父贴子
begin
select topicid = 0
return (0)
end
else –如果发现父贴子，则取出层数和根id
select @m_intlayer = layer + 1 ,@m_introotid = rootid ,@m_fordernum = ordernum
from bbs where id = @a_intfatherid
end
/*更新表，因为要对多个表操作，所以放到事务里*/
begin transaction
/*插入表bbs*/
insert into bbs (fatherid , layer , forumid , userid , title ,
content , posttime , faceid , hits , selected ,
closed , ifemail , ifsignature , ordernum)
values(@a_intfatherid , @m_intlayer , @a_intforumid , @a_intuserid , @a_strtitle ,
@a_strcontent , getdate() , @a_intfaceid , 0 , 0 ,
0 , @a_bifemail , @a_bifsignature , default)
if (@@error <> 0) goto on_error –如果出错转向错误处理部分
select @m_inttopicid = @@identity –取出刚刚插入纪录的id
/*如果是新发贴子则取id为rootid*/
if @m_introotid = 0 –新发贴子
begin
select @m_introotid = @m_inttopicid
end
else –不是新发贴子则更新根纪录的totalcounts
begin
update bbs set totalchilds = totalchilds + 1 –更新根的子贴数
where id = @m_introotid
if (@@error <> 0) goto on_error –如果更新失败则转向错误处理部分
end
select @m_fordernum = @m_fordernum + power(2,30)/power(2,totalchilds)
from bbs where id = @m_introotid
select @m_fordernum
/*更新rootid , ordernum*/
update bbs set ordernum = @m_fordernum , rootid = @m_introotid
where id = @m_inttopicid
if (@@error <> 0) goto on_error –如果更新失败则转向错误处理部分
/*更新bbscategory表*/
update bbscategory set topiccounts = topiccounts + 1 , lastreplytime = getdate()
where categoryid = @a_intforumid
if (@@error <>0) goto on_error –如果更新失败则转向错误处理部分
/*更新bbsuser表，将用户分数加一*/
update bbsuser set point = point + 1
where id = @a_intuserid
/*如果全部成功则完成事务*/
commit transaction
/*如果要求回复则发邮件*/
declare @m_stremail varchar(100) , @m_bifemail bit
declare @m_strname varchar(20) , @m_strsubject varchar(50)
declare @m_strmessage varchar(255)
select @m_bifemail = a.ifemail , @m_stremail = isnull(b.email , ""),
@m_strname = b.username
from bbs as a
left join bbsuser as b on a.userid = b.id
where a.id = @a_intfatherid
select @m_strsubject = "来自ematter board : 您有回复"
select @m_strmessage = "您发表在ematter board的贴子现在有人回复："
+ " http://server1/bbs/showtopic.asp?id="
+ convert(varchar,@a_intfatherid)
if @m_stremail <> "" and @m_bifemail = 1
exec master..xp_sendmail @recipients = @m_stremail , @subject = @m_strsubject,
@message = @m_strmessage
select @a_intid = @m_inttopicid –返回贴子id
return (0)
on_error: –错误处理部分
rollback transaction
select @a_intid = 0 –贴子id返回0，代表失败
return (-1)
go
