cs_system_updatethread.prc

解压即可使用

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_system_UpdateThread]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_system_UpdateThread]
GO







CREATE   procedure [dbo].cs_system_UpdateThread (
	@ThreadID int,
	@ReplyPostID int,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN
SET NOCOUNT ON
DECLARE @ThreadDate datetime
DECLARE @StickyDate datetime
DECLARE @UserID int
DECLARE @PostAuthor nvarchar(64)
DECLARE @FirstPostID INT
DECLARE @PostConfiguration int

-- Set default
SET @PostConfiguration = 0

IF @ReplyPostID = 0
	SELECT TOP 1 
		@ReplyPostID = PostID 
	FROM 
		cs_Posts
	WHERE
		ThreadID = @ThreadID
		AND IsApproved = 1 and SettingsID = @SettingsID
	ORDER BY
		PostDate DESC

-- Get details about the reply & PostConfiguration details
SELECT 
	@ThreadDate = PostDate, 
	@UserID = UserID, 
	@PostAuthor = PostAuthor,
	@PostConfiguration = PostConfiguration
FROM 
	cs_Posts 
WHERE 
	PostID = @ReplyPostID and SettingsID = @SettingsID

-- Is anonymous post?
IF (@PostConfiguration & 1) = 1
BEGIN
	-- Set anonymous info instead real user info
	SELECT @UserID = cs_UserID FROM cs_vw_Users_FullUser WHERE SettingsID = @SettingsID and IsAnonymous = 1
	SET @PostAuthor = ''
END

SELECT 
	@StickyDate = StickyDate 
FROM 
	cs_Threads 
WHERE 
	ThreadID = @ThreadID and SettingsID = @SettingsID

IF @StickyDate < @ThreadDate
	SET @StickyDate = @ThreadDate

-- do the mass updates.
UPDATE 
	cs_Threads
SET
	TotalReplies = (SELECT Count(PostID) FROM cs_Posts WHERE ThreadID = @ThreadID AND IsApproved = 1 AND PostLevel > 1),
	ThreadDate = @ThreadDate,
	StickyDate = @StickyDate,
	MostRecentPostAuthorID = @UserID,
	MostRecentPostAuthor = @PostAuthor,
	MostRecentPostID = @ReplyPostID
WHERE
	ThreadID = @ThreadID and SettingsID = @SettingsID



-- find any lingering ParentIDs that don't match any posts in
-- our thread (from a merge or split action)
SET @FirstPostID = (	SELECT TOP 1 
				PostID 
			FROM 
				cs_Posts
			WHERE
				ThreadID = @ThreadID
				AND IsApproved = 1 and SettingsID = @SettingsID
			ORDER BY
				PostDate ASC )

UPDATE
	cs_Posts
SET
	ParentID = @FirstPostID
WHERE
	ParentID NOT IN (SELECT PostID FROM cs_Posts WHERE ThreadID = @ThreadID and SettingsID = @SettingsID)
	AND ThreadID = @ThreadID and SettingsID = @SettingsID




-- fix the PostLevel and SortOrder ordering, by date
-- this could be done better, as it's on a MassScale now.
UPDATE
	cs_Posts
SET
	PostLevel = 1,
	SortOrder = 1
WHERE
	ThreadID = @ThreadID
	AND PostID = (SELECT TOP 1 PostID FROM cs_Posts WHERE ThreadID = @ThreadID and SettingsID = @SettingsID ORDER BY PostID ASC)

UPDATE
	cs_Posts
SET
	PostLevel = 2,
	SortOrder = SortOrder + 1
WHERE
	ThreadID = @ThreadID
	AND PostID > @ReplyPostID and SettingsID = @SettingsID

-- update the EmoticonID, if it's the first post
IF @ReplyPostID = (SELECT TOP 1 PostID FROM cs_Posts WHERE ThreadID = @ThreadID and SettingsID = @SettingsID ORDER BY PostDate ASC)
	UPDATE
		cs_Threads
	SET
		ThreadEmoticonID = (SELECT EmoticonID FROM cs_Posts WHERE PostID = @ReplyPostID and SettingsID = @SettingsID)
	WHERE
		ThreadID = @ThreadID	 and SettingsID = @SettingsID


SET NOCOUNT OFF
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_system_UpdateThread] to public
go