I am on my tablet so I cannot test, but from just reading the code it seems like it should work even if there is no row in the login-table. If there is no row then the selection of NUM_ALLOWED should eval to NULL and any test against NULL will be NULL therefore you should not hit the ROLLBACK. Have you confirmed the trigger works for any low-privilege (i.e. non-sysadmin) logins? I ask because I am wondering if you forgot to include EXECUTE AS 'logon_trigger_login'

COMMIT seems like a natural choice to add as the counter-task to ROLLBACK but it doesn't really work that way. A COMMIT has to be paired with an explicit BEGIN TRANSACTION statement whereas ROLLBACK can be issued without being paired with a BEGIN TRANSACTION because when you are inside a trigger it is implied that you are within, at the very least, an implicit transaction. The ROLLBACK serves as a signal that the current operation should be rolled back, whether that be a login attempt, or in the case of a DML trigger, maybe an UPDATE, DELETE or INSERT.