sql-serverstored-procedurestriggersdml

Error-raising procedure and trigger in SQL Server


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.


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;