examples of sql triggers (SQLite)

triggers.sql

-- users
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'user'
        CHECK (role IN ('user', 'moderator', 'admin')),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    last_login_at TIMESTAMP,
    banned_at TIMESTAMP,
    deleted_at TIMESTAMP
);

-- user profiles
CREATE TABLE IF NOT EXISTS user_profiles (
    user_id INTEGER PRIMARY KEY,
    bio TEXT,
    website TEXT,
    avatar_url TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

-- refresh tokens
CREATE TABLE IF NOT EXISTS refresh_tokens (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    token_hash TEXT NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

-- moderator actions
CREATE TABLE IF NOT EXISTS moderator_actions (
    id INTEGER PRIMARY KEY,
    target_user_id INTEGER NOT NULL,
    moderator_id INTEGER NOT NULL,
    action TEXT NOT NULL CHECK (action in ('ban', 'unban', 'delete', 'undelete')),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (moderator_id != target_user_id),
    FOREIGN KEY (target_user_id) REFERENCES users (id) ON DELETE CASCADE,
    FOREIGN KEY (moderator_id) REFERENCES users (id) ON DELETE CASCADE
);

-- creates a profile for each new user
CREATE TRIGGER IF NOT EXISTS trg_create_profile
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_profiles (user_id)
    VALUES (NEW.id);
END;

-- update the update_at column of each updated user
CREATE TRIGGER IF NOT EXISTS trg_update_user
AFTER UPDATE ON users
FOR EACH ROW
WHEN OLD.updated_at IS NEW.updated_at
BEGIN
    UPDATE users
    SET updated_at = CURRENT_TIMESTAMP
    WHERE id = NEW.id;
END;

-- update the update_at column of each updated user profile
CREATE TRIGGER IF NOT EXISTS trg_update_user_profile
AFTER UPDATE ON user_profiles
FOR EACH ROW
WHEN OLD.updated_at IS NEW.updated_at
BEGIN
    UPDATE user_profiles
    SET updated_at = CURRENT_TIMESTAMP
    WHERE user_id = NEW.user_id;
END;

-- update login date when new refresh token is created
CREATE TRIGGER IF NOT EXISTS trg_update_login_date
AFTER INSERT ON refresh_tokens
FOR EACH ROW
BEGIN
    UPDATE users
    SET last_login_at = CURRENT_TIMESTAMP
    WHERE id = NEW.user_id;
END;

-- delete refresh tokens for banned users
CREATE TRIGGER IF NOT EXISTS trg_delete_refresh_tokens
AFTER UPDATE OF banned_at ON users
FOR EACH ROW
WHEN
    OLD.banned_at IS NULL
    AND NEW.banned_at IS NOT NULL
BEGIN
    DELETE FROM refresh_tokens
    WHERE user_id = NEW.id;
END;

-- delete refresh tokens for deleted users
CREATE TRIGGER IF NOT EXISTS trg_delete_refresh_tokens_del
AFTER UPDATE OF deleted_at ON users
FOR EACH ROW
WHEN
    OLD.deleted_at IS NULL
    AND NEW.deleted_at IS NOT NULL
BEGIN
    DELETE FROM refresh_tokens
    WHERE user_id = NEW.id;
END;

-- prevent refresh token creation for banned users
CREATE TRIGGER IF NOT EXISTS trg_refresh_tokens_no_banned_user
BEFORE INSERT ON refresh_tokens
FOR EACH ROW
    WHEN EXISTS (
        SELECT 1 FROM users
        WHERE
            id = NEW.user_id
            AND banned_at IS NOT NULL
    )
BEGIN
    SELECT RAISE (ABORT, 'User is banned');
END;

-- prevent refresh token creation for deleted users
CREATE TRIGGER IF NOT EXISTS trg_refresh_tokens_no_deleted_user
BEFORE INSERT ON refresh_tokens
FOR EACH ROW
    WHEN EXISTS (
        SELECT 1 FROM users
        WHERE
            id = NEW.user_id
            AND deleted_at IS NOT NULL
    )
BEGIN
    SELECT RAISE (ABORT, 'User is deleted');
END;

-- only moderators and admins may act on moderator actions
CREATE TRIGGER IF NOT EXISTS trg_mod_actions_for_mods
BEFORE INSERT ON moderator_actions
FOR EACH ROW
WHEN
    (SELECT role FROM users WHERE users.id = NEW.moderator_id) = 'user'
BEGIN
    SELECT RAISE (ABORT, 'Only moderators and admins may act on moderator actions');
END;

-- mods cannot manage mods or admins
CREATE TRIGGER IF NOT EXISTS trg_mod_rights
BEFORE INSERT ON moderator_actions
FOR EACH ROW
WHEN
    (SELECT role FROM users WHERE users.id = NEW.moderator_id) = 'moderator'
    AND
    (SELECT role FROM users WHERE users.id = NEW.target_user_id) IN ('admin', 'moderator')
BEGIN
    SELECT RAISE (ABORT, 'Moderators cannot manage other moderators or admins');
END;

-- only admins can (un)delete users
CREATE TRIGGER IF NOT EXISTS trg_admin_rights
BEFORE INSERT ON moderator_actions
FOR EACH ROW
WHEN
    (SELECT role FROM users WHERE users.id = NEW.moderator_id) = 'moderator'
    AND (NEW.action = 'delete' OR NEW.action = 'undelete')
BEGIN
    SELECT RAISE (ABORT, 'Moderators cannot delete or undelete users');
END;

-- execute ban
CREATE TRIGGER IF NOT EXISTS trg_execute_ban
AFTER INSERT ON moderator_actions
FOR EACH ROW
WHEN
    NEW.action = 'ban'
BEGIN
    UPDATE users
    SET banned_at = CURRENT_TIMESTAMP
    WHERE id = NEW.target_user_id;
END;

-- execute unban
CREATE TRIGGER IF NOT EXISTS trg_execute_unban
AFTER INSERT ON moderator_actions
FOR EACH ROW
WHEN
    NEW.action = 'unban'
BEGIN
    UPDATE users
    SET banned_at = NULL
    WHERE id = NEW.target_user_id;
END;

-- execute deletion
CREATE TRIGGER IF NOT EXISTS trg_execute_delete
AFTER INSERT ON moderator_actions
FOR EACH ROW
WHEN
    NEW.action = 'delete'
BEGIN
    UPDATE users
    SET deleted_at = CURRENT_TIMESTAMP
    WHERE id = NEW.target_user_id;
END;

-- execute undeletion
CREATE TRIGGER IF NOT EXISTS trg_execute_undelete
AFTER INSERT ON moderator_actions
FOR EACH ROW
WHEN
    NEW.action = 'undelete'
BEGIN
    UPDATE users
    SET deleted_at = NULL
    WHERE id = NEW.target_user_id;
END;

-- sample users (with password = test)
INSERT INTO users (username, password_hash, role)
VALUES
    (
        'admin',
        '$2b$10$xgxzXkCwKdtEFsM5FUJo4eUbm9O1/riBDV8/4U0h7C78wdPWqhNjm',
        'admin'
    ),
    (
        'aisha',
        '$2b$10$xgxzXkCwKdtEFsM5FUJo4eUbm9O1/riBDV8/4U0h7C78wdPWqhNjm',
        'moderator'
    ),
    (
        'maya',
        '$2b$10$xgxzXkCwKdtEFsM5FUJo4eUbm9O1/riBDV8/4U0h7C78wdPWqhNjm',
        'user'
    ),
    (
        'elias',
        '$2b$10$xgxzXkCwKdtEFsM5FUJo4eUbm9O1/riBDV8/4U0h7C78wdPWqhNjm',
        'user'
    ),
     (
        'melanie',
        '$2b$10$xgxzXkCwKdtEFsM5FUJo4eUbm9O1/riBDV8/4U0h7C78wdPWqhNjm',
        'user'
    ),
     (
        'amara',
        '$2b$10$xgxzXkCwKdtEFsM5FUJo4eUbm9O1/riBDV8/4U0h7C78wdPWqhNjm',
        'user'
    )
ON CONFLICT (username) DO NOTHING;