Solved

sending multiple emails as a result of an update staement

Posted on 2008-10-08
3
446 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22673830
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
ID: 22680564
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
ID: 22681015
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

688 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