Using SQL Server's global temporary table to prevent users from repeatedly logging on

When we develop business software, we often encounter the question of how to prevent users from repeatedly logging on to our system. Especially for the bank or the financial department, but also to restrict the user as its work number of multiple login.

Some people may say that in the User Information table, add a field to determine the status of user work number login, write 1 after login, write 0 when you exit, and when you log in to determine whether the sign is 1, if you do not let the user work number login. But this is bound to bring new problems: such as a power outage, such as unpredictable phenomenon, the system is abnormal exit, can not be the flag location of 0, then the next time the user work number login can not log in, this How to do?

Maybe we can change the train of thought: Is there anything that can be automatically recycled after the connection is disconnected? Yes, SQL Server's temp table has this feature! But we can't use a local temp table here, because a local temp table is a separate object for each connection, so we can only use the global temp table to achieve our goal.

Well, as the situation is clear, we can write a simple stored procedure like the following:

create procedure gp_findtemptable -- 2001/10/26
21:36 zhuzhichao in nanjing
/* 寻找以操作员工号命名的全局临时表
* 如无则将out参数置为0并创建该表,如有则将out参数置为1
* 在connection断开连接后,全局临时表会被SQL Server自动回收
* 如发生断电之类的意外,全局临时表虽然还存在于tempdb中,
但是已经失去活性
* 用object_id函数去判断时会认为其不存在.
*/
@v_userid varchar(6), -- 操作员工号
@i_out int out -- 输出参数 0:没有登录 1:已经登录
as
declare @v_sql varchar(100)
if object_id(''''tempdb.dbo.##''''+@v_userid) is null
begin
set @v_sql = ''''create table ##''''+@v_userid+
''''(userid varchar(6))''''
exec (@v_sql)
set @i_out = 0
end
else
set @i_out = 1

In this process, we see that if a global temporary table named with a user name does not exist, the procedure creates one and puts the out parameter to 0, and if it already exists, the out parameter is set to 1.

So, when we call this procedure in our application, if we get an out parameter of 1 o'clock, we can just jump out of a message and tell the user, "Sorry, this work is being used!" ”

(Test environment: Server: WINNT Server 4.0 SQL Server7.0 workstation: WINNT workstation)