• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 450
  • Last Modified:

sending multiple emails as a result of an update staement

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
pedley123
Asked:
pedley123
  • 2
1 Solution
 
Cvijo123Commented:
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
 
pedley123Author Commented:
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
 
Cvijo123Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now