SQL插入Marks表的分数范围校验触发器失效，需拦截超范围分数入库

原触发器存在的核心问题 #

• 核心逻辑判断错误：原代码写的非法分数判断条件是IF (((@marks) < 0) AND ((@marks) > 100))，用逻辑与连接两个判断项，数学上不存在同时满足“小于0”和“大于100”的数值，整个判断表达式永远返回假，回滚拦截的逻辑根本不会被触发。
• 冗余操作带来额外漏判风险：原代码在判断分支外额外写了DELETE FROM Marks WHERE TestID = @tID AND StudentID = @stID的删除逻辑，完全没必要——如果真的要拦截非法数据，直接回滚整个插入事务即可，根本不需要手动删数据；而且这种删除逻辑因为基于单变量的主键匹配，遇到批量插入场景时，只会识别inserted结果集最后一行的主键，其他非法插入的记录因为匹配不到删除条件，会直接留在数据库中。
• 不兼容批量写入场景：原代码直接把inserted系统表的内容赋值给单值变量，哪怕修正了判断逻辑，只要一次插入多条记录，就只会对最后一条记录的分数做校验，其余记录完全绕过检查。
• 表结构的潜在隐患：IssuedDate字段使用VARCHAR类型存储日期，后续会出现隐式转换、日期格式不统一的问题，建议后续替换为原生DATE类型。

修正后的可正常拦截的触发器实现 #

核心思路是直接基于inserted系统表做集合判断，不依赖单变量承接数据，一旦发现存在分数不在0-100范围的记录，立刻回滚整个插入事务，同时抛出明确的错误提示：

CREATE TRIGGER checkRange
ON Marks
FOR INSERT
AS
BEGIN
    -- 校验所有新插入记录的分数范围
    IF EXISTS (
        SELECT 1 
        FROM inserted 
        WHERE Marks < 0 OR Marks > 100
    )
    BEGIN
        -- 回滚整个插入操作，拒绝非法数据入库
        ROLLBACK TRANSACTION
        -- 抛出明确错误，方便定位问题
        RAISERROR('合法分数范围为0-100，当前操作因存在超范围分数已被终止', 16, 1)
        RETURN
    END
END

使用之前的测试语句INSERT INTO Marks VALUES ('T02','ST00000001','F',110,'2019-08-24')执行时，触发器会检测到110超出合法范围，直接回滚操作，非法数据不会写入数据库，同时返回对应错误提示。

更优的实现方案 #

这类单字段范围校验完全不需要依赖触发器，直接在表定义中添加CHECK约束即可，作为数据库引擎层面的拦截规则，性能更高、逻辑更简洁，也不存在触发器的逻辑漏洞：

-- 建表时添加约束示例
CREATE TABLE Marks
(
    TestID VARCHAR(10),
    StudentID VARCHAR(10),
    Grade CHAR(1),
    Marks FLOAT,
    IssuedDate DATE,
    PRIMARY KEY (TestID, StudentID),
    -- 分数范围校验约束
    CONSTRAINT chk_marks_valid CHECK (Marks >= 0 AND Marks <= 100),
    CONSTRAINT FK66 FOREIGN KEY(TestID) REFERENCES Test(TestID),
    CONSTRAINT FK77 FOREIGN KEY(StudentID) REFERENCES Student(StudentID)
)

注意：如果表已经创建完成，不需要重建表，直接执行以下语句即可添加约束：

ALTER TABLE Marks ADD CONSTRAINT chk_marks_valid CHECK (Marks >=0 AND Marks <=100)

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