Sending the email is a business rule and should not be in the database. Your idea of a service is better.

>In foxpro we have an application that checks periodically for new records on a table (dbf) and when it founds one it will send and e-mail based on the new record. The system was conceived originally for pagers so it has some limitations, but it was later been used for some other notifications (regular e-mails) so we were thinking on writing a .NET service to do the same task with a SQL back-end and more options, but then we realized that the service is completely superfluous as we can just write an instead of insert trigger to handle the e-mail, so we wrote said trigger and it is working fine.
>
>The question is if there is any ill-effect of doing something like this, for example, would SQL server have any problems handling something like 500 e-mails per day? The emails might come in waves, meaning that it might need to send lots of e-mails in a short period and then just idle. Is there any known issue with sp_send_dbmail that I should be aware of? I tried to google for it but lot of worthless reading.
>
>I do not expect this to be a problem for the server as it does not seem to be a lot of work, but I thought I would ask before proposing this solution.
>
>Thanks,
>Hugo