SQL Server触发器改造：实现多邮箱地址批量插入邮件队列

原触发器仅能从LOCATION表获取单个邮箱地址，在订单进度码（prgcode）更新为'INVOICED'时向EmailQueue插入单条记录。现需改造为从LOCATION_CONTACTS表获取关联的多个邮箱地址，每个地址对应插入一条队列记录，适配动态数量的收件人需求。

改造后的触发器代码 #

IF UPDATE(prgcode)
BEGIN
    -- 提前获取发件人地址，避免重复查询表
    DECLARE @FromEmail VARCHAR(255) = (SELECT scffromaddress FROM dbo.SchedulerConfig);

    INSERT INTO dbo.EmailQueue (emlLogged,
                                emlTo,
                                emlFrom,
                                emlSubject,
                                emlBody,
                                emlReady_bool,
                                emlContentType)
    SELECT GETDATE(),
           -- 优先使用联系人邮箱，无有效邮箱时 fallback 到发件人地址
           ISNULL(cc.email_address, @FromEmail),
           @FromEmail,
           'Update on your order reference',
           'Some text in the body here',
           'T',
           'text/plain'
    FROM inserted i
         -- 关联LOCATION_CONTACTS表，沿用原LOCATION表的关联字段匹配订单联系人
         INNER JOIN dbo.LOCATION_CONTACTS cc 
             ON i.locLevel1 = cc.locLevel1
             AND i.locLevel2 = cc.locLevel2
             AND i.locLevel3 = cc.locLevel3
             AND i.locLevel4 = cc.locLevel4
             AND i.rgnCode = cc.rgnCode
    WHERE i.prgCode = 'INVOICED';
    
    SET NOCOUNT OFF;
END;

关键修改说明 #

• 关联表替换：将原与LOCATION表的关联改为与LOCATION_CONTACTS表关联，保持相同的关联字段（locLevel1至locLevel4、rgnCode），确保获取当前订单对应的所有联系人邮箱。
• 动态生成队列记录：由于LOCATION_CONTACTS可能包含多条联系人记录，SELECT语句会自动返回对应行数的结果，最终向EmailQueue插入与邮箱数量一致的记录，适配任意数量的收件人。
• 性能优化：提前用变量存储发件人地址，避免多次查询SchedulerConfig表，提升触发器执行效率。
• 空值兼容：保留原逻辑，当LOCATION_CONTACTS中无有效邮箱时，使用发件人地址作为备选，避免插入空收件人记录。

内容的提问来源于stack exchange，提问作者user1022772