I just want to mention that I agree with MackamMagic, that you need to be very careful when doing something like this in a trigger, in fact I recommend against it, although as MackamMagic also mentions this is not as much an issue with SQL Server 2005. If you need to do any action outside the database I recommend queuing the data using a trigger and have a job or windows service that does the action.

The other thing is that are you sure you only want to send an email or SMS out when only 1 row is updated? At the least shouldn't someone be notified that a mass update was done?