Any way to send mail on SQL trigger (without using the paid services)?

Question

I have SQL Server 2005 with Management Studio Express. Is there a way to send an email in a SQL trigger without having to buy the Agent or Database Mailer?

I've heard something about a sendEmail program but I can't figure out what to do to configure it and send mail.

Answer 1

I wouldn't do that in a trigger - after all, sending the e-mail could potentially take quite some time - and a trigger should be lean and mean and do the least amount of work possible. Don't do extensive, time-consuming processing in a trigger!

What I'd do is this:

• the trigger writes some information it needs into a "command" table - that's quick, that's easy, that doesn't take much time at all

• a separate, async process (a SQL Server Agent job in a full edition, or even a C# application) scan this "command" table at specified intervals, and if there's a command to send an e-mail, it sends out that e-mail (handling all the possible delays and retries etc.)

That way, your trigger will be quick and won't cause delays in your database processing, and the e-mails can be sent with whatever software components you might have at your disposal - asynchronously, separate from your main application. Your main application remains fast and responsive.