aspnet_Membership_UpdateUser --更新用户数据

 1 ALTER    PROCEDURE  dbo.aspnet_Membership_UpdateUser  -- 更新用户数据
 2      @ApplicationName        NVARCHAR ( 256 ),
 3      @UserName               NVARCHAR ( 256 ),
 4      @Email                  NVARCHAR ( 256 ),
 5      @Comment                NTEXT ,
 6      @IsApproved             BIT ,
 7      @LastLoginDate          DATETIME ,
 8      @LastActivityDate       DATETIME ,
 9      @UniqueEmail            INT ,
10      @TimeZoneAdjustment     INT
11 AS
12 BEGIN
13      DECLARE   @UserId   UNIQUEIDENTIFIER
14      DECLARE   @ApplicationId   UNIQUEIDENTIFIER
15      SELECT    @UserId   =   NULL
16      SELECT    @UserId   =  u.UserId,  @ApplicationId   =  a.ApplicationId
17      FROM     dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
18      WHERE    LoweredUserName  =   LOWER ( @UserName )  AND
19             u.ApplicationId  =  a.ApplicationId   AND
20              LOWER ( @ApplicationName )  =  a.LoweredApplicationName  AND
21             u.UserId  =  m.UserId
22      /**/ /*查询此用户的用户ID和应用程序ID*/
23
24      IF  ( @UserId   IS   NULL )  -- 如果为空，返回1
25          RETURN ( 1 )
26
27      IF  ( @UniqueEmail   =   1 )  -- 如果邮件地址唯一
28      BEGIN
29          IF  ( EXISTS  ( SELECT   *
30                      FROM   dbo.aspnet_Membership  WITH  (UPDLOCK,  HOLDLOCK )
31                      WHERE  ApplicationId  =   @ApplicationId    AND   @UserId   <>  UserId  AND  LoweredEmail  =   LOWER ( @Email )))
32          /**/ /*如果存在相同的邮件地址，返回7（用户ID 不同，所以是判断是否有相同地址的意思）*/
33          BEGIN
34              RETURN ( 7 )
35          END
36      END
37
38      DECLARE   @TranStarted     BIT
39      SET   @TranStarted   =   0
40
41      IF (  @@TRANCOUNT   =   0  )  -- 如果当前活动事务为0，开始事务并设置事务参数为1
42      BEGIN
43          BEGIN   TRANSACTION
44          SET   @TranStarted   =   1
45      END
46      ELSE
47      SET   @TranStarted   =   0
48
49      UPDATE  dbo.aspnet_Membership
50      SET
51          Email             =   @Email ,
52          LoweredEmail      =   LOWER ( @Email ),
53          Comment           =   @Comment ,
54          IsApproved        =   @IsApproved ,
55          LastLoginDate     =   DATEADD (n,  - @TimeZoneAdjustment ,  @LastLoginDate )
56      WHERE
57         @UserId   =  UserId
58        /**/ /*更新表aspnet_Membership*/
59
60
61      IF (  @@ERROR   <>   0  ) -- 错误则回滚
62          GOTO  Cleanup
63
64      UPDATE  dbo.aspnet_Users
65      SET
66          LastActivityDate  =   DATEADD (n,  - @TimeZoneAdjustment ,  @LastActivityDate )
67      WHERE
68         @UserId   =  UserId
69      /**/ /*更新表aspnet_Users*/
70      IF (  @@ERROR   <>   0  )   -- 错误则回滚
71          GOTO  Cleanup
72
73      IF (  @TranStarted   =   1  )
74      BEGIN
75      SET   @TranStarted   =   0
76      COMMIT   TRANSACTION
77      END
78
79      RETURN   0
80
81 Cleanup:
82
83      IF (  @TranStarted   =   1  )
84      BEGIN
85          SET   @TranStarted   =   0
86          ROLLBACK   TRANSACTION
87      END
88
89      RETURN   - 1
90 END

转载于:https://www.cnblogs.com/ruanbl/archive/2006/08/31/491051.html