How to enable and run database mail in SQL Server 2008? I know that it need to
I cannot think of any way to configure db mail to send for every rollback or transaction, that would be a little overkill, and it sounds like you want to be selective about it anyways.
What you can do though is add a call to the email sp after each commit / rollback in your stored procedures. Here's a quick example:
BEGIN TRAN -- sql operations here COMMIT -- send email EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DB Alerts', @recipients = 'firstname.lastname@example.org', @body = 'Commit completed for ...', @subject = 'SQL Commit/Rollback event';
You can read more about the parameters for sp_send_dbmail here: http://msdn.microsoft.com/en-us/library/ms190307.aspx
Hope this helps