SQL Server能否创建LOGON触发器修改用户表STATUS及会话状态变更？

1. 假设用户表结构 #

首先确保你的用户表包含匹配数据库登录名的用户名字段，以及状态字段，示例表结构如下：

USE SchoolProjectDB; -- 指定目标数据库
GO

CREATE TABLE user_accounts (
    username VARCHAR(50) PRIMARY KEY, -- 需与数据库登录名完全一致
    status VARCHAR(20) DEFAULT '不可用' -- 默认状态为未登录
);
GO

2. 创建LOGON触发器（仅在指定数据库触发） #

该触发器仅在用户登录到目标数据库时，更新对应用户的状态为已登录：

CREATE TRIGGER trg_UserLogon
ON ALL SERVER
FOR LOGON
AS
BEGIN
    SET NOCOUNT ON;

    -- 仅在指定数据库登录时执行状态更新
    IF DB_NAME() = 'SchoolProjectDB'
    BEGIN
        UPDATE SchoolProjectDB.dbo.user_accounts
        SET status = '已登录'
        WHERE username = ORIGINAL_LOGIN(); -- 获取当前登录的数据库用户名
    END
END;
GO

3. 创建LOGOFF触发器（会话关闭时更新状态） #

该触发器会在用户关闭会话时，自动将对应用户的状态重置为不可用：

CREATE TRIGGER trg_UserLogoff
ON ALL SERVER
FOR LOGOFF
AS
BEGIN
    SET NOCOUNT ON;

    -- 更新目标数据库中的用户状态
    UPDATE SchoolProjectDB.dbo.user_accounts
    SET status = '不可用'
    WHERE username = ORIGINAL_LOGIN();
END;
GO

4. 创建检查状态的存储过程 #

该存储过程用于查询指定用户的登录状态，并返回对应提示：

USE SchoolProjectDB;
GO

CREATE PROCEDURE sp_CheckUserStatus
    @username VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @userStatus VARCHAR(20);

    SELECT @userStatus = status
    FROM user_accounts
    WHERE username = @username;

    -- 根据状态输出提示
    IF @userStatus = '已登录'
        PRINT '用户' + @username + '当前可用';
    ELSE
        PRINT '用户' + @username + '不可用';
END;
GO

关键注意事项 #

• 创建服务器级触发器需要ALTER ANY SERVER TRIGGER权限
• 必须保证user_accounts表的username字段与数据库登录名完全匹配，否则状态更新会失效
• 此方案仅为满足学校项目需求设计，生产环境中更推荐直接查询系统视图（如sys.dm_exec_sessions）获取实时登录状态，无需维护额外状态字段

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