如何在SQL Server中创建表实现邮箱地址Active/Inactive状态管理

First, let's tackle your core challenges: designing a table to track email statuses and implementing triggers to handle confirmation emails. I’ll use PostgreSQL for code examples (adjust syntax for MySQL, SQL Server, etc., if needed).

1. Database Table Design #

You’ll need two main tables: one to track user email subscriptions, and another to queue email jobs (since database triggers can’t directly send emails).

User Email Subscription Table #

This table stores the user’s email, activation status, and unique token for the activation link:

CREATE TABLE user_email_subscriptions (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    email_address VARCHAR(255), -- Nullable for initial state with no email
    status VARCHAR(20) NOT NULL DEFAULT 'Inactive', -- 'Active' or 'Inactive'
    activation_token VARCHAR(64) UNIQUE, -- Random token for activation link
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Add indexes for faster lookups
CREATE INDEX idx_user_email_subscriptions_user_id ON user_email_subscriptions(user_id);
CREATE INDEX idx_user_email_subscriptions_activation_token ON user_email_subscriptions(activation_token);

• email_address: Nullable to support the initial state where no email is set. When a user first enters an email, populate this column and let status default to 'Inactive'.
• status: Defaults to 'Inactive' so any new email (initial or updated) starts in this state automatically.
• activation_token: A random unique string (use gen_random_uuid() or a hash function) to create a secure activation link.

Email Queue Table #

This table acts as a buffer for sending emails, since triggers can’t execute external code directly:

CREATE TABLE email_queue (
    id SERIAL PRIMARY KEY,
    recipient_email VARCHAR(255) NOT NULL,
    email_type VARCHAR(20) NOT NULL, -- e.g., 'activation'
    template_data JSONB NOT NULL, -- Stores token, user ID, etc.
    sent BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

2. Trigger to Queue Activation Emails #

We’ll create a trigger that fires whenever a new email is inserted or an existing email is updated (resulting in an 'Inactive' status). The trigger adds a job to the email_queue table.

First, create the trigger function:

CREATE OR REPLACE FUNCTION queue_activation_email()
RETURNS TRIGGER AS $$
BEGIN
    -- Only queue if email exists and status is Inactive
    IF NEW.email_address IS NOT NULL AND NEW.status = 'Inactive' THEN
        INSERT INTO email_queue (recipient_email, email_type, template_data)
        VALUES (
            NEW.email_address,
            'activation',
            jsonb_build_object(
                'user_id', NEW.user_id,
                'activation_token', NEW.activation_token
            )
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Then attach it to the subscription table for INSERT and UPDATE events:

CREATE TRIGGER trigger_queue_activation_email
AFTER INSERT OR UPDATE OF email_address, status ON user_email_subscriptions
FOR EACH ROW
EXECUTE FUNCTION queue_activation_email();

This trigger runs after inserting a new row or updating the email_address/status columns. It checks if the email is valid and inactive, then adds an activation email job to the queue.

3. Handling Initial Setup & Email Modifications #

Initial Email Setup #

When a user enters their email for the first time, insert a row with the email, auto-set status to 'Inactive', and generate a token:

INSERT INTO user_email_subscriptions (user_id, email_address, activation_token)
VALUES (123, 'user@example.com', gen_random_uuid());

The trigger will automatically queue the activation email.

Updating Existing Email #

When a user modifies their email, reset the status to 'Inactive' and generate a new token:

UPDATE user_email_subscriptions
SET 
    email_address = 'new_user@example.com',
    status = 'Inactive',
    activation_token = gen_random_uuid(),
    updated_at = CURRENT_TIMESTAMP
WHERE user_id = 123;

The trigger will queue a new activation email for the updated address.

4. Activation Workflow #

When the user clicks the activation link (e.g., https://your-app.com/activate?token=abc123), your backend should:

1. Look up the subscription row using the activation_token.
2. If found and status is 'Inactive', update the row to set status = 'Active' and clear the token (or keep it for reference):

UPDATE user_email_subscriptions
SET 
    status = 'Active',
    activation_token = NULL,
    updated_at = CURRENT_TIMESTAMP
WHERE activation_token = 'abc123' AND status = 'Inactive';

3. Mark the corresponding email in email_queue as sent (optional).

5. Sending Emails from the Queue #

You’ll need a background service (e.g., cron job, Python script, or backend task) that periodically checks the email_queue for unsent jobs. For each activation email:

1. Retrieve the recipient email and template data (token, user ID).
2. Generate the activation link using the token.
3. Send the email via your service (SMTP, SendGrid, etc.).
4. Mark the job as sent = TRUE in email_queue.

Key Notes #

• Null Email Handling: The email_address column is nullable to support the initial state where no email is set.
• Token Uniqueness: Use UUIDs or secure hashes to ensure activation tokens are unique and hard to guess.
• Trigger Edge Cases: The trigger only queues emails when the status is 'Inactive', so activating a user won’t re-send the email.

内容的提问来源于stack exchange，提问作者John Vasquez