How to execute some script when database starts up

Question

I want to execute some T-sql code, when a database starts up.

the objective is to resume a session of mirroring in case the session is "suspended" when the database comes "back to life".

So I've done this code:

begin try
    Declare @state_desc nvarchar(60)
    SELECT @state_desc = mirroring_state_desc FROM SYS.database_mirroring WHERE database_id = DB_ID('MyDataBase')
    if @state_desc = 'SUSPENDED'
    begin
        ALTER DATABASE [MyDataBase] SET PARTNER RESUME
    end
end try
begin catch
end catch

but how can I make it when the sql server starts up?

I used this with no success:

create procedure dbm_startup_resume
                                as
                                begin
begin try
    Declare @state_desc nvarchar(60)
    WAITFOR DELAY '00:00:10'
    SELECT @state_desc = mirroring_state_desc FROM SYS.database_mirroring WHERE database_id = DB_ID('MinhaBaseDeDados')
    if @state_desc = 'SUSPENDED'
    begin
      ALTER DATABASE [MinhaBaseDeDados] SET PARTNER RESUME
    end
  end try
  begin catch
  end catch

exec sp_procoption @ProcName = dbm_startup_resume, @OptionName = startup, @OptionValue = 'on'

end

Answer 1

[Updated]

1. Create SQL Agent job for what you need.
2. Use Server-scoped DDL trigger for that

Approximately like this:

Create TRIGGER ddl_trig_AlterDatabes
ON ALL SERVER 
FOR ALTER_DATABASE 
AS 
    -- your code here
    -- use EventData() to check database name if needed.. or other info..
    Exec msdb..sp_start_job @job_name = 'job_name'