SQL，基于需要重新选择的插入值的foreach-loop-like INSERT，可能吗？

Question

现状

我有 4 个表，看起来像这样 具备以下条件

• DataMessages.SenderID 是一个 FK，以 DataUser.UserID 作为引用
• Data_UserGroup.UserID 是一个 FK，以 DataUser.UserID 作为引用
• DataMessageRecipient.RecipID 是一个 FK，以 DataUser.UserID 作为引用
• DataMessageRecipient.DestID 是一个 FK，以 DataUser.DestID 作为引用
• DataMessageRecipient.MssgID 是一个 FK，以 DataMessages.ID 作为引用
• DataMessages.ID 和 DataMessageRecipient.EntryID 是 IDENTITY() 列

这个 AFTER INSERT 触发 dbo.DataMessages 表

CREATE TRIGGER OnNewQueue ON dbo.DataMessages AFTER INSERT AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @inTabs Table
    (ID INT);
    /*Part that fetch the IDENTITY() column*/
    INSERT INTO @inTabs SELECT DataMessages.ID FROM DataMessages
        INNER JOIN inserted INS ON
            INS.SenderID = DataMessages.SenderID AND
            INS.MssgID = DataMessages.MssgID AND
            INS.CreatedAt = DataMessages.CreatedAt;
    /*
        DataMessages.CreatedAt (DateTime2), DataMessages.MssgID (int) are
        ommited from the picture, but those columns AND-ed will guaranteed to
        return unique rows because a SenderID cannot have duplicate MssgID
        even more on same CreatedAt time
    */
    INSERT INTO dbo.DataMessageRecipient (MssgID, RecipID, DestID)
        SELECT 
            inTbl.ID,
            DataUser.UserID,
            DataUser.DestID 
        FROM
            @inTabs AS inTbl,
            DataUser
        WHERE
            DataUser.UserID IN
            (
                SELECT Data_UserGroup.UserID 
                FROM Data_UserGroup
                WHERE Data_UserGroup.GroupID IN
                (
                    SELECT Data_UserGroup.GroupID 
                    FROM Data_UserGroup, inserted inr
                    WHERE Data_UserGroup.UserID = inr.SenderID
                )
            );
END

---

我要实现的目标

我想要实现的是，foreach inserted DataMessages，无论是单行插入还是多行插入，插入DataMessageRecipient(s) 如下条件:

1. RecipID 和 DestID 来自 DataUser 表，其中 DataMessages.SenderID 共享相同的 Data_UserGroup.GroupID(s)
2. MssgID 源自插入的 DataMessages.ID，RecipID 与插入的 SenderID 相关，基于 No .1 条件
3. (SQL 可选)插入的收件人不能包含自己的 SenderID 作为 RecipID

---

问题

INSERT INTO dbo.DataMessages (SenderID) 
    (10)

INSERT INTO dbo.DataMessages (SenderID) 
    (-83)

INSERT INTO dbo.DataMessages (SenderID) 
    (2213)

以上命令/查询的行为和结果符合预期，因为它们中的每一个都是不同的单独查询(满足要求，异常条件编号 3)，当多行 INSERT 尝试时(对于例如，像这样)

INSERT INTO dbo.DataMessages (SenderID) 
    (10), (83), (2213)

预期的结果是，无论单行还是多行插入，对于每个插入的 DataMessages，每个插入的都将有“它们自己的”DataMessageRecipient 条目，带有 RecipID 与每个插入的 SenderID 相关，基于 Data_UserGroup 中的条目，其中 RecipID 和 SenderID 共享相同的 群组ID(s)

当前多行插入的结果是，对于每个插入的 DataMessages，“收件人”被合并，因此所有新插入的 DataMessage 将共享相同的收件人

例子

考虑以下数据:

|     DataUser    |   |    Data_UserGroup  |   |   DataMessages  |
|:------:|:------:|:-:|:------:|:---------:|:-:|:----:|:--------:|
| UserID | DestID |   | UserID |  GroupID  |   |  ID  | SenderID |
|    1   |   -1   |   |    4   |     10    |   |  100 |     4    |
|    2   |   -2   |   |    5   |     10    |   |  101 |     1    |
|    3   |   -3   |   |    2   |     11    |   |  102 |     5    |
|    4   |   -4   |   |    1   |     12    |   |      |          |
|    5   |   -5   |   |    1   |     13    |   |      |          |
|    6   |   -6   |   |    3   |     13    |   |      |          |
|        |        |   |    5   |     13    |   |      |          |
|        |        |   |    6   |     13    |   |      |          |

不管 INSERT 的“方式”如何，它都应该产生这个 DataMessageRecipient

| EntryID | MssgID | RecipID | DestID |
|:-------:|:------:|:-------:|:------:|
|         |   100  |    4    |   -4   |
|         |   100  |    5    |   -5   |
|         |   101  |    1    |   -1   |
|         |   101  |    3    |   -3   |
|         |   101  |    5    |   -5   |
|         |   102  |    5    |   -5   |
|         |   102  |    4    |   -4   |
|         |   102  |    1    |   -1   |
|         |   102  |    3    |   -3   |

/*
 Note The RecipID is STILL included as the recipients.
 This is 'okay' for now, as the logic 
 outside SQL can filter this out
*/

但是，如果 DataMessage 插入多行 INSERT，它会产生如下结果:

| EntryID | MssgID | RecipID | DestID |
|:-------:|:------:|:-------:|:------:|
|         |   101  |    1    |   -1   |
|         |   101  |    3    |   -3   |
|         |   101  |    4    |   -4   |
|         |   101  |    5    |   -5   |
|         |   102  |    1    |   -1   |
|         |   102  |    3    |   -3   |
|         |   102  |    4    |   -4   |
|         |   102  |    5    |   -5   |
|         |   103  |    1    |   -1   |
|         |   103  |    3    |   -3   |
|         |   103  |    4    |   -4   |
|         |   103  |    5    |   -5   |

---

问题

我怎样才能达到预期的结果？这可能在 SQL 逻辑中做这样的事情吗？或者我应该提取插入的 DataMessages 结果并在后端逻辑中对它进行后处理，在那里我可以为每个插入的 ID 执行 for-each 循环？

谢谢

Answer 1

触发器可以访问插入的，拉取任何插入的id

create trigger theTriggerName 
    on dbo.DataMessages for update
as
insert into dbo.DataMessagesInserted (insertedID)
select ID from inserted
GO