ALTER PROCEDURE dbo.aspnet_Roles_DeleteRole
@ApplicationName NVARCHAR(256),
@RoleName NVARCHAR(256),
@DeleteOnlyIfRoleIsEmpty BIT --只有当角色为空时删除(即无用户使用该角色,下面有用)
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN(1)
/*查询@ApplicationId(他好象都是通过这个和另外一个调用存储过程的方法来返回@ApplicationId的
两种方法在我看来好象是一样的样子,不过存储过程显然验证的东西多点.)*/
DECLARE @ErrorCode INT
SET @ErrorCode = 0
DECLARE @TranStarted BIT
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 ) ---如果当前活动事务为0,开始事务并设置事务参数为1
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
DECLARE @RoleId UNIQUEIDENTIFIER
SELECT @RoleId = NULL
SELECT @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
--声明角色ID并查询此角色的角色ID
IF (@RoleId IS NULL) ---如果不存在
BEGIN
SELECT @ErrorCode = 1 --返回错误
GOTO Cleanup
END
IF (@DeleteOnlyIfRoleIsEmpty <> 0) --如果@DeleteOnlyIfRoleIsEmpty不为0
BEGIN
IF (EXISTS (SELECT RoleId FROM dbo.aspnet_UsersInRoles WHERE @RoleId = RoleId))
/*如果存在有用户在使用此角色,就是这意思吧.*/
BEGIN
SELECT @ErrorCode = 2 --返回错误代码2
GOTO Cleanup
END
END
DELETE FROM dbo.aspnet_UsersInRoles WHERE @RoleId = RoleId
/*从aspnet_UsersInRoles表中删除此角色的所有记录*/
IF( @@ERROR <> 0 ) --如果有错误,返回错误代码并跳转到回滚
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
DELETE FROM dbo.aspnet_Roles WHERE @RoleId = RoleId AND ApplicationId = @ApplicationId
/*从aspnet_Roles中删除此角色*/
IF( @@ERROR <> 0 ) ---如果有错误则回滚
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN(0)
/*因为角色在两个表中存在,所以删除某角色的时候必须删除两个表中的信息*/
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
@ApplicationName NVARCHAR(256),
@RoleName NVARCHAR(256),
@DeleteOnlyIfRoleIsEmpty BIT --只有当角色为空时删除(即无用户使用该角色,下面有用)
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN(1)
/*查询@ApplicationId(他好象都是通过这个和另外一个调用存储过程的方法来返回@ApplicationId的
两种方法在我看来好象是一样的样子,不过存储过程显然验证的东西多点.)*/
DECLARE @ErrorCode INT
SET @ErrorCode = 0
DECLARE @TranStarted BIT
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 ) ---如果当前活动事务为0,开始事务并设置事务参数为1
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
DECLARE @RoleId UNIQUEIDENTIFIER
SELECT @RoleId = NULL
SELECT @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
--声明角色ID并查询此角色的角色ID
IF (@RoleId IS NULL) ---如果不存在
BEGIN
SELECT @ErrorCode = 1 --返回错误
GOTO Cleanup
END
IF (@DeleteOnlyIfRoleIsEmpty <> 0) --如果@DeleteOnlyIfRoleIsEmpty不为0
BEGIN
IF (EXISTS (SELECT RoleId FROM dbo.aspnet_UsersInRoles WHERE @RoleId = RoleId))
/*如果存在有用户在使用此角色,就是这意思吧.*/
BEGIN
SELECT @ErrorCode = 2 --返回错误代码2
GOTO Cleanup
END
END
DELETE FROM dbo.aspnet_UsersInRoles WHERE @RoleId = RoleId
/*从aspnet_UsersInRoles表中删除此角色的所有记录*/
IF( @@ERROR <> 0 ) --如果有错误,返回错误代码并跳转到回滚
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
DELETE FROM dbo.aspnet_Roles WHERE @RoleId = RoleId AND ApplicationId = @ApplicationId
/*从aspnet_Roles中删除此角色*/
IF( @@ERROR <> 0 ) ---如果有错误则回滚
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN(0)
/*因为角色在两个表中存在,所以删除某角色的时候必须删除两个表中的信息*/
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END