Solved

sending multiple emails as a result of an update staement

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

21 Experts available now in Live!

Get 1:1 Help Now