Error-Raising Procedure and Trigger in MS SQL

July 25, 2022

I am trying to prevent a user from running DML commands on employees in MS SQL Server in certain period of the the week.

What I have been trying is:

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;

but it seems not working for me. I still can update the table employees.

Am I missing something, what should I change in the code?

UPDATE:

Then as was suggested i put the logic inside the trigger itself as follows:

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;

But it does not prevent me from updating of the table anyway.

Then I tried as follows:

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;

but as you may guess again without success.

>Solution :

As I mentioned in the comments, I suggest you use THROW here and put the logic in the TRIGGER:

CREATE OR ALTER TRIGGER secure_employees ON employees
AFTER INSERT, DELETE, UPDATE
AS BEGIN
    IF CONVERT(time(0),GETDATE()) < '08:00:00'
    OR CONVERT(time(0),GETDATE()) > '18:00:00'
    OR (DATEPART(WEEKDAY,GETDATE()) + @@DATEFIRST) % 7 IN (1,2) --This is language safe
       THROW 50001, N'You may only make changes during normal office hours.',16;
END;