title: DROP TRIGGER with ON ALL SERVER
body: <p>I have a trigger to prevent someone using Management Studio:</p>
  
  <pre><code>CREATE TRIGGER [TR_LOGON_APP]
  ON ALL SERVER 
  FOR LOGON
  AS
  BEGIN
     DECLARE @program_name NVARCHAR(128)
     DECLARE @host_name NVARCHAR(128)
  
     SELECT 
         @program_name = program_name, 
         @host_name = host_name
     FROM 
         sys.dm_exec_sessions AS c
     WHERE 
         c.session_id = @@spid
  
     IF @program_name LIKE '%Management%Studio%' 
     BEGIN
         RAISERROR('Access Denied.',16,1)
         ROLLBACK;
     END
  END;
  </code></pre>
  
  <p>How to check that if it already exists, the trigger will be dropped first? </p>
  
  <p>I have tried using :</p>
  
  <pre><code>IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'TR_LOGON_APP' AND [type] = 'TR')
  BEGIN
        DROP TRIGGER [TR_LOGON_APP];
  END;
  </code></pre>
  
  <p>and </p>
  
  <pre><code>IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'TR_LOGON_APP' AND [type] = 'TR')
  BEGIN
        DROP TRIGGER [TR_LOGON_APP] ON ALL SERVER;
  END;
  </code></pre>
  
  <p>But both return an error :</p>
  
  <blockquote>
    <p>System.Data.OleDb.OleDbException (0x80040E14): There is already an object named 'TR_LOGON_APP' in the database.</p>
  </blockquote>