May I expand a little bit on the question. How about conditional drop of the trigger?

Let's assume that I need a script which would recreate a trigger. So, if trigger exists, it has to be dropped. According to MSDN the code should be something like this:

IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class = 0 AND name = 'connection_limit_trigger')

DROP TRIGGER connection_limit_trigger ON ALL SERVER

GO

CREATE TRIGGER connection_limit_trigger

ON ALL SERVER WITH EXECUTE AS 'navi'

FOR LOGON

AS

BEGIN

IF ORIGINAL_LOGIN()= 'navi' AND

(SELECT COUNT(*) FROM sys.dm_exec_sessions

WHERE is_user_process = 1 AND

original_login_name = 'navi') > 3

ROLLBACK;

END;

Yet, for some reason the LOGON trigger on ALL SERVER is not listed in sys.triggers.

So, what the check query for LOGON trigger should be?