Hi. I'm from VietNam. I've read your article and very excited about it. But when I do like you, I can't send a message to a mobiphone or e-mail. I think your SMS provider's rules not suitable to my country but I can't solve that problem. Can you help me? Phone number in my country have format like 840958149760. 84 is the code of my country. 095 is the code of the provider. 8149760 is the phone number. :). Can you contact with me throught my e-mail (kimxuyen.it@gmail.com) or yahooID: kimxuyenit? Thanks!

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.

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?

I created the trigger successfully but when I tried to update the table the query doesn't execute it prints out error stating that Msg 15281, Level 16, State 1, Procedure xp_sendmail, Line 1 SQL Server blocked access to procedure 'sys.xp_sendmail' of component 'SQL Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'SQL Mail XPs' by using sp_configure. For more information about enabling 'SQL Mail XPs', see "Surface Area Configuration" in SQL Server Books Online. Any help would be more appreciated am beginner to sqlserver

bsr.anwar (5/8/2014)I created the trigger successfully but when I tried to update the table the query doesn't execute it prints out error stating that Msg 15281, Level 16, State 1, Procedure xp_sendmail, Line 1 SQL Server blocked access to procedure 'sys.xp_sendmail' of component 'SQL Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'SQL Mail XPs' by using sp_configure. For more information about enabling 'SQL Mail XPs', see "Surface Area Configuration" in SQL Server Books Online. Any help would be more appreciated am beginner to sqlserver