ALTER PROCEDURE [dbo].cs_guestbook_Post_Create
(
@SectionID int,
@Subject nvarchar(256),
@UserID int,
@PostAuthor nvarchar(64) = null,
@Body ntext,
@FormattedBody ntext,
@EmoticonID int = 0,
@PostType int = 0,
@PostDate datetime = null,
@UserHostAddress nvarchar(32),
@PropertyNames ntext = null,
@PropertyValues ntext = null,
@SettingsID int,
@PostID int out
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
/*设置事务隔离级别,Read UNCOMMITTED 执行脏读或 0 级隔离锁定,这表示不发出共享锁,
也不接受排它锁。当设置该选项时,可以对数据执行未提交读或脏读;在事务结束前可以更
改数据内的数值,行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有
语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。*/
BEGIN
SET NOCOUNT ON
/*当 SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。
当 SET NOCOUNT 为 OFF 时,返回计数。即使当 SET NOCOUNT 为 ON 时,也更新
@@ROWCOUNT 函数。*/
DECLARE @ThreadID int
-- set the PostDate
-- 设置回帖日期
IF @PostDate IS NULL
SET @PostDate = GetDate()
-- set the username
-- 设置用户名即作者
IF @PostAuthor IS NULL
SELECT
@PostAuthor = UserName
FROM
cs_vw_Users_FullUser
WHERE
cs_UserID = @UserID
-- Do we care about duplicates?
-- 我们是否考虑完全相同的
Select @ThreadID = ThreadID FROM cs_Threads Where SectionID = @SectionID
SET NOCOUNT ON
/*当 SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。
当 SET NOCOUNT 为 OFF 时,返回计数。即使当 SET NOCOUNT 为 ON 时,也更新
@@ROWCOUNT 函数。*/
BEGIN TRAN -- 开始事务
IF @ThreadID is null
BEGIN
INSERT cs_Threads -- 插入数据
(
SectionID,
PostDate,
UserID,
PostAuthor,
ThreadDate,
MostRecentPostAuthor,
MostRecentPostAuthorID,
MostRecentPostID,
IsLocked,
IsApproved,
IsSticky,
StickyDate,
ThreadEmoticonID,
SettingsID
)
VALUES
(
@SectionID,
@PostDate,
@UserID,
@PostAuthor,
@PostDate,
@PostAuthor,
@UserID,
0, -- MostRecentPostID, which we don't know until after post INSERT below.
0,
1,
0,
@PostDate,
-1,
@SettingsID
)
-- Get the new ThreadID
-- 获取主题ID
SELECT
@ThreadID = @@IDENTITY
FROM
cs_Threads
-- Now we add the new post
-- 现在插入新的帖子
/*在主题表中插入数据的时候也要在帖子表中插入数据,因为主题只是帖子的信息统计
具体的内容还是帖子里的.*/
INSERT cs_Posts
( SectionID,
ThreadID,
ParentID,
PostLevel,
SortOrder,
Subject,
UserID,
PostAuthor,
IsApproved,
IsLocked,
Body,
FormattedBody,
PostType,
PostDate,
IPAddress,
EmoticonID,
PropertyNames,
PropertyValues,
SettingsID
)
VALUES
( @SectionID,
@ThreadID,
0, -- ParentID, which we don't know until after INSERT
1, -- PostLevel, 1 marks start/top/first post in thread.
1, -- SortOrder (not in use at this time)
@Subject,
@UserID,
@PostAuthor,
1,
0,
@Body,
@FormattedBody,
@PostType,
@PostDate,
@UserHostAddress,
-1,
@PropertyNames,
@PropertyValues,
@SettingsID )
-- Get the new PostID
-- 获取帖子ID
SELECT
@PostID = @@IDENTITY
-- Update the new Thread with the new PostID
-- 更新主题表
UPDATE
cs_Threads
SET
MostRecentPostID = @PostID
WHERE
ThreadID = @ThreadID
END
ELSE BEGIN -- @ParentID <> 0 means there is a reply to an existing post
-- 如果@ParentID不为0则表示有回复
-- Insert the new post
INSERT cs_Posts
( SectionID,
ThreadID,
ParentID,
PostLevel,
SortOrder,
Subject,
UserID,
PostAuthor,
IsApproved,
IsLocked,
Body,
FormattedBody,
PostType,
PostDate,
IPAddress,
EmoticonID,
PropertyNames,
PropertyValues,
SettingsID )
VALUES
( @SectionID,
@ThreadID,
0,
1,
0,
@Subject,
@UserID,
@PostAuthor,
1,
0,
@Body,
@FormattedBody,
@PostType,
@PostDate,
@UserHostAddress,
-1,
@PropertyNames,
@PropertyValues,
@SettingsID )
-- Grab the new PostID and update the ThreadID's info
-- 获取新的回帖ID并更新主题表
SELECT
@PostID = @@IDENTITY
UPDATE
cs_Threads
SET
TotalReplies = (SELECT COUNT(*) FROM cs_Posts WHERE ThreadID = @ThreadID)
WHERE
ThreadID = @ThreadID
END
END
COMMIT TRAN
SET NOCOUNT OFF
SELECT @PostID = @PostID
(
@SectionID int,
@Subject nvarchar(256),
@UserID int,
@PostAuthor nvarchar(64) = null,
@Body ntext,
@FormattedBody ntext,
@EmoticonID int = 0,
@PostType int = 0,
@PostDate datetime = null,
@UserHostAddress nvarchar(32),
@PropertyNames ntext = null,
@PropertyValues ntext = null,
@SettingsID int,
@PostID int out
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
/*设置事务隔离级别,Read UNCOMMITTED 执行脏读或 0 级隔离锁定,这表示不发出共享锁,
也不接受排它锁。当设置该选项时,可以对数据执行未提交读或脏读;在事务结束前可以更
改数据内的数值,行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有
语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。*/
BEGIN
SET NOCOUNT ON
/*当 SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。
当 SET NOCOUNT 为 OFF 时,返回计数。即使当 SET NOCOUNT 为 ON 时,也更新
@@ROWCOUNT 函数。*/
DECLARE @ThreadID int
-- set the PostDate
-- 设置回帖日期
IF @PostDate IS NULL
SET @PostDate = GetDate()
-- set the username
-- 设置用户名即作者
IF @PostAuthor IS NULL
SELECT
@PostAuthor = UserName
FROM
cs_vw_Users_FullUser
WHERE
cs_UserID = @UserID
-- Do we care about duplicates?
-- 我们是否考虑完全相同的
Select @ThreadID = ThreadID FROM cs_Threads Where SectionID = @SectionID
SET NOCOUNT ON
/*当 SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。
当 SET NOCOUNT 为 OFF 时,返回计数。即使当 SET NOCOUNT 为 ON 时,也更新
@@ROWCOUNT 函数。*/
BEGIN TRAN -- 开始事务
IF @ThreadID is null
BEGIN
INSERT cs_Threads -- 插入数据
(
SectionID,
PostDate,
UserID,
PostAuthor,
ThreadDate,
MostRecentPostAuthor,
MostRecentPostAuthorID,
MostRecentPostID,
IsLocked,
IsApproved,
IsSticky,
StickyDate,
ThreadEmoticonID,
SettingsID
)
VALUES
(
@SectionID,
@PostDate,
@UserID,
@PostAuthor,
@PostDate,
@PostAuthor,
@UserID,
0, -- MostRecentPostID, which we don't know until after post INSERT below.
0,
1,
0,
@PostDate,
-1,
@SettingsID
)
-- Get the new ThreadID
-- 获取主题ID
SELECT
@ThreadID = @@IDENTITY
FROM
cs_Threads
-- Now we add the new post
-- 现在插入新的帖子
/*在主题表中插入数据的时候也要在帖子表中插入数据,因为主题只是帖子的信息统计
具体的内容还是帖子里的.*/
INSERT cs_Posts
( SectionID,
ThreadID,
ParentID,
PostLevel,
SortOrder,
Subject,
UserID,
PostAuthor,
IsApproved,
IsLocked,
Body,
FormattedBody,
PostType,
PostDate,
IPAddress,
EmoticonID,
PropertyNames,
PropertyValues,
SettingsID
)
VALUES
( @SectionID,
@ThreadID,
0, -- ParentID, which we don't know until after INSERT
1, -- PostLevel, 1 marks start/top/first post in thread.
1, -- SortOrder (not in use at this time)
@Subject,
@UserID,
@PostAuthor,
1,
0,
@Body,
@FormattedBody,
@PostType,
@PostDate,
@UserHostAddress,
-1,
@PropertyNames,
@PropertyValues,
@SettingsID )
-- Get the new PostID
-- 获取帖子ID
SELECT
@PostID = @@IDENTITY
-- Update the new Thread with the new PostID
-- 更新主题表
UPDATE
cs_Threads
SET
MostRecentPostID = @PostID
WHERE
ThreadID = @ThreadID
END
ELSE BEGIN -- @ParentID <> 0 means there is a reply to an existing post
-- 如果@ParentID不为0则表示有回复
-- Insert the new post
INSERT cs_Posts
( SectionID,
ThreadID,
ParentID,
PostLevel,
SortOrder,
Subject,
UserID,
PostAuthor,
IsApproved,
IsLocked,
Body,
FormattedBody,
PostType,
PostDate,
IPAddress,
EmoticonID,
PropertyNames,
PropertyValues,
SettingsID )
VALUES
( @SectionID,
@ThreadID,
0,
1,
0,
@Subject,
@UserID,
@PostAuthor,
1,
0,
@Body,
@FormattedBody,
@PostType,
@PostDate,
@UserHostAddress,
-1,
@PropertyNames,
@PropertyValues,
@SettingsID )
-- Grab the new PostID and update the ThreadID's info
-- 获取新的回帖ID并更新主题表
SELECT
@PostID = @@IDENTITY
UPDATE
cs_Threads
SET
TotalReplies = (SELECT COUNT(*) FROM cs_Posts WHERE ThreadID = @ThreadID)
WHERE
ThreadID = @ThreadID
END
END
COMMIT TRAN
SET NOCOUNT OFF
SELECT @PostID = @PostID
cs_guestbook_GetPosts