cs_weblog_deletestalespamcomments.prc

community server 源码

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE   PROCEDURE [dbo].cs_weblog_DeleteStaleSpamComments
(
	@SettingsID int,
	@ExpirationDays int = 30
)
AS

SET Transaction Isolation Level Read UNCOMMITTED


DECLARE	@CommentsToDelete TABLE
		(PostID int)

-- temporarily store posts to delete
INSERT	@CommentsToDelete
		(PostID)
SELECT	P.PostID
FROM	cs_Posts P INNER JOIN cs_Sections S ON P.SectionID = S.SectionID
WHERE	S.ApplicationType = 1									-- only posts in blogs
	AND	P.ApplicationPostType IN (4, 8)							-- only comments/trackbacks
	AND	(P.PostStatus & 2) = 2									-- only posts marked as spam
	AND	P.PostDate < dateadd(d, -@ExpirationDays, getdate())	-- only posts that have been around for a while

-- archive deleted posts
INSERT	cs_posts_deleted_archive
		(PostID, UserID, SectionID, SettingsID, ApplicationType, Body, Subject, IPAddress, DeletedDate, ParentID, ThreadID, PostLevel, PostType)
SELECT	P.PostID, P.UserID, P.SectionID, P.SettingsID, 1, P.Body, P.Subject, P.IPAddress, getdate(), P.ParentID, P.ThreadID, P.PostLevel, P.PostType
FROM	cs_Posts P INNER JOIN @CommentsToDelete D ON P.PostID = D.PostID

-- actually delete posts
DELETE	P
FROM	cs_Posts P INNER JOIN @CommentsToDelete D ON P.PostID = D.PostID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


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