• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • 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.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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