Need to be pretty careful with this trigger.

Consider if the call to exec master.xp_sendMail .... fails.

This is a trigger - an error will cause an automatic rollback of the transaction in which the trigger was fired. Not only will the email/sms not be send but also the action which caused it to be raised - i.e. the insert into the table will not happen. If this insert was called from e.g. a stored proc then whatever was in there would also roll back.

I had a very similar problem many moons ago with a USERS table for a web site that had a trigger to send a rgistration email - lo and behold if exchange falls over or we can't connect to it then not only do they not receive their registration email but they also don't get registered on the site - i.e. no row appears in the USERS table. Very dangerous.