周日预约插入限制触发器失效排查：@dayName疑似为空问题

Let’s break down why your trigger isn’t working as expected and get it sorted out.

The Core Issues in Your Original Code #

Your trigger has two critical problems causing it to fail:

1. Single variable can’t handle multiple rows: When you run SELECT @dayName = AppDate FROM inserted;, if multiple rows are being inserted at once, @dayName will only store the value from the last row. That means if one row is a Sunday and another isn’t, the trigger might incorrectly let all rows slip through (or block all of them).
2. Null handling gaps: If AppDate is NULL, DATENAME(DW, @dayName) returns NULL, which doesn’t equal 'Sunday'—so the trigger will insert those null-date rows even though that’s not part of your intended logic.

Fixed Trigger Option 1: Block All Inserts If Any Sunday Is Present #

This version will throw an error and stop all inserts if even one row is for a Sunday:

CREATE TRIGGER no_appointments ON Appointments INSTEAD OF INSERT AS 
BEGIN
    SET NOCOUNT ON; -- Prevents extra row count messages from being sent to the client
    
    -- Check if any inserted row falls on a Sunday
    IF EXISTS (SELECT 1 FROM inserted WHERE DATENAME(DW, AppDate) = 'Sunday')
    BEGIN
        RAISERROR ('No appointments on Sunday are allowed!', 10, 1);
        RETURN; -- Exit without inserting any rows
    END
    
    -- Insert only valid (non-Sunday) appointments
    INSERT INTO Appointments
    SELECT * FROM inserted;
END;

Fixed Trigger Option 2: Insert Valid Rows, Block Only Sundays #

If you want to allow non-Sunday rows to be inserted even if some Sunday rows are included (and just warn about the blocked Sundays), use this version:

CREATE TRIGGER no_appointments ON Appointments INSTEAD OF INSERT AS 
BEGIN
    SET NOCOUNT ON;
    
    -- Throw an error if any Sunday rows are present
    IF EXISTS (SELECT 1 FROM inserted WHERE DATENAME(DW, AppDate) = 'Sunday')
    BEGIN
        RAISERROR ('No appointments on Sunday are inserted!', 10, 1);
    END
    
    -- Insert all rows that aren't Sundays
    INSERT INTO Appointments
    SELECT * FROM inserted
    WHERE DATENAME(DW, AppDate) <> 'Sunday';
END;

Bonus: Handle Null AppDates #

If you also want to block rows where AppDate is NULL, add an extra condition to the WHERE clause:

WHERE DATENAME(DW, AppDate) <> 'Sunday' AND AppDate IS NOT NULL

The key fix here is moving away from a single variable and directly querying the inserted table to handle all rows at once—this ensures every appointment is checked correctly, no matter how many are being inserted.

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