SQl Server 2008 R2 - I have a trigger that's set up to send an email to a specific email address that is monitored by a third party system which sends out messages to certain groups of people based upon the subject of that message. The limitation to this is that only one group number can be in the subject per message. But I need to send the same message from the trigger to at least two groups meaning that two separate emails must be generated with two different subject lines. I'm thinking possibly a cursor in the trigger would accomplish this but I'm not sure how to write it. So in the code below I have @cat in which I need to send an email with the subject 'ED' EVERY time the trigger kicks off. I then need to send another email with the subject being set based on the condition of the case statement currently used to set the subject line. I've updated the code as follows and by removing the scalar variables have moved to a set based trigger(??) I've also created and inserted the information into an "email" table that will have a service set up to run every 10 sec and email all records that are set as 0 and then update the flag to 1.
New Code -
CREATE TABLE [dbo].[tb_BatchEmail] (
[BatchEmailID] [bit] NOT NULL DEFAULT 0
,[To] [varchar](50) NOT NULL DEFAULT 'someemail' --will never change
,[Body] [varchar](255) NULL
,[Subject] [varchar](20) NOT NULL
,[Profile] [varchar](50) NOT NULL DEFAULT 'Alert' --will never change
,[OrderID] [varchar] (25) NULL
,[OrderDateTime] [datetime] NULL
,[SentDateTime] [datetime] NULL
,CONSTRAINT msg_pk PRIMARY KEY CLUSTERED (BatchEmailID)
) ON [PRIMARY]
GO
ALTER TRIGGER [dbo].[VeOrders] ON [dbo].[Orders]
FOR INSERT
AS
SELECT @visitid = i.VisitID
,@priority = Priority
,@cat = Category
,@procedure = OrderedProcedureName
,@orderid = OrderID
,@orderdate = OrderDateTime
,@locationid = CurrentLocationID
,@roomid = CASE
WHEN RoomTreatmentID IS NULL
THEN 'No Room#'
ELSE RoomTreatmentID
END
FROM inserted i
INNER JOIN livedb.dbo.EdmPatients edp
ON edp.VisitID = i.VisitID
WHERE Priority = 'STAT'
AND Category IN ('CT', 'MRI', 'XRAY', 'US', 'NUC', 'ECHO')
AND CurrentLocationID = 'ED'
IF @cat IN ('CT', 'MRI', 'XRAY', 'US', 'NUC', 'ECHO')
AND @priority = 'STAT'
AND @locationid = 'ED'
BEGIN
DECLARE @msg VARCHAR(500)
DECLARE @subject VARCHAR(500)
SET @msg = @roomid + '-' + @procedure + '-' + @priority + '.' --+ 'Order DateTime/Number ' + @order + '/+ @locationid + ' ' + @orderid
SET @subject = CASE
WHEN @cat = 'US'
THEN 'Test Ultra Sound'
WHEN @cat = 'CT'
THEN 'Test C T'
WHEN @cat = 'XRAY'
THEN 'Test X-Ray'
END
INSERT INTO livedb.dbo.tb_BatchEmail (
Body
,[Subject]
,OrderID
,OrderDateTime
)
SELECT Body = @msg
,[Subject] = @subject
,OrderID = @orderid
,OrderDateTime = @orderdate
END
Old Code -
ALTER TRIGGER [dbo].[VeOrders] ON [dbo].[Orders]
FOR INSERT
AS
IF NOT EXISTS (
SELECT *
FROM dbo.EdmPatients
WHERE CurrentLocationID = 'ED'
)
RETURN
DECLARE @priority VARCHAR(50)
DECLARE @cat VARCHAR(50)
DECLARE @procedure VARCHAR(50)
DECLARE @orderid VARCHAR(50)
DECLARE @locationid VARCHAR(10)
DECLARE @roomid VARCHAR(10)
DECLARE @visitid VARCHAR(50)
SELECT @visitid = VisitID
,@priority = Priority
,@cat = Category
,@procedure = OrderedProcedureName
,@orderid = OrderID
,@locationid = CurrentLocationID
,@roomid = CASE
WHEN RoomTreatmentID IS NULL
THEN 'No Room#'
ELSE RoomTreatmentID
END
FROM inserted i
INNER JOIN dbo.EdmPatients edp
ON edp.VisitID = i.VisitID
WHERE Priority = 'STAT'
AND Category IN ('CT', 'MRI', 'XRAY', 'US', 'NUC', 'ECHO')
AND CurrentLocationID = 'ED'
IF @cat IN ('CT', 'MRI', 'XRAY', 'US', 'NUC', 'ECHO')
AND @priority = 'STAT'
AND @locationid = 'ED'
BEGIN
DECLARE @msg VARCHAR(500)
DECLARE @subject VARCHAR(500)
SET @msg = @roomid + '-' + @procedure + '-' + @priority + '.'
SET @subject = CASE
WHEN @cat = 'CT'
THEN '55194'
WHEN @cat = 'US'
THEN '59843'
WHEN @cat = 'XRAY'
THEN '70071'
END
EXEC msdb.dbo.sp_send_dbmail
@recipients = N'someemail'
,@body = @msg
,@subject = @subject
,@profile_name = 'Alerts'
END
I would separate out the concerns from creating the emails and sending them. Have a service whose only job is to send emails. Emails are abstract in that they all have a From, a To, a Body, and a Subject. Create a table (named BatchEmail for example) that holds all of those fields.
Then your trigger is simple. It just inserts into your BatchEmail table in a normal select statement.
insert into BatchEmail
(To, Body, Subject, Profile)
select N'someemail',
@msg,
CASE
WHEN @cat = 'CT'
THEN '55194'
WHEN @cat = 'US'
THEN '59843'
WHEN @cat = 'XRAY'
THEN '70071'
END,
'Alerts'
Your BatchEmail table should have some column or flag indicating when or if it was sent.
Your service (or even the Insert Trigger on the table, if you really love triggers) Could just select 1 row at a time that hasn't been processed.
while true
begin
select top 1
@BatchEmailID = batchEmailID,
@To = to,
@From = from,
@Body = body,
@Subject = subject,
@ProfileName = ProfileName
from BatchEmail
where processed = 0
if(@BatchEmailID is null)
break;
exec msdb.dbo.sp_send_dbmail @To, @Body, @Subject, @ProfileName
update BatchEmail
set processed = 1
where BatchEmailID = @BatchEmailID
end
If you still would rather not have a separate service and insist on doing all of this in the trigger, you could turn @BatchEmail into a table variable, still with a @Processed bit column and select 1 row at a time, updating @Processed after each row.