Can a SQL Server Trigger send me an email?

views:

185

answers:

+2 Q:

Can a SQL Server Trigger send me an email?

I wish to send an email from a Trigger, on my SQL Server 2008 machine. The data of the email will be, basically, some of the Trigger information.

Can someone provide some simple/sample code on how to do this, please? E.g. what's the system stored procedure called? Etc.

I've not set up any SQL mail and stuff, so I'm guessing it's built in and I can leverage that. But just to be sure: do I need to install any extra software on the server?

+1 A:

Triggers should be kept to a minimum time. Atomic database updates / inserts / deletes should be allowed to be as fast as possible. Consider adding a separate table which the trigger can insert data into which a separate process monitors and initiates an email based on the contents.

Of course, this doesn't address whether or not it's possible to use TransactSQL to create an email - I'm curious about that one myself!

Kieveli 2009-11-23 00:06:20

Re: Triggers - yep. i know that, etc. etc. And this is only for deletes .. and only for some debugging purposes AND on some low activity tables AND sql email is async, i believe ..

Pure.Krome 2009-11-23 00:11:21

Database mail is, but SQLMail and other variants (especially using CDO via sp_OA) are not. In your original question you didn't say you were using database mail, you actually said SQL mail. There is a big difference. In any case, even if you are using database mail on your current system, what if you move your code to another system where you can't use database mail for some reason?

Aaron Bertrand 2009-11-23 13:41:40

+2 A:

Yep.

http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

NitroxDM 2009-11-23 00:07:33

Can you? Yes. Should you? Absolutely not, IMHO.

http://sqlblog.com/blogs/aaron%5Fbertrand/archive/2009/10/12/bad-habits-to-kick-abusing-triggers.aspx

Aaron Bertrand 2009-11-23 01:12:30

Wow, I'm glad I put all that work into blog posts like that just to have people do drive-by down-votes without an explanation. I guess I'll really think hard the next time I have some prescriptive reading to offer.

Aaron Bertrand 2009-11-23 13:38:51

<Sarcasm>I love it when people down vote without explanation! It just makes me feel good.</Sarcasm> It should be a SO rec to add some explanation to a down vote even if it was just a generic tag.

NitroxDM 2009-11-23 15:37:30

And I think it would be less attractive if it couldn't be anonymous. Often I bet people just don't like to be told what the right answer is, so their comeback is a down-vote. :-\

Aaron Bertrand 2009-11-23 15:49:15

ansaurus

tags:

views:

answers:

Can a SQL Server Trigger send me an email?

related questions