批量插入DataMessages时SQL触发器生成收件人不符合预期的解决方法

Let's break down why your trigger is misbehaving during bulk inserts, then fix it with proper SQL logic (no need to pull data to your backend for loops—we can handle this entirely in the database).

The Root Cause #

Your current trigger has a critical flaw when dealing with multiple inserted records:

• When you bulk insert, the subquery SELECT Data_UserGroup.GroupID FROM Data_UserGroup, inserted inr WHERE Data_UserGroup.UserID = inr.SenderID returns all groups associated with every inserted SenderID, not just the groups for each individual message's SenderID.
• Then you cross-join all these groups' users with all newly inserted messages, which is why every message gets every user from every inserted Sender's groups (the "shared recipients" problem you're seeing).

Plus, your initial step of populating @inTabs is unnecessary—you can directly use the inserted table (which holds all newly added records) to link each message to its correct recipients.

The Fixed Trigger #

Here's the revised trigger that handles both single and bulk inserts correctly, including the optional exclusion of the sender themselves:

CREATE TRIGGER OnNewQueue ON dbo.DataMessages 
AFTER INSERT 
AS 
BEGIN 
    SET NOCOUNT ON;

    -- Insert recipients for each new message, linked to the sender's groups
    INSERT INTO dbo.DataMessageRecipient (MssgID, RecipID, DestID)
    SELECT 
        ins.ID AS MssgID,
        du.UserID AS RecipID,
        du.DestID AS DestID
    FROM inserted ins
    -- Get all groups that the message's sender belongs to
    INNER JOIN Data_UserGroup sender_groups 
        ON sender_groups.UserID = ins.SenderID
    -- Get all users in those groups
    INNER JOIN Data_UserGroup group_users 
        ON group_users.GroupID = sender_groups.GroupID
    -- Get the user's DestID from DataUser
    INNER JOIN DataUser du 
        ON du.UserID = group_users.UserID
    -- Optional: Exclude the sender themselves from recipients
    WHERE du.UserID != ins.SenderID;
END

How This Works #

Let's walk through the logic step by step for each inserted message:

1. Start with the inserted table, which has every new message (including its ID and SenderID).
2. Join to Data_UserGroup to get all groups that the message's sender is part of.
3. Join back to Data_UserGroup again to get every user who is in any of those groups.
4. Join to DataUser to fetch each recipient's UserID and DestID.
5. The optional WHERE clause filters out the sender from their own message's recipient list (remove this line if you want the sender to receive their own message).

Testing with Your Sample Data #

• For the message with SenderID=4 (groups: 10), this trigger will insert recipients 4 and 5 (or just 5 if you keep the exclusion clause).
• For the message with SenderID=1 (groups:12,13), it will insert recipients 1,3,5,6 (or 3,5,6 with exclusion).
• For the message with SenderID=5 (groups:10,13), it will insert recipients 4,5,1,3,6 (or 4,1,3,6 with exclusion).

This exactly matches your expected results, whether you insert one message or 100 at a time.

内容的提问来源于stack exchange，提问作者Tommy Aria Pradana