Using an update trigger to send an email

  • lmayer

    Old Hand

    Points: 325

    Hi there,

    I am trying to write my first trigger. I need it to fire off an email to the help desk when the enroll_separation field in the academic table is changed from enrolled to kicked out. Does anyone have any idea where I could look for an example? Never having done this before I am at a serious disadvantage.

    Thanks

    Laura

  • David Benoit

    SSC-Dedicated

    Points: 34562

    I wouldn't do the mail in a trigger but rather a stored procedure and then create a job to run the procedure every 5 minutes or so. That way when (and notice I said when) you have SQL Mail hang then your table won't get locked up. Just my two cents.

    The trigger would be great to populate a "notices" table that would have information like Mail recipient, Message, Subject, etc. You may want to consider looking at the variables used in xp_sendmail and creating your table "notices" to be similar in style. Then your procedure could loop through this table to send out the notices on the schedule mentioned above.

    If you would like an example, let me know and I will put something up.

    Hope this helps.

    David

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • lmayer

    Old Hand

    Points: 325

    David,

    Stored procs I can do! I'll try to make it on my own, if I have problems I'll come back. Can xp_sendmail work with SQL 7.0 or is that just with SQL 2K?

    Thanks again

    Laura

  • Andy Warren

    SSC Guru

    Points: 119694

    xp_sendmail works in both versions.

    Andy

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply