title: SQL Server on database drop trigger deleting associated agent jobs
body: <p>We have databases with associated SQL agent jobs that we keep track in a table called databasename.dbo.sqlagentjobs. Some databases are demos or QA databases and get regularly deleted.</p>
  <p>At some point it happens that the administrator forgets to delete the associated SQL Agent jobs. In this case I wanted to write a trigger on database drop like this:</p>
  <pre><code>create trigger deleteagentjobs on all server for drop_database as
  begin
   declare @databasename nvarchar(100)
   declare @eventdata xml
   declare @query nvarchar(1000)
  
   set @eventdata = EVENTDATA()
   set @databasename = @eventData.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(128)')
  
   if object_id(@databasename +'.dbo.sqlagentjobs') is not null begin -- check if the database is a database with &quot;our&quot; database scheme
    set @query = N'declare c insensitive cursor for select jobname_komplett from ' + @databasename + N'.dbo.sqlagentjobs ' + 
                 N'declare @jobname nvarchar(257) ' + 
                 N'declare @jobid uniqueidentifier ' +
                 N'open c ' +
                 N'fetch next from c into @jobname ' +
                 N'while @@FETCH_STATUS = 0 begin ' +
                 N' select job_id from msdb.dbo.sysjobs where name = @jobname ' +
                 N' exec msdb.dbo.sp_delete_job @jobid ' +
                 N' fetch next from c into @jobname ' +
                 N'end ' +
                 N'close c ' +
                 N'deallocate c '
    exec sp_sqlexec @query
   end
  end
  </code></pre>
  <p>The issue with the trigger is that I get the following logical error message when I try to drop the database as follows:</p>
  <pre><code>exec msdb.dbo.sp_delete_database_backuphistory @database_name = n'demo'
  go
  alter database [demo] set single_user with rollback immediate
  go
  drop database [demo]
  </code></pre>
  <p>Error message:</p>
  <pre><code>Msg 5064, Level 16, State 1, Line 3
  Changes to the state or options of database demo cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
  Msg 5069, Level 16, State 1, Line 3
  ALTER DATABASE statement failed.
  Msg 3702, Level 16, State 4, Line 5
  Cannot drop database demo because it is currently in use.
  </code></pre>
  <p>The error makes sense to me since I set the database to single user mode and try to access the database inside of the trigger.</p>
  <p>Any ideas how to fix this? Thanks in advance!</p>