Error-raising procedure and trigger in SQL Server-CodePudding

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

What I have been trying is to achieve this:

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 to be working for me. I still can update the table Employees at all times.

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.

CodePudding user response：

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;

CodePudding user response：

You don't need either a procedure or an INSTEAD OF trigger. You can just use an AFTER trigger

CREATE OR ALTER TRIGGER secure_employees ON employees
  AFTER INSERT, DELETE, UPDATE
AS

SET NOCOUNT ON;

IF CONVERT(time, GETDATE()) NOT BETWEEN CONVERT(time, '08:00:00') AND CONVERT(time, '18:00:00')
        OR (DATEPART(weekday, GETDATE())   @@DATEFIRST) % 7 IN (1, 2) 
BEGIN
    THROW 500001, 'You may only make changes during normal office hours', 1;
END;

You don't need to roll back the transaction, THROW will do that for you.

db<>fiddle

I suggest you find a different way of enforcing this kind of business restriction on employees, SQL is not really the place for it.