Logon triggers

As the name suggests  these triggers are invoked at logon event, theses event are raised after authentication but before user session is established



Example : Below example checks count of user session on the server and  rollbacks back if the session count is all ready greater than 100

CREATE TRIGGER limit_100_onnection_trigger 

ON ALL SERVER

FOR LOGON 

AS 

BEGIN 

IF (SELECT COUNT(*) FROM sys.dm_exec_sessions 

            WHERE is_user_process = 1 ) > 100 

    ROLLBACK; 

END;