Solved

sending multiple emails as a result of an update staement

Posted on 2008-10-08
3
444 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
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

829 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