cs_system_deletepostandchildren.prc

解压即可使用

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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




CREATE procedure [dbo].cs_system_DeletePostAndChildren
(
    @PostID int,
    @RootPostID int = null,
    @DeleteChildren bit = 1,
    @SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
-- Posts are not "deleted", they are moved to SectionID=4.

DECLARE @ThreadID INT
DECLARE @OldThreadID INT
DECLARE @UserID INT
DECLARE @PostAuthor NVARCHAR(64)
DECLARE @SectionID INT
DECLARE @ParentID INT
DECLARE @IsApproved BIT
DECLARE @MostRecentPostAuthor NVARCHAR(64)
DECLARE @MostRecentPostAuthorID NVARCHAR(64)
DECLARE @MostRecentPostID INT
DECLARE @ForumPostStatisticsEnabled BIT
DECLARE @PostDate DATETIME
DECLARE @EmoticonID INT
DECLARE @IsLocked INT
DECLARE @MinPostLevel INT
DECLARE @MinSortOrder INT
DECLARE @DeletedSectionID INT

Select @DeletedSectionID = SectionID FROM cs_Sections where SettingsID = @SettingsID and ForumType = 50


-- First, get information about the post that is about to be deleted.
SELECT
	@OldThreadID = ThreadID,
	@UserID = UserID,
	@PostAuthor = PostAuthor,
	@ParentID = ParentID,
	@SectionID = SectionID,
	@IsLocked = IsLocked,
	@IsApproved = IsApproved,
	@PostDate = PostDate,
	@EmoticonID = EmoticonID
FROM
	cs_Posts
WHERE
	PostID = @PostID and SettingsID = @SettingsID


IF @IsApproved = 1 -- AND @RootPostID IS NULL
BEGIN
	-- We create a new thread here because we don't
	-- know if we are deleting a reply, a thread starter, or both.
	INSERT cs_Threads 	
		( SectionID,
		PostDate, 
		UserID, 
		PostAuthor, 
		ThreadDate, 
		MostRecentPostAuthor, 
		MostRecentPostAuthorID, 	
		MostRecentPostID, 
		IsLocked, 
		IsApproved,
		IsSticky, 
		StickyDate, 
		ThreadEmoticonID,
		SettingsID )
	VALUES
		( @DeletedSectionID, 	-- the Deleted Posts forum
		@PostDate, 
		@UserID, 
		@PostAuthor,
		@PostDate,
		@PostAuthor,
		@UserID, 
		@PostID,	-- MostRecentPostID, which we don't know until after post INSERT below.
		@IsLocked,
		@IsApproved,
		0,	-- Downgrade the thread to a non-sticky.
		@PostDate,
		@EmoticonID,
		@SettingsID )

	-- Get the new ThreadID
	SELECT 
		@ThreadID = @@IDENTITY
	FROM
		cs_Threads

	-- Move the post to the new thread
        UPDATE 
		cs_Posts 
	SET 
		SectionID = @DeletedSectionID,
		ThreadID = @ThreadID,
		ParentID = @PostID,
		SortOrder = 1,
		PostLevel = 1		-- set as the thread starter
	WHERE 
		PostID = @PostID and SettingsID = @SettingsID
	
	-- delete all child posts, unless DeleteChildred is set to 0.
	IF @DeleteChildren = 1 
	BEGIN
		UPDATE
			cs_Posts
		SET
			SectionID = @DeletedSectionID,
			ThreadID = @ThreadID,
			PostLevel = 2,
			SortOrder = 2
		WHERE
			ParentID = @PostID and SettingsID = @SettingsID

		-- EAD: quick fix because it was reset above
		-- (set all others to 2 for now)
		UPDATE
			cs_Posts
		SET
			PostLevel = 1,
			SortOrder = 1
		WHERE
			PostID = @PostID and SettingsID = @SettingsID
	END
	ELSE BEGIN
		-- Have to fix the non-deleted child posts, if any, for the ParentID.
		-- Setting them to the top-level post.
		UPDATE
			cs_Posts
		SET
			ParentID = (SELECT TOP 1 PostID FROM cs_Posts WHERE ThreadID = @OldThreadID and SettingsID = @SettingsID ORDER BY PostID ASC)
		WHERE
			ThreadID = @OldThreadID and SettingsID = @SettingsID
	END
    
	-- update the new thread's stats
	SELECT TOP 1
		@MostRecentPostAuthor = PostAuthor,
		@MostRecentPostAuthorID = UserID,
		@MostRecentPostID = PostID
	FROM
		cs_Posts
	WHERE
		ThreadID = @ThreadID and SettingsID = @SettingsID
	ORDER BY
		PostID DESC

	UPDATE
		cs_Threads
	SET
		MostRecentPostAuthor = @MostRecentPostAuthor, 
		MostRecentPostAuthorID = @MostRecentPostAuthorID, 	
		MostRecentPostID = @MostRecentPostID
	WHERE
		ThreadID = @ThreadID and SettingsID = @SettingsID		


	-- If no posts are linked to the OldthreadID, delete the old thread
	IF NOT EXISTS(SELECT ThreadID FROM cs_Posts WHERE ThreadID = @OldThreadID and SettingsID = @SettingsID)
	BEGIN
		-- Delete all thread tracking data.	
		DELETE FROM 
			cs_SearchBarrel
		WHERE 
			ThreadID = @OldThreadID and SettingsID = @SettingsID

		-- Delete all thread tracking data.	
		DELETE FROM 
			cs_TrackedThreads
		WHERE 
			ThreadID = @OldThreadID and SettingsID = @SettingsID

		-- Delete all thread read data.
		DELETE FROM 
			cs_ThreadsRead
		WHERE 
			ThreadID = @OldThreadID and SettingsID = @SettingsID

		-- Delete the thread
		DELETE
			cs_Threads
		WHERE
			ThreadID = @OldThreadID and SettingsID = @SettingsID
	END

	-- Decrease the TotalPosts on the user's profile.
	IF (SELECT EnablePostStatistics FROM cs_Sections WHERE SectionID = @SectionID and SettingsID = @SettingsID) = 1
		UPDATE 
			cs_UserProfile
		SET 
			TotalPosts = ISNULL(TotalPosts - (SELECT COUNT(PostID) FROM cs_Posts WHERE ThreadID = @ThreadID and SettingsID = @SettingsID), 0)
		WHERE 
			UserID = @UserID and SettingsID = @SettingsID
END

-- Delete from the search index
DELETE 
	cs_SearchBarrel 
WHERE 
	PostID = @PostID and SettingsID = @SettingsID


-- If the post is approved, reset the statistics on the forums and threads table.
IF @IsApproved = 1
BEGIN
	EXEC cs_system_ResetThreadStatistics @OldThreadID
	EXEC cs_system_ResetThreadStatistics @ThreadID
	EXEC cs_system_ResetForumStatistics @SectionID
	EXEC cs_system_ResetForumStatistics @DeletedSectionID
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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