SQL Server cluster node fail over details

Just to add, your sql agent job could look like this, which works for us (this assumes you have database mail configured properly):

Set the schedule to be when sql server starts as described in Ronaldo's answer

DECLARE @importance AS VARCHAR(6)
DECLARE @body AS NVARCHAR(1000)
DECLARE @Subject  AS NVARCHAR(1000)
DECLARE @InstanceName AS VARCHAR(100)
DECLARE @NodeName AS NVARCHAR(100)
DECLARE @recipientsList VARCHAR(100)

SELECT  @recipientsList ='[email protected];[email protected]'
SELECT  @InstanceName =@@SERVERNAME
SELECT  @NodeName = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(200))
SELECT  @Subject  = 'Failover occured for SQL Server Instance '+@InstanceName
SELECT  @body = 'Failover occured for SQL Server Instance '+@InstanceName + 'This instance is currently running on the node '+@NodeName
SELECT  @importance ='High' 
EXEC msdb.dbo.sp_send_dbmail 
     @profile_name ='Dbas',
     @recipients=@recipientsList,
     @subject = @subject ,
     @body = @body,
     @body_format = 'HTML' ,
     @importance = 'HIGH'

The CREATE TRIGGER doc states that you can only reference a view by an INSTEAD OF trigger:

table | view
The table or view on which the DML trigger runs. This table or view is sometimes referred to as the trigger table or trigger view. Specifying the fully qualified name of the table or view is optional. You can only reference a view by an INSTEAD OF trigger. You can't define DML triggers on local or global temporary tables.

And here's the INSTEAD OF definition:

INSTEAD OF
Specifies that the DML trigger launches instead of the triggering SQL statement, thus, overriding the actions of the triggering statements.

I assume if you would use an INSTEAD OF trigger on a system view, you could break something.

I believe a job would be the best option if you would choose it to start automatically when SQL Server Agent starts. This way you won't need to schedule it to run frequently to see if there was any change. It would only run when your SQL Server started (which happens during a fail over). Here's the option you should choose in your schedule: