A trigger would be the most 'immediate' way to do this, there will always be some mail server latency.

I am not a fan of triggers so generally I will create a SQL Server job to check for a condition and run it at some interval. You'd have to consider whether this is immediate enough and whether the repeated polling of you logging table has any performance or server resource implications