SQL Server 2019中如何用TRY-CATCH与TRIGGER阻止违规数据插入

需求说明 #

在SQL Server 2019中，向Sales.Orders表插入数据时，若OrderDate列的值大于'2021-01-01'，需阻止该插入操作并返回提示信息'Error'，要求通过触发器（TRIGGER）结合TRY...CATCH实现。

示例数据 #

原代码问题分析 #

你提供的待修正触发器代码存在以下问题：

• 使用AFTER INSERT触发器时，数据已插入表中，无法真正阻止非法数据写入，需改用INSTEAD OF INSERT触发器
• INSERTED.orderdate<'2021-01-01'是无效语法，缺少完整的逻辑判断和错误抛出机制
• 拼写错误：NO ERROE应为NO ERROR

修正后的代码 #

CREATE TRIGGER date_check ON Sales.Orders INSTEAD OF INSERT  
AS
BEGIN
    BEGIN TRY
        -- 检查插入集合中是否存在OrderDate大于'2021-01-01'的记录
        IF EXISTS (SELECT 1 FROM INSERTED WHERE OrderDate > '2021-01-01')
        BEGIN
            -- 抛出自定义错误，触发CATCH块
            THROW 50001, 'Error', 1;
        END
        ELSE
        BEGIN
            -- 数据合法，执行插入操作
            INSERT INTO Sales.Orders (OrderID, OrderDate)
            SELECT OrderID, OrderDate FROM INSERTED;
            PRINT 'NO ERROR';
        END
    END TRY
    BEGIN CATCH
        -- 捕获错误并输出提示信息
        PRINT ERROR_MESSAGE();
        -- 回滚未提交事务（若存在）
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
    END CATCH
END
GO

代码说明 #

• INSTEAD OF INSERT触发器会替代默认的插入逻辑，先完成合法性检查再决定是否写入数据
• 通过EXISTS判断插入的数据集是否包含非法日期，若存在则用THROW抛出自定义错误（错误号50001属于SQL Server允许的用户自定义错误范围）
• 只有合法数据会被插入表中，并输出NO ERROR提示
• CATCH块捕获错误后输出Error，同时处理事务回滚，避免残留未提交的事务

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