• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

Trigger an email

Using NT 4 and SQL 7.
Is it possible (and if so, how) to send an email when a certain field in a certain table changes?

I'm thinking like a trigger calling a stored procedure, or something like that.
1 Solution
Brendt HessSenior DBACommented:
Look at xp_SendMail.  You might use it like this:

CREATE TRIGGER t_ItChanged ON MyTable FOR Update AS

If Update(MyColumn)
    EXEC xp_sendmail 'myemailaddress', 'The MyColumn field on ' + Cast(inserted.ID) + ' was changed from ' + deleted.MyColumn + ' to ' + inserted.MyColumn + '.'

What bhess1 has said is correct, but you will need to make sure that you have the SQL Mail configured on the server to send those mails.  These details would be available in the sql online books.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Furthermore, i recommend not to send emails directly in triggers, as this slows down the initial action, especially if there are email problems...

I suggest that you have separate tables where you mark that email(s) should be send (and why), and let a separate process (job) send the emails at regular intervals.

JOKAuthor Commented:
Thanks for the comments. Its going to be a few days before I can get to try this. If you don't hear anything back by Wednesday (8/8) feel free to bug/remind me.
JOKAuthor Commented:
Thanks to all. Haven't finished yet, but I'm on the right track. Sorry for the delay. Giving points to the everyone because every little bit helped.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now