插入数据触发AddDoctorBookingSlots触发器时遇512错误的求助

这个错误的核心原因很明确：你的触发器代码只考虑了单行插入的场景，但SQL Server的INSERTED表会包含所有被插入的行。当你批量插入多条BookingDay记录时，那些直接用SELECT ... FROM INSERTED的子查询（比如给@starttime赋值的语句）就会返回多个值，这就违反了SQL的语法规则——当子查询用在赋值、比较操作里时，必须只能返回单个值。

修复方案：改用基于集合的方式处理批量插入 #

SQL是基于集合的语言，相比逐行处理的游标，用集合操作不仅更高效，也天然支持批量场景。下面是修改后的触发器代码：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[AddDoctorBookingSlots]
ON [dbo].[BookingDay]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON; -- 避免返回影响行数的消息干扰主操作

    -- 用CTE生成数字序列，用来生成时间间隔（这里生成0到1000的数字，可根据需求调整）
    WITH Numbers AS (
        SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS Num
        FROM sys.all_columns
    )
    INSERT INTO [dbo].[Booking] (
        [DoctorID], [Day], [Time], [Fees], [Valid], [PatientPhone], 
        [BookingDayID], [PatientName], [PatientEmail], [NoShow], 
        [PackageID], [IsFree], [IsEditable], [Shift], 
        [CancelledByDoctor], [CancelledByUser], [UserId], [UpdatedBy]
    )
    SELECT
        i.DoctorID,
        i.[Day],
        CONVERT(CHAR(5), DATEADD(minute, n.Num * i.WaitingTime, CAST(i.[Day] AS DATETIME) + CAST(i.DayFrom AS DATETIME)), 108) AS [Time],
        d.ExaminationFees,
        1 AS Valid,
        NULL AS PatientPhone,
        i.BookingDayID,
        NULL AS PatientName,
        NULL AS PatientEmail,
        0 AS NoShow,
        d.SubscribtionPackage AS PackageID,
        0 AS IsFree,
        0 AS IsEditable,
        i.[Shift],
        0 AS CancelledByDoctor,
        0 AS CancelledByUser,
        i.UserId,
        i.UpdatedBy
    FROM INSERTED i
    JOIN Numbers n ON 
        DATEADD(minute, n.Num * i.WaitingTime, CAST(i.[Day] AS DATETIME) + CAST(i.DayFrom AS DATETIME)) 
        <= CAST(i.[Day] AS DATETIME) + CAST(i.DayTo AS DATETIME)
    LEFT JOIN Doctor d ON d.DoctorID = i.DoctorID;
END
GO

代码说明： #

1. SET NOCOUNT ON：必须加上，避免触发器返回的影响行数消息干扰主插入操作。
2. 数字序列CTE：通过sys.all_columns生成足够多的数字（这里是1000个），用来计算每个时间槽的起始时间。如果你的DayTo - DayFrom间隔很大，可以调整TOP的数值。
3. 关联INSERTED表：对每一行插入的BookingDay记录，生成所有符合时间间隔的时间槽，然后批量插入到Booking表。
4. 避免子查询：直接通过JOIN关联Doctor表获取费用和套餐信息，不再使用容易出问题的嵌套子查询。

备选方案：用游标逐行处理（适合简单场景） #

如果你暂时不熟悉CTE，也可以用游标逐行遍历INSERTED表，这样能兼容原代码的逻辑，但性能不如集合操作：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[AddDoctorBookingSlots]
ON [dbo].[BookingDay]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @DoctorID INT, @Day DATE, @DayFrom TIME, @DayTo TIME, @WaitingTime INT;
    DECLARE @BookingDayID INT, @Shift VARCHAR(50), @UserId INT, @UpdatedBy INT;
    DECLARE @ExaminationFees DECIMAL(18,2), @SubscribtionPackage INT;
    DECLARE @starttime DATETIME, @endtime DATETIME;

    -- 声明游标遍历INSERTED表
    DECLARE BookingDayCursor CURSOR FOR
        SELECT 
            i.DoctorID, i.[Day], i.DayFrom, i.DayTo, i.WaitingTime,
            i.BookingDayID, i.[Shift], i.UserId, i.UpdatedBy,
            d.ExaminationFees, d.SubscribtionPackage
        FROM INSERTED i
        LEFT JOIN Doctor d ON d.DoctorID = i.DoctorID;

    OPEN BookingDayCursor;
    FETCH NEXT FROM BookingDayCursor INTO 
        @DoctorID, @Day, @DayFrom, @DayTo, @WaitingTime,
        @BookingDayID, @Shift, @UserId, @UpdatedBy,
        @ExaminationFees, @SubscribtionPackage;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @starttime = CAST(@Day AS DATETIME) + CAST(@DayFrom AS DATETIME);
        SET @endtime = CAST(@Day AS DATETIME) + CAST(@DayTo AS DATETIME);

        WHILE (@starttime <= @endtime)
        BEGIN
            INSERT INTO [dbo].[Booking] (
                [DoctorID], [Day], [Time], [Fees], [Valid], [PatientPhone], 
                [BookingDayID], [PatientName], [PatientEmail], [NoShow], 
                [PackageID], [IsFree], [IsEditable], [Shift], 
                [CancelledByDoctor], [CancelledByUser], [UserId], [UpdatedBy]
            )
            VALUES (
                @DoctorID, @Day, CONVERT(CHAR(5), @starttime, 108), @ExaminationFees,
                1, NULL, @BookingDayID, NULL, NULL, 0,
                @SubscribtionPackage, 0, 0, @Shift,
                0, 0, @UserId, @UpdatedBy
            );

            SET @starttime = DATEADD(minute, @WaitingTime, @starttime);
        END

        FETCH NEXT FROM BookingDayCursor INTO 
            @DoctorID, @Day, @DayFrom, @DayTo, @WaitingTime,
            @BookingDayID, @Shift, @UserId, @UpdatedBy,
            @ExaminationFees, @SubscribtionPackage;
    END

    CLOSE BookingDayCursor;
    DEALLOCATE BookingDayCursor;
END
GO

关键注意点： #

• 永远不要假设INSERTED表只有一行数据，触发器必须支持批量插入场景。
• 优先使用基于集合的操作，游标只适合逻辑复杂且数据量小的场景。

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