如何实现禁止插入/更新日期早于2022-01-01的记录？触发器问题求助

需要实现以下三条规则：

• 禁止更新现有日期早于2022-01-01的记录
• 禁止更新时设置新日期早于2022-01-01
• 禁止插入日期早于2022-01-01的记录

简言之：不允许插入或更新任何导致日期字段早于2022-01-01的操作。

原尝试方案（存在问题） #

使用游标触发器实现批量操作支持，但出现了顺序依赖问题：

CREATE TRIGGER TriggerDate
ON tb
AFTER INSERT,UPDATE 
AS
BEGIN
    DECLARE @IVDate Date;

    DECLARE my_Cursor CURSOR FOR 
         SELECT InvoiceDate 
         FROM INSERTED; 

    OPEN my_Cursor; 
     
    FETCH NEXT FROM my_Cursor INTO @IVDate;
        
    WHILE @@FETCH_STATUS = 0 
    BEGIN
        FETCH NEXT FROM my_Cursor INTO @IVDate;

        IF @IVDate < '2022-01-01'
           ROLLBACK TRANSACTION
    END

    CLOSE my_Cursor; 
    DEALLOCATE my_Cursor;
END

遇到的问题 #

• 当先插入合法记录再插入非法记录时，仅非法记录被阻止，符合预期：

  insert into tb values (1, '2022-05-02','A');
  insert into tb values (2, '2021-08-06','B');
  

• 但先插入非法记录再插入合法记录时，合法记录也会被回滚，无法插入：

  insert into tb values (2, '2021-08-06','B');
  insert into tb values (1, '2022-05-02','A');   -- 该记录合法但仍无法插入
  

• 带OR条件的UPDATE语句也存在同样问题。

正确解决方案（基于集合操作，无需游标） #

SQL Server触发器的INSERTED和DELETED表是集合对象，直接通过集合判断是否存在违规记录即可，无需遍历游标。同时要区分INSERT和UPDATE的不同场景：

CREATE TRIGGER TriggerDate
ON tb
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 检查插入的记录是否有日期早于2022-01-01
    IF EXISTS (SELECT 1 FROM INSERTED WHERE InvoiceDate < '2022-01-01')
    BEGIN
        RAISERROR('禁止插入日期早于2022-01-01的记录', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END

    -- 检查更新操作：原记录日期早于2022-01-01 或 新日期早于2022-01-01
    IF EXISTS (
        SELECT 1 
        FROM DELETED d
        JOIN INSERTED i ON d.PrimaryKeyColumn = i.PrimaryKeyColumn -- 替换为你的主键列
        WHERE d.InvoiceDate < '2022-01-01' OR i.InvoiceDate < '2022-01-01'
    )
    BEGIN
        RAISERROR('禁止更新日期早于2022-01-01的记录，或设置新日期早于2022-01-01', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END

说明 #

1. SET NOCOUNT ON：避免返回触发器执行过程中的计数信息，防止干扰调用方。
2. INSERT场景检查：直接判断INSERTED表中是否存在违规日期，有则抛出错误并回滚。
3. UPDATE场景检查：通过DELETED（原记录）和INSERTED（新记录）的主键关联，检查两种违规情况：
   • 原记录日期早于2022-01-01（禁止更新这类旧记录）
   • 新设置的日期早于2022-01-01（禁止将记录更新为违规日期）
4. RAISERROR：抛出明确的错误信息，方便调用方排查问题，错误级别16会终止当前操作。

这种基于集合的方式避免了游标遍历的顺序问题，同时高效支持批量操作，所有违规记录都会被一次性检测并阻止。

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