Post reply

Sending an SMTP email when row(s) change(s)

  • March 2, 2004 at 10:12 am

    #107747

    I want to generate an SMTP email to a user when the password column in a table changes.

    The passwords are being created by a random generator either singly or as a batch.

    The SMTP mail is being created using a procedure master.dbo.xp_smtp_sendmail

    In the email I want to include the password and some other information from the row being updated.

    I am wondering what is the best approach?

    A Trigger based on a column update using 'inserted' table?

    A cursor in a procedure?

    Or?

  • March 2, 2004 at 10:55 am

    #496717

    You are going to be emailling the user their new password, or just a single user?

    Is the batch a SQL sp or something external?

     

    I would use an sp to change the password and use the self same sp to email the new password out.


    Shamless self promotion - read my blog http://sirsql.net

  • March 2, 2004 at 11:31 am

    #496724

    It would be simplest to send the mail from within the same procedure that updates the password, however, that means notifications wouldn't be sent if the table were updated directly.

    A trigger would solve that problem.  In this case it would be pretty simple, as a matter of fact I have done exactly this on one of my tables, although, not for a password!

    If you don't want to deal with the trigger, another way I've accomplished this is by adding 2 fields to my table.  A last update date and a notification sent field.  You can have a job that runs every so often to check for updated rows that haven't been notified and send mail.  If the mail is sent successfully, update the notified column.


    Regards,
    Steve

    Meddle not in the affairs of dragons, for you are crunchy and taste good with ketchup.

  • March 3, 2004 at 5:03 am

    #496863

    Sometimes I would want to update only one password, other times I would want to update all passwords.

    In either case I would generate a random password.

    I guess that I would always want to do this from a procedure as the passwords being generated are as per the UDF on this site (8 characters mixed lower, upper and numbers)

    Think I will try doing this using a cursor in a procedure as this will be more transparent that a trigger.

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

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