CREATE TRIGGER [dbo].[newClaim]
ON [dbo].[Claims]
AFTER INSERT
AS

DECLARE @claimNum int, @location int

SELECT @claimNum = claimID FROM inserted
SELECT @location = location FROM inserted

DECLARE @message varchar(500), @queryToRun varchar(300)

SET @message = 'A new claim has been filed! Please follow up on claim ' + CONVERT(VARCHAR(10), @claimNum) + ' and set up an appointment.  ' +
		'The damage will need to be inspected and photographs of the 
damage need to be taken and logged.  ' +
		'The details of the claim are attached. '

DECLARE @recList varchar(max)
SELECT @recList = COALESCE(@recList + ', ', '') + email
FROM MailGroup
WHERE locationID = @location


SET @queryToRun = 'SELECT B.claimID, dateOpened, firstName, lastName FROM Claims.dbo.Customers A 
INNER JOIN Claims.dbo.Claims B ON A.customerID = B.customerID WHERE B.claimID = ' + convert(varchar(10), @claimNum)


-- Use this area to SEND the email.
EXEC msdb.dbo.sp_send_dbmail
@profile_name='TestMail',
@recipients=@recList,
@subject='Test Claim E-mail',
@query=@queryToRun,
@attach_query_result_as_file='1',
@query_attachment_filename='Claim Details.txt',
@body=@message

DECLARE @claimNum int, @location int

SELECT @claimNum = claimID FROM inserted
SELECT @location = location FROM inserted

DECLARE @message varchar(500), @queryToRun varchar(300)

SET @message = 'A new claim has been filed! Please follow up on claim ' + CONVERT(VARCHAR(10), @claimNum) + ' and set up an appointment.  ' +
		'The damage will need to be inspected and photographs of the 
damage need to be taken and logged.  ' +
		'The details of the claim are attached. '

DECLARE @recList varchar(max)
SELECT @recList = COALESCE(@recList + ', ', '') + email
FROM MailGroup
WHERE locationID = @location


SET @queryToRun = 'SELECT B.claimID, dateOpened, firstName, lastName FROM Claims.dbo.Customers A 
INNER JOIN Claims.dbo.Claims B ON A.customerID = B.customerID 
WHERE B.claimID = ' + convert(varchar(10), @claimNum)


-- Use this area to SEND the email.
EXEC msdb.dbo.sp_send_dbmail
@profile_name='TestMail',
@recipients=@recList,
@subject='Test Claim E-mail',
@query=@queryToRun,
@attach_query_result_as_file='1',
@query_attachment_filename='Claim Details.txt',
@body=@message

quote:

---

Originally posted by KrafDinner

Also, one other question...

What would be the best practice for making sure each one gets e-mailed ? They will have to be sent as separate e-mails because each one could potentially have a different list of recipients.

I realize a cursor would be an option to step through each row and send out the e-mail. Is there something else that would be a better practice?

Example: The mail queue table may simply contain a claimID and status. (There are different types of e-mails depending on the status)
If I have
23, 6
38, 1
39, 1
30, 4

These all need to be e-mailed to separate mail groups because they happened in separate locations and claims need to be taken up with the correct people. I have a table containing addresses based on location to query to build the recipient list. None of that is an issue, I'm just wondering if there is a better way to address this than running a cursor and stepping through to each and sending the mail.

Thanks for the information so far :)

---

SELECT @claimNum = claimID FROM inserted
IF @@ROWCOUNT <> 1 RAISERROR(...