Login triggers were designed as a part of the row level access control feature in Adaptive Server. In this context, you can use a login trigger in combination with the features for access rules and application contexts to set up row-level access controls, once a session logs in to Adaptive Server.However, you can use login triggers for other purposes as well. The following example limits the number of concurrent connections to Adaptive Server that a specific login can make.
create procedure my_proc as declare @cnt int, @limit int, @loginname varchar(32) select @limit = 2 -- max nr. of concurrent logins select @cnt = count(*) /*determinecurrent #sessions from master.dbo.sysprocesses*/ where suid = suser_id() if @cnt > @limit -- check the limit begin select @loginname = suser_name() print "Aborting login [%1!]: exceeds session limit [%2!]", @loginname, @limit select syb_quit() /*abort this session*/end
If two other sessions are already active, when the login connects to Adaptive Server, the current session is terminated by the login trigger calling the syb_quit() function:
% isql -SASE125 -Umy_login -Pmypasswd 1> select 1CT-LIBRARY error: ct_results(): network packet layer: internal net library error: Net-Library operation terminated due to disconnect
This message appears in the Adaptive Server error log file:
(...) background task message: Aborting login [ my_login]:exceeds session limit [2]
This allows you to limit the number of concurrent connections for a specific login, but it has one disadvantage: the client application cannot easily detect the reason the session was terminated. To display a message to the user, such as “Too many users right now—please try later,” you need a different approach.Instead of calling the built-in function syb_quit(), which causes the server to simply terminate the current session, you can deliberately cause an error in the stored procedure to abort the login trigger stored procedure.
For example, dividing by zero aborts the login trigger stored procedure, terminates the session, and causes a message to appear.
