sql server语言 - 在触发器中使用游标来更改db_sendmail的主题

Question

SQl Server 2008 R2—我设置了一个触发器，用于向特定的电子邮件地址发送电子邮件，该电子邮件地址由第三方系统监控，该系统根据该消息的主题向特定的人群发送消息。这样做的限制是，每条消息的主题中只能有一个组号。但是我需要从触发器向至少两个组发送相同的消息，这意味着必须用两个不同的主题行生成两个单独的电子邮件。我想可能是一个光标在触发器会完成这一点，但我不知道如何写它。所以在下面的代码中，我有@cat，我需要在每次触发器启动时发送一封主题为"ED"的电子邮件。然后，我需要发送另一封电子邮件，根据当前用于设置主题行的case语句的条件设置主题。我已经更新了代码如下，并通过删除标量变量已经移动到一个基于触发器的集合(?? ?)我还创建并将信息插入到"email"表中，该表将有一个设置为每10秒运行一次的服务，并通过电子邮件发送设置为0的所有记录，然后将标志更新为1。

新代码-

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

旧代码-

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

Answer 1

我会将关注点从创建和发送电子邮件中分离出来。有一个服务，它的唯一工作是发送电子邮件。电子邮件是抽象的，因为它们都有一个发件人，一个收件人，一个正文和一个主题。创建一个包含所有这些字段的表(例如名为BatchEmail)。

那么你的触发器很简单。它只是在一个普通的选择语句中插入BatchEmail表。

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'

你的BatchEmail表应该有一些列或标志来指示何时或是否发送。

你的服务(或者甚至是表上的Insert Trigger，如果你真的喜欢触发器的话)一次只能选择一个没有被处理的行。

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

如果您仍然不希望有一个单独的服务，并坚持在触发器中执行所有这些操作，您可以将@BatchEmail转换为一个表变量，仍然使用@Processed位列并每次选择1行，在每行之后更新@Processed。