ALL IN ONE : 利用存储过程实现BBS树形结构的存储及有回复email
来源：岁月联盟
时间：2003-07-11
/**********************************************************************/
/* */
/* 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
