#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    2
    Rep Power
    0

    Triggers (Beginner Questions)


    I'm new to work with SQL and though I've learned querying and a few small stored procedures I have an issue I need to try and resolve. So here is the scendario:

    I have 3 email fields in my database that I would like to send an email out too whenever one of the fields in my database changes.

    For example: when (Site_Status) field changes email the 3 emails in the record i'm updating (sales_email, Site_email, Management_email). I was told to do this through an application layer which I will implement at a later time, but for now I need some kind of trigger that I can use. I know triggers can be cumbersome and unreliable but at this time it's my only option. I will be using DBmail to email out these message updates. Thanks for your help.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    116
    Rep Power
    9
    I would set table foe emails needs to be send and separate this process to steps.
    1. Trigger changes (set triggers) and add records to table with changes
    2. set stored procedure which read records from table with changes, send emails and market them asp processed.
    3. run that stored procedure as SQL job on schedule
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    2
    Rep Power
    0
    The only problem is I'm not familiar with all these steps. Are you suggesting to put these emails into a separate table all together? I could do that without any problems. But the rest of the steps I don't know how to do.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    2
    Originally Posted by archive972
    The only problem is I'm not familiar with all these steps. Are you suggesting to put these emails into a separate table all together? I could do that without any problems. But the rest of the steps I don't know how to do.
    1. gk53 has anwered one of my questions regarding triggers, and has given a fine example on how to make it just search here...


    2. In my opinion, you could do your number two using DTS, in sql 2005 and up versions they are report something, DTS, or Data Transformation services provides a step by step process on how to manage SQL queries, and can even save some of your time because it has the utility for you to do it, in your problem, it has the ability to send emails

    3. This is easy


    HTH... or maybe i made you confused...
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    116
    Rep Power
    9
    Which part you do not know?
    How to create trigger?
    How to send email from SQL server?
    How to create stored procedure?
    How to schedule stored procedure execute on server?

IMN logo majestic logo threadwatch logo seochat tools logo