Professional Web Applications Themes

emailing from trigger - Microsoft SQL / MS SQL Server

Does anybody have a suggestion for the best way to send email from a trigger? Is it even possible? I'd like to send a notification everytime a new entry goes into a table. Thank you....

  1. #1

    Default emailing from trigger

    Does anybody have a suggestion for the best way to send
    email from a trigger? Is it even possible? I'd like to
    send a notification everytime a new entry goes into a
    table. Thank you.
    Andrea Worley Guest

  2. #2

    Default Re: emailing from trigger

    Andrea,

    Yes..its possible. I dont know the best way since I have not done it.To send
    emails, you can use in-built SQL mail (master..xp_sendmail ) or :

    How to send email from sqlserver without using sqlmail.
    [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;Q312839[/url]

    [url]http://sqldev.net/xp/xpsmtp.htm[/url]
    It supports HTML and SMTP email


    Heres a untested code for a insert trigger using xp_sendmail

    CREATE TRIGGER trgSendEmail ON <tablename>
    FOR INSERT
    AS
    EXEC master..xp_sendmail 'k_ragehotmail.com', 'New rows added!'


    If you dont have SQL mail set up, refer:

    INF: How to Configure SQL Mail
    [url]http://support.microsoft.com/support/kb/articles/Q263/5/56.ASP[/url]


    --
    Dinesh.
    SQL Server FAQ at
    [url]http://www.tkdinesh.com[/url]

    "Andrea Worley" <k_ragehotmail.com> wrote in message
    news:9afa01c3463c$ce2a3300$a401280aphx.gbl...
    > Does anybody have a suggestion for the best way to send
    > email from a trigger? Is it even possible? I'd like to
    > send a notification everytime a new entry goes into a
    > table. Thank you.

    Dinesh.T.K Guest

  3. #3

    Default emailing from trigger

    Andrea,

    How about something like... This happens to be written as
    a stored procedure, but with a little effort, you could
    select from the INSERTED table that is created
    automatically by the insert statement for triggers, rather
    than the myTable and grab all of the values you needed
    from the INSERTED table, and you would not need to pass in
    any parameters.


    CREATE procedure sp_mailWelcomeLetter
    login nvarchar(128), password nvarchar(56)
    as



    declare strrecipients nvarchar(256),strsubject nvarchar
    (256),strmessage nvarchar(1024), newline nvarchar(25)
    declare companyName nvarchar(128),firstname nvarchar
    (56), lastname nvarchar(56)


    --Check to make sure login exists in the system...
    if (select count(*) from myTable where systemid=login) <>1
    begin
    print "No records were found for this login. No
    email was sent."
    return(99)
    end
    else
    begin
    --If exists, then initialize additional
    values for email...
    SELECT
    strrecipients=email,firstname=firstname,
    lastname=lastname FROM myTable
    WHERE systemid=login


    end
    set companyName='My Company Name'


    set newline=char(10) + char(13)

    set strsubject='Welcome to ' + companyName

    set strmessage='Dear user: ' + firstname + ' ' +
    lastname + newline + newline +
    'Welcome to ' + companyName +'. We hope you will
    take a few minutes to logon and to peruse the courses
    in our new catalog. Your logon credentials are given
    below. Please, make sure to change your password as soon
    as you log in by going to the "Personal Information"
    button in the menu and remember to keep your password
    safe.'
    + newline + newline +



    'Your Login is : ' + login + newline +
    'Your password is : ' + password + newline +
    newline +

    'Thank you,' + newline + newline +

    'The ' + companyName + ' Team'
    --end



    >-----Original Message-----
    >Does anybody have a suggestion for the best way to send
    >email from a trigger? Is it even possible? I'd like to
    >send a notification everytime a new entry goes into a
    >table. Thank you.
    >.
    >
    Robert Taylor Guest

  4. #4

    Default emailing from trigger

    I used this on a table that was insert into upon delete in
    a different table, might have to play with it a bit


    ALTER TRIGGER [email_alert] ON audit_tasks
    FOR insert
    AS
    declare wo_num varchar(30) , deleted_user varchar
    (255),emails varchar(255) , messages varchar(100),
    subjecttext varchar(100)
    declare mail_alert CURSOR
    set mail_alert = CURSOR LOCAL SCROLL FOR
    select wo_num , deleted_user from
    trackit..audit_tasks WHERE STATUS = '0'
    open mail_alert
    fetch next from mail_alert into wo_num , deleted_user
    while fetch_status = 0
    begin
    set messages = 'The Trackit wo number ' +'('+
    wo_num + ')'+' has been deleted'
    set subjecttext = 'A Work order in trackit was
    deleted'
    set emails = 'joesmoehotmail.com'
    + ';' + 'joebosshotmail.com'
    exec master..xp_sendmail recipients = emails ,
    message = messages,subject = subjecttext
    fetch next from mail_alert into wo_num , deleted_user
    end
    close mail_alert
    deallocate mail_alert
    UPDATE AUDIT_TASKS SET STATUS = '1'

    HTH

    Ray Higdon MCSE, MCDBA, CCNA

    >-----Original Message-----
    >Does anybody have a suggestion for the best way to send
    >email from a trigger? Is it even possible? I'd like to
    >send a notification everytime a new entry goes into a
    >table. Thank you.
    >.
    >
    Ray Higdon Guest

  5. #5

    Default emailing from trigger

    I use xp_sendmail. To use it, you must have the SQL Server
    Service (MSSQLERVER) running under the security context of
    a domain account (local system won't work, nor will an
    account on the local machine, unless it's an NT DC). A one
    time step is to log in to the SQL Server machine as this
    account and create an Outlook profile. You don't have to
    log in as that account again.

    HTH
    Vern
    >-----Original Message-----
    >Does anybody have a suggestion for the best way to send
    >email from a trigger? Is it even possible? I'd like to
    >send a notification everytime a new entry goes into a
    >table. Thank you.
    >.
    >
    Vern Rabe Guest

Similar Threads

  1. Bulk eMailing
    By Whizzzper in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: December 4th, 09:23 AM
  2. Need help in emailing ad to friend
    By kkash in forum Macromedia Flash Ad Development
    Replies: 1
    Last Post: January 26th, 12:07 PM
  3. FDF emailing
    By Linda_Ringie@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 1
    Last Post: April 14th, 06:56 PM
  4. 550 error when emailing
    By Al Kolff in forum PHP Development
    Replies: 0
    Last Post: August 28th, 10:51 PM
  5. Emailing Information
    By goingpostal101 webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 21st, 10:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139