SQL Server中限制Employees表DML操作的触发器与存储过程问题

我希望阻止用户在一周的特定时段对SQL Server中的Employees表执行DML命令。尝试了几种触发器实现方式，但都无法生效，仍然可以随时更新该表。

尝试过的代码 #

方式1：存储过程+触发器 #

CREATE OR ALTER PROCEDURE secure_dml
AS
BEGIN
    IF CONVERT(VARCHAR(8), GETDATE(), 8) NOT BETWEEN '08:00:00' AND '18:00:00'
       OR FORMAT(GETDATE(), 'ddd') IN ('Mon', 'Sun') 
    BEGIN
        RAISERROR ('You may only make changes during normal office hours', 1, 1)
    END
END;

CREATE OR ALTER TRIGGER secure_employees 
ON employees
INSTEAD OF INSERT, DELETE, UPDATE
AS
BEGIN
    EXEC secure_dml;
END;

方式2：逻辑直接放入触发器（RAISERROR） #

CREATE OR ALTER TRIGGER secure_employees 
ON employees
INSTEAD OF INSERT, DELETE, UPDATE
AS
BEGIN
    IF CONVERT(VARCHAR(8), GETDATE(), 8) NOT BETWEEN '08:00:00' AND '18:00:00'
        OR FORMAT(GETDATE(), 'ddd') IN ('Mon', 'Sun') 
    BEGIN
        RAISERROR ('You may only make changes during normal office hours', 1, 1);
    END
END;

方式3：使用THROW替代RAISERROR #

CREATE OR ALTER TRIGGER secure_employees 
ON employees
INSTEAD OF INSERT, DELETE, UPDATE
AS
BEGIN
    IF CONVERT(VARCHAR(8), GETDATE(), 8) NOT BETWEEN '08:00:00' AND '18:00:00'
        OR FORMAT(GETDATE(), 'ddd') IN ('Mon', 'Sun') 
        THROW 50005, 'You may only make changes during normal office hours', 1;
END;

问题根源 #

1. RAISERROR严重级别过低：你使用的RAISERROR严重级别为1，属于信息性提示，不会中断执行流程或回滚事务，仅输出消息，无法阻止后续操作。
2. 星期判断存在区域依赖：FORMAT(GETDATE(), 'ddd')的返回结果受SQL Server区域设置影响，若系统为中文环境，会返回中文星期缩写（如'周日'、'周一'），而非英文的'Mon'、'Sun'，导致条件判断完全失效。
3. INSTEAD OF触发器逻辑不完整：INSTEAD OF触发器会完全替代原DML操作，当条件允许修改时，必须手动在触发器中执行对应的INSERT/UPDATE/DELETE操作，否则原操作不会生效；但你当前代码仅在禁止时段抛出错误，允许时段无实际执行逻辑——结合你能随时更新的现象，核心问题是星期判断的区域适配错误。

修正后的解决方案 #

方案1：用DATEPART判断星期（无区域依赖） #

使用DATEPART(WEEKDAY, GETDATE())判断星期（SQL Server默认周日为1，周一为2），同时限制时间在08:00-18:00之外禁止操作：

CREATE OR ALTER TRIGGER secure_employees 
ON employees
INSTEAD OF INSERT, DELETE, UPDATE
AS
BEGIN
    -- 判断是否处于禁止时段
    IF (DATEPART(HOUR, GETDATE()) < 8 OR DATEPART(HOUR, GETDATE()) >= 18)
        OR DATEPART(WEEKDAY, GETDATE()) IN (1, 2)
    BEGIN
        -- 抛出严重级别错误，中断执行并回滚
        THROW 50005, 'You may only make changes during normal office hours', 1;
    END
    ELSE
    BEGIN
        -- 允许操作时，执行对应DML逻辑（需替换为实际列名和主键）
        IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
        BEGIN
            -- 处理UPDATE操作
            UPDATE e
            SET e.EmployeeName = i.EmployeeName,
                e.Department = i.Department
            FROM employees e
            JOIN inserted i ON e.EmployeeID = i.EmployeeID;
        END
        ELSE IF EXISTS (SELECT * FROM inserted)
        BEGIN
            -- 处理INSERT操作
            INSERT INTO employees (EmployeeName, Department)
            SELECT EmployeeName, Department FROM inserted;
        END
        ELSE IF EXISTS (SELECT * FROM deleted)
        BEGIN
            -- 处理DELETE操作
            DELETE e
            FROM employees e
            JOIN deleted d ON e.EmployeeID = d.EmployeeID;
        END
    END
END;

方案2：坚持用FORMAT则指定区域 #

若必须使用FORMAT，需指定区域参数确保返回英文星期缩写：

CREATE OR ALTER TRIGGER secure_employees 
ON employees
INSTEAD OF INSERT, DELETE, UPDATE
AS
BEGIN
    IF CONVERT(VARCHAR(8), GETDATE(), 8) NOT BETWEEN '08:00:00' AND '18:00:00'
        OR FORMAT(GETDATE(), 'ddd', 'en-US') IN ('Sun', 'Mon')
    BEGIN
        THROW 50005, 'You may only make changes during normal office hours', 1;
    END
    ELSE
    BEGIN
        -- 同上，执行对应DML逻辑（替换为实际列名和主键）
        IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
        BEGIN
            UPDATE e
            SET e.EmployeeName = i.EmployeeName,
                e.Department = i.Department
            FROM employees e
            JOIN inserted i ON e.EmployeeID = i.EmployeeID;
        END
        ELSE IF EXISTS (SELECT * FROM inserted)
        BEGIN
            INSERT INTO employees (EmployeeName, Department)
            SELECT EmployeeName, Department FROM inserted;
        END
        ELSE IF EXISTS (SELECT * FROM deleted)
        BEGIN
            DELETE e
            FROM employees e
            JOIN deleted d ON e.EmployeeID = d.EmployeeID;
        END
    END
END;

关键说明 #

• THROW的可靠性：THROW会自动终止执行并回滚事务，无需额外设置，比低级别RAISERROR更能有效阻止非法操作。
• INSTEAD OF触发器必须补全DML逻辑：因为该类型触发器会完全替代原操作，允许修改时必须手动编写对应的数据变更语句，确保正常业务流程不受影响。
• DATEPART的稳定性：避免了区域设置对星期判断的影响，适用于多环境部署场景。

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