How can I automatically trigger a script when my DB fails over?
For a cluster failover you can use a startup procedure. Assuming you have xp_cmdshell enabled and the service account has adequate permissions to start services:

USE [master];
GO

CREATE PROCEDURE dbo.StartService
AS
BEGIN
  SET NOCOUNT ON;

  EXEC master..xp_cmdshell 'net start servicename';
END
GO

EXEC [master].dbo.sp_procoption 
   'dbo.StartService', 
   'startup', 
   'true';


I blogged about this for different reasons here:


https://sqlblog.org/2009/09/18/managing-multi-instance-cluster-failovers-with-different-hardware


You could also not use the startup option and instead just call the stored procedure from a job, and schedule the job to run when SQL Server Agent starts:



Of course this depends on the SQL Agent service starting successfully, and also assumes that the Agent or SQL Server services do not depend on the service you're trying to start. :-)