Solved

sending multiple emails as a result of an update staement

Posted on 2008-10-08
3
441 Views
Last Modified: 2012-06-21
Hello

I'm trying to set up some code in a stored procedure that will send out an email for each row affected by an update statement.

For example, the update statement is:

Update table 1
set status = 'done'
where status = 'not done'

lets say 50 rows are affcted by that statement when its triggered

each row has a column called id. I want to get the id's of these 50 rows that were updated and send out 50 emails, one for each row affected with the id on each email.

I'm using msdb.dbo.sp_send_dbmail to handle the sending of the emails

any ideas?

thanks
0
Comment
Question by:pedley123
  • 2
3 Comments
 
LVL 5

Expert Comment

by:Cvijo123
Comment Utility
u can do it in trigger (at least that would be only solution for SQL 2000) for 2005 i cant tell if they implement some new feature for that.

so in your table u need to add trigger
u can put your code to insert all updated tables in your que table for sending emails
if u dont have any u should make one IMO
------------------------------------------------------------------------------
CREATE TRIGGER [TriggerName] ON [YourTableName]
AFTER UPDATE
AS
 
 
 insert into yourQueMailTable ( idTable )
 Select idTable from deleted
------------------------------------------------------------------------------
 
 that will insert all updated records into your table yourQueMailTable you have created.
 
 After that u could have job to send email's for every record in that table and then setting flag in that table to true if mail was sended or simple delete record from that table after u send it.
U can set job to execute every 5 min or any other time period
 
 putting your code to send mail inside trigger mighr slow down inserts but u can do that as well if u like.
 
0
 

Author Comment

by:pedley123
Comment Utility
ok, so the trigger adds the updated records to a separate table. i can set up an agent job to run at certain intervals but how do i then send 1 email for each record in that table, so if there are 50 records in the table i'd need 50 separate emails to go out with the id of the record on each email.

what would the code be to do that, assuming the table that has had the records added to it from the trigger is called QueMailTable?

thanks
0
 
LVL 5

Accepted Solution

by:
Cvijo123 earned 500 total points
Comment Utility
well one way is to use cursor to send mail's

something like:

declare @id int
 

declare cur cursor for

    Select

    id

    from QueMailTable
 

open cur

fetch next from cur into @id
 

while @@fetch_status = 0

begin

    -- u can select some data here what u will e-mail since u have now @id from your updated records

    -- like: Select @body = yourFieldTable from otherTable where idOtherTable = @id

    

    -- exec your SP to send mail .. i dont know what way you are sending mail thrue SQL server

    print 'Sending email for id: ' + cast(@id as varchar(10))

    exec spSendMailProcedure @from, @to, @body --... or somethin else your SP needs

    

    

    

  fetch next from cur into @id

end
 
 

close cur

deallocate cur

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now