替代嵌套游标：批量生成触发器及关联动作行的SQL实现方案

核心思路 #

抛弃游标逐行遍历的方式，通过批量生成唯一主键+临时存储关联关系的集合式操作，一次性完成两张表的插入，既保证主键唯一性，又能高效关联触发器与动作记录。

具体实现步骤 #

1. 批量生成触发器记录并存储关联主键

基于TableA中CustomerNumber=0815的模板数据，为目标客户（@CustomerNumber）生成新的触发器记录，同时计算唯一非自增主键Number，并将生成的记录暂存到表变量中，方便后续关联动作表。

DECLARE @CustomerNumber VARCHAR(10) = '15000';
DECLARE @MailAddresses NVARCHAR(MAX) = 'mail1@example.com;mail2@example.com';

-- 表变量：暂存刚生成的触发器记录（含主键Number）
DECLARE @NewTriggers TABLE (
    Number INT,
    CustomerNumber VARCHAR(10),
    EventType VARCHAR(50) -- 对应原表事件类型字段，按需调整
);

-- 计算当前触发器表最大主键，批量生成新主键并插入
WITH MaxTriggerNumber AS (
    SELECT ISNULL(MAX(Number), 0) AS MaxNum FROM BenachrichtigungsEreignisse
),
TemplateEvents AS (
    SELECT 
        EventType,
        Column1, Column2 -- 复制模板中的其他字段，按需调整
    FROM TableA 
    WHERE CustomerNumber = '0815'
),
NewTriggerData AS (
    SELECT
        mt.MaxNum + ROW_NUMBER() OVER (ORDER BY te.EventType) AS NewNumber,
        @CustomerNumber AS CustomerNumber,
        te.EventType,
        te.Column1, te.Column2
    FROM TemplateEvents te
    CROSS JOIN MaxTriggerNumber mt
)
INSERT INTO BenachrichtigungsEreignisse (Number, CustomerNumber, EventType, Column1, Column2)
OUTPUT inserted.Number, inserted.CustomerNumber, inserted.EventType INTO @NewTriggers
SELECT NewNumber, CustomerNumber, EventType, Column1, Column2
FROM NewTriggerData;

2. 拆分邮箱列表并批量插入动作记录

将输入的邮箱字符串拆分为独立行，再与暂存的触发器记录关联，批量插入动作表，自动匹配对应的triggernumber（即触发器的主键Number）。

-- 拆分邮箱字符串为单行数据
DECLARE @SplitMails TABLE (MailAddress NVARCHAR(255));
INSERT INTO @SplitMails (MailAddress)
SELECT value FROM STRING_SPLIT(@MailAddresses, ';') WHERE value <> '';

-- 批量插入动作表，生成唯一主键并关联触发器
INSERT INTO BenachrichtigungsReaktionen (Number, triggernumber, MailAddress)
SELECT
    -- 生成动作表唯一主键：基于当前最大主键自增
    (SELECT ISNULL(MAX(Number), 0) FROM BenachrichtigungsReaktionen) 
    + ROW_NUMBER() OVER (ORDER BY nt.Number, sm.MailAddress) AS NewActionNumber,
    nt.Number AS triggernumber,
    sm.MailAddress
FROM @NewTriggers nt
CROSS JOIN @SplitMails sm;

方案优势 #

• 完全替代游标（包括嵌套游标），集合式操作性能远高于逐行遍历
• 一次性计算所有新主键，若需更高并发安全性，可加事务或用SEQUENCE替代MAX(Number)+1
• 逻辑清晰，维护成本低

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