會員系統 Email 驗證機制完整實作：資料庫設計、觸發器、預存程序與流程

建立完整的會員 Email 驗證與忘記密碼流程，含資料表結構、觸發器自動重置驗證狀態、請求處理預存程序、驗證/重設密碼預存程序，前後端分工明確。

一、核心資料表設計

1. 會員主檔擴充

ALTER TABLE dbo.Users ADD 
    Email VARCHAR(100) NULL,
    IsVerify BIT NOT NULL DEFAULT(0);

2. 請求動作類型表

CREATE TABLE dbo.RequestActionType (
    Type NCHAR(2) NOT NULL PRIMARY KEY,
    Description NVARCHAR(30) NULL
);
INSERT INTO dbo.RequestActionType VALUES 
    ('V', N'驗證 Email 有效性'),
    ('C', N'使用者變更密碼');

3. 使用者請求記錄表

CREATE TABLE dbo.UserRequestAction (
    Type NCHAR(2) NOT NULL FOREIGN KEY REFERENCES RequestActionType(Type),
    Token UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    Account NVARCHAR(30) NOT NULL,
    Email NVARCHAR(150) NOT NULL,
    Expire DATETIME NOT NULL DEFAULT DATEADD(DAY, 1, CURRENT_TIMESTAMP)
);

二、觸發器：Email 變更自動重置驗證

CREATE TRIGGER tri_Users_Update ON dbo.Users FOR UPDATE AS
DECLARE @newEmail VARCHAR(100), @oldEmail VARCHAR(100), @account NVARCHAR(20), @isVerify BIT
SELECT @newEmail = Email FROM INSERTED
SELECT @account = U_nbr, @oldEmail = Email, @isVerify = IsVerify FROM DELETED
IF @isVerify = 1 AND (LEN(ISNULL(@newEmail,'')) = 0 OR @newEmail <> @oldEmail)
    UPDATE dbo.Users SET IsVerify = 0 WHERE U_nbr = @account

三、統一請求處理預存程序

CREATE PROCEDURE usp_UserRequestAction_Request
    @Type NCHAR(2), @U_nbr NVARCHAR(20)
AS
IF NOT EXISTS(SELECT 1 FROM dbo.Users WHERE U_nbr = @U_nbr)
    RAISERROR(N'帳號不存在', 16, 1)

DECLARE @Email NVARCHAR(100)
SELECT @Email = Email FROM dbo.Users WHERE U_nbr = @U_nbr

IF EXISTS(SELECT 1 FROM dbo.UserRequestAction 
          WHERE Type = @Type AND Account = @U_nbr AND Email = @Email)
    UPDATE dbo.UserRequestAction 
    SET Token = NEWID(), Expire = DATEADD(DAY, 1, CURRENT_TIMESTAMP)
    WHERE Type = @Type AND Account = @U_nbr AND Email = @Email
ELSE
    INSERT INTO dbo.UserRequestAction(Type, Account, Email) 
    VALUES (@Type, @U_nbr, @Email)

四、驗證/重設密碼預存程序

1. Email 驗證確認

CREATE PROCEDURE usp_Users_UpdateIsVerifyField @token NVARCHAR(36) AS
IF EXISTS(SELECT 1 FROM dbo.UserRequestAction 
          WHERE Token = @token AND Expire >= CURRENT_TIMESTAMP)
BEGIN
    DECLARE @account NVARCHAR(30)
    SELECT @account = Account FROM dbo.UserRequestAction WHERE Token = @token
    UPDATE dbo.Users SET IsVerify = 1 WHERE U_nbr = @account
    UPDATE dbo.UserRequestAction SET Expire = DATEADD(DAY, -1, CURRENT_TIMESTAMP) WHERE Token = @token
END

2. 密碼重設

CREATE PROCEDURE usp_Users_ResetPassword @token NVARCHAR(36), @Password NVARCHAR(100) AS
IF EXISTS(SELECT 1 FROM dbo.UserRequestAction 
          WHERE Token = @token AND Expire >= CURRENT_TIMESTAMP)
BEGIN
    DECLARE @account NVARCHAR(30), @pwd VARBINARY(MAX)
    SELECT @account = Account FROM dbo.UserRequestAction WHERE Token = @token
    SET @pwd = ENCRYPTBYPASSPHRASE('insus#sec!%y', @Password)
    UPDATE dbo.Users SET Pwd = @pwd WHERE U_nbr = @account
    UPDATE dbo.UserRequestAction SET Expire = DATEADD(DAY, -1, CURRENT_TIMESTAMP) WHERE Token = @token
END
ELSE
    RAISERROR(N'連結失效或已過期', 16, 1)

五、前端流程串接

1. 會員登入後點「驗證 Email」→ 呼叫 usp_UserRequestAction_Request 'V', @U_nbr → 寄送含 Token 連結信件
2. 忘記密碼頁輸入帳號 → 呼叫 usp_UserRequestAction_Request 'C', @U_nbr → 寄送重設連結
3. 使用者點擊信件連結 → 導向驗證頁/重設頁 → 驗證 Token 有效性 → 呼叫對應預存程序
4. 成功後將請求記錄 Expire 設為過期，防重複使用

安全性加強建議

• Token 使用 NEWID() 或 CRYPT_GEN_RANDOM(32) 產生高熵值
• 信件連結採 HTTPS，Token 僅在 URL 參數傳遞，不記錄 Log
• 限制同一帳號/Email 分鐘級請求頻率
• 密碼儲存採鹽值雜湊（HASHBYTES('SHA2_256', salt + pwd)），勿用可逆加密

此架構清晰分離資料層邏輯，前端僅負責流程導向與介面呈現，擴充性與維護性俱佳。