Trigger is not updating the rows-CodePudding

I am trying to create a SQL Server 2019 trigger where if someone tries to update, insert or delete after 17:00 (during weekdays) or on Saturdays/Sundays, they are not allowed to do so.

If for example I try to update/delete/insert during working hours (before 17:00) it appears that one row has been affected but if I check it then it appears that the row has not been updated (for actions after 17:00 or at weekends the restriction is working).

Can someone help me?

CREATE TRIGGER ALTAEMPLE3
ON EMPLE 
INSTEAD OF INSERT, UPDATE, DELETE
AS
    DECLARE @day varchar(50),
            @hour int
BEGIN
    SELECT @day = DATENAME(WEEKDAY, GETDATE())
    SELECT @hour = DATENAME(HOUR, GETDATE())

    IF (@hour > 17) OR @day IN ('Saturday', 'Sunday')
    BEGIN
        PRINT 'It is closed'
        ROLLBACK 
    END
END

CodePudding user response：

You seem to be using transactions, and do a ROLLBACK if it is in the wrong time frame. Instead of using INSTEAD OF (which means you never do the INSERT, UPDATE or DELETE) use AFTER, and then if it is the wrong time do a ROLLBACK as you do now.

CodePudding user response：

You should seriously consider moving this to your application software in stead of putting this in your database.
This means you are not able to correct data, move data to an archive, remove old data, and so on after 17:00 and in the weekend.

If you want it like that anyway, try a normal trigger with a throw

CREATE TRIGGER ALTAEMPLE3
ON EMPLE 
for INSERT, UPDATE, DELETE
AS
BEGIN
    set nocount on

    DECLARE @day varchar(50),
            @hour int
    SELECT @day = DATENAME(WEEKDAY, GETDATE())
    SELECT @hour = DATENAME(HOUR, GETDATE())

    IF (@hour > 17) OR @day IN ('Saturday', 'Sunday')
    BEGIN
        ;THROW 99001, 'It is closed', 1
    END
END

Ow yes, before I forget,
this does makes it possible to insert/update/delete on weekdays from midnight until 17:00
If you want this restriction on workhours, you should add a starthour also