cs_user_delete.prc

community server 源码

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE procedure [dbo].[cs_User_Delete]
(
	@UserID					int,
	@ReassignUserName		nvarchar(256)
)
AS
	SET Transaction Isolation Level Read UNCOMMITTED

	-- locals
	DECLARE	@ApplicationGuid	uniqueidentifier
	DECLARE	@UserGuid			uniqueidentifier
	DECLARE	@ReassignUserID		int


	-- lookup application, user
	SELECT	@ApplicationGuid = ASP.ApplicationId, @UserGuid = ASP.UserId
	FROM	aspnet_Users ASP INNER JOIN cs_Users CS ON ASP.UserId = CS.MembershipID
	WHERE	CS.UserID = @UserID


	-- lookup reassign user
	SELECT	@ReassignUserID = CS.UserID
	FROM	aspnet_Users ASP INNER JOIN cs_Users CS ON ASP.UserId = CS.MembershipID
	WHERE	LoweredUserName = LOWER(@ReassignUserName)
		AND	ApplicationId = @ApplicationGuid


	-- does the reassign user exist?
	IF (@ReassignUserID IS NULL)
	BEGIN
		SELECT 2 -- InvalidReassignUserName
		RETURN
	END


	-- reassign user appears valid
	BEGIN TRANSACTION


	-- be sure posts are set to be reindexed BEFORE they are reassigned
	EXECUTE cs_Posts_ReindexByUser @UserID
	IF (@@ERROR <> 0) GOTO Failure


	-- reassign content
	UPDATE	cs_InkData
	SET		UserID = @ReassignUserID
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	UPDATE	cs_ModerationAudit
	SET		UserID = @ReassignUserID
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	UPDATE	cs_PostAttachments
	SET		UserID = @ReassignUserID
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	UPDATE	cs_PostAttachments_TEMP
	SET		UserID = @ReassignUserID
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	UPDATE	cs_Posts
	SET		UserID = @ReassignUserID,
			PostAuthor = CONVERT(nvarchar(64), @ReassignUserName)
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	UPDATE	cs_Posts_Deleted_Archive
	SET		UserID = @ReassignUserID
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	UPDATE	cs_PostsArchive
	SET		UserName = CONVERT(nvarchar(64), @ReassignUserName)
	FROM	cs_PostsArchive PA INNER JOIN aspnet_Users ASP ON PA.UserName = ASP.UserName INNER JOIN cs_Users CS ON ASP.UserId = CS.MembershipID
	WHERE	CS.UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	UPDATE	cs_PrivateMessages
	SET		UserID = @ReassignUserID
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	UPDATE	cs_Sections
	SET		MostRecentPostAuthorID = @ReassignUserID,
			MostRecentPostAuthor = CONVERT(nvarchar(64), @ReassignUserName)
	WHERE	MostRecentPostAuthorID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	UPDATE	cs_Threads
	SET		UserID = @ReassignUserID,
			PostAuthor = CONVERT(nvarchar(64), @ReassignUserName)
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	UPDATE	cs_Threads
	SET		MostRecentPostAuthorID = @ReassignUserID,
			MostRecentPostAuthor = CONVERT(nvarchar(64), @ReassignUserName)
	WHERE	MostRecentPostAuthorID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	UPDATE	cs_weblog_Weblogs
	SET		MostRecentArticleAuthor = CONVERT(nvarchar(64), @ReassignUserName)
	WHERE	MostRecentArticleAuthorID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	UPDATE	cs_weblog_Weblogs
	SET		MostRecentPostAuthor = CONVERT(nvarchar(64), @ReassignUserName)
	WHERE	MostRecentPostAuthorID = @UserID
	IF (@@ERROR <> 0) GOTO Failure


	-- delete content
	DELETE
	FROM	cs_FavoritePosts
	WHERE	OwnerID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_FavoriteSections
	WHERE	OwnerID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_FavoriteUsers
	WHERE	OwnerID = @UserID OR UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_FolderFeed
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_Folder
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_ModerationAudit
	WHERE	ModeratorID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_PostRating
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_SectionsRead
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_Statistics_User
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_ThreadRating
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_ThreadsRead
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_TrackedSections
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_TrackedThreads
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_UserAvatar
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_UserInvitation
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_UserReadPost
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_Votes
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	csm_EmailIds
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	files_Downloads
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure


	-- delete user
	DELETE
	FROM	cs_Moderators
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_UserProfile
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	cs_Users
	WHERE	UserID = @UserID
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	aspnet_UsersInRoles
	WHERE	UserID = @UserGuid
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	aspnet_Profile
	WHERE	UserID = @UserGuid
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	aspnet_Membership
	WHERE	UserID = @UserGuid
	IF (@@ERROR <> 0) GOTO Failure

	DELETE
	FROM	aspnet_Users
	WHERE	UserID = @UserGuid
	IF (@@ERROR <> 0) GOTO Failure


	-- no problems occurred
	COMMIT TRANSACTION
	SELECT 1 -- Success
	RETURN


Failure:
	-- something went wrong
	ROLLBACK TRANSACTION
	SELECT 0 -- UnknownFailure
	RETURN


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].[cs_User_Delete] TO PUBLIC
GO