Triggers INSTEAD OF Update, How to filter by date

Question

Hello, First of all thank you for all your time in the community.

Come on!

I will throw the information and then try, in the best possible way to explain the problem.

I have 3 tables:

Person

List

Stopover

CREATE TABLE PESSOA 
(
    ID INTEGER IDENTITY NOT NULL, 
    NOME VARCHAR(50) NOT NULL,
    MESA INT,
    DATACRIACAO DATETIME NOT NULL,d
    DATAMOD DATETIME,
    SETOR_ID INT NOT NULL,
    ACESSO_ID INT

    CONSTRAINT PK_PESSOA_ID PRIMARY KEY (ID),
    CONSTRAINT FK_SETOR_PESSOA_ID FOREIGN KEY (SETOR_ID)
        REFERENCES SETOR(ID)
)   

CREATE TABLE ESCALA 
(   
    ID INTEGER IDENTITY NOT NULL,
    NOME VARCHAR(150) NOT NULL,
    STS VARCHAR(20) NOT NULL, 
    TIPO VARCHAR(20) NOT NULL,
    DATA_ATUACAO_INICIAL VARCHAR(11),
    DATA_ATUACAO_FINAL VARCHAR(11),
    HORA_ATUACAO_INICIAL VARCHAR(8),
    HORA_ATUACAO_FINAL VARCHAR(8),
    PESSOA_ID_CREATE INTEGER NOT NULL,
    PESSOA_ID_UPDATE INTEGER,
    DATACRIACAO DATETIME NOT NULL,
    DATAMOD DATETIME 

    CONSTRAINT PK_ESCALA_ID PRIMARY KEY (ID),
    CONSTRAINT FK_PESSOA_ESCALA_ID_CREATE FOREIGN KEY (PESSOA_ID_CREATE)    
        REFERENCES PESSOA(ID)  
) 

CREATE TABLE LISTA 
(   
    ID INTEGER IDENTITY NOT NULL, 
    PESSOA_ID INT NOT NULL,
    ESCALA_ID INT NOT NULL,
    DATACRIACAO DATETIME NOT NULL,
    INDICE INTEGER NOT NULL

    CONSTRAINT PK_LISTA_ID PRIMARY KEY (ID),
    CONSTRAINT FK_PESSOA_LISTA FOREIGN KEY (PESSOA_ID)
        REFERENCES PESSOA(ID) ON DELETE CASCADE,
    CONSTRAINT FK_ESCALA_LISTA FOREIGN KEY (ESCALA_ID)
        REFERENCES ESCALA(ID) ON DELETE CASCADE
)       

My logic of these tables is: Several people can be on a scale, where I add people to the list and just put the scala_id they belong to.

If anyone knows how to improve this thank you(I do not know if it is the best way)

My question is this::

I want a Trigger INSTEAD OF UPDATE that:

Prevent UPDATE(of course)

Check if there is any scale acting on that date

If there is any, do not update and warn(scale such is acting on that date)

It follows Querry SQL of the code I created for Insert (if it acts insertion and there is another scale on the same date) and Trigger for update, but it is with a Probleme (let update even try scaling acting on that date)

Disregard scale field (Sts = active or inactive)

/* Insert */  
CREATE TRIGGER  IMPEDIR_ESCALA_MESMA_DATA_TIPO_INSERT ON ESCALA 

INSTEAD OF INSERT
AS 
DECLARE 

@NOME                 VARCHAR(150),
@STS                  VARCHAR(20),
@TIPO                 VARCHAR(20),
@DATA_ATUACAO_INICIAL VARCHAR(11),
@DATA_ATUACAO_FINAL   VARCHAR(11),
@HORA_ATUACAO_INICIAL VARCHAR(8),
@HORA_ATUACAO_FINAL   VARCHAR(8),
@PESSOA_ID_CREATE     INTEGER,
@PESSOA_ID_UPDATE     INTEGER,
@DATACRIACAO          DATETIME,
@DATAMOD              DATETIME

        SELECT 

        @NOME                 = NOME                 , 
        @STS                  = STS                  ,
        @TIPO                 = TIPO                 , 
        @DATA_ATUACAO_INICIAL = DATA_ATUACAO_INICIAL ,
        @DATA_ATUACAO_FINAL   = DATA_ATUACAO_FINAL   ,
        @HORA_ATUACAO_INICIAL = HORA_ATUACAO_INICIAL ,
        @HORA_ATUACAO_FINAL   = HORA_ATUACAO_FINAL   ,
        @PESSOA_ID_CREATE     = PESSOA_ID_CREATE     ,
        @PESSOA_ID_UPDATE     = PESSOA_ID_UPDATE     , 
        @DATACRIACAO          = DATACRIACAO          ,
        @DATAMOD              = DATAMOD              

        FROM INSERTED  

        IF((SELECT 'FAIL'

                WHERE EXISTS(SELECT  @DATA_ATUACAO_INICIAL WHERE NOT EXISTS (SELECT  * FROM ESCALA WHERE CONVERT(DATE, @DATA_ATUACAO_INICIAL  ) BETWEEN DATA_ATUACAO_INICIAL AND DATA_ATUACAO_FINAL AND TIPO = @TIPO) 
                )                                         
                   AND EXISTS(SELECT @DATA_ATUACAO_FINAL   WHERE NOT EXISTS (SELECT  * FROM ESCALA WHERE CONVERT(DATE, @DATA_ATUACAO_FINAL  ) BETWEEN DATA_ATUACAO_INICIAL AND DATA_ATUACAO_FINAL  AND TIPO = @TIPO) 
                )                                         
                   OR EXISTS(SELECT @HORA_ATUACAO_INICIAL WHERE NOT EXISTS (SELECT  * FROM ESCALA WHERE CONVERT(TIME, @HORA_ATUACAO_INICIAL) BETWEEN HORA_ATUACAO_INICIAL AND HORA_ATUACAO_FINAL  AND TIPO = @TIPO)
                )                                         
                   AND EXISTS(SELECT @HORA_ATUACAO_FINAL   WHERE NOT EXISTS (SELECT  * FROM ESCALA WHERE CONVERT(TIME, @HORA_ATUACAO_FINAL  ) BETWEEN HORA_ATUACAO_INICIAL AND HORA_ATUACAO_FINAL  AND TIPO = @TIPO) 

                ))  != '')

                        INSERT INTO ESCALA 
                                (
                                NOME                 , 
                                STS                  ,
                                TIPO                 , 
                                DATA_ATUACAO_INICIAL ,
                                DATA_ATUACAO_FINAL   ,
                                HORA_ATUACAO_INICIAL ,
                                HORA_ATUACAO_FINAL   ,
                                PESSOA_ID_CREATE     ,
                                PESSOA_ID_UPDATE     , 
                                DATACRIACAO          ,
                                DATAMOD              
                                ) VALUES 
                                (
                                @NOME                 , 
                                @STS                  ,
                                @TIPO                 , 
                                @DATA_ATUACAO_INICIAL ,
                                @DATA_ATUACAO_FINAL   ,
                                @HORA_ATUACAO_INICIAL ,
                                @HORA_ATUACAO_FINAL   ,
                                @PESSOA_ID_CREATE     ,
                                @PESSOA_ID_UPDATE     , 
                                @DATACRIACAO          ,
                                @DATAMOD                 
                                )

        ELSE RAISERROR ('NÃO FOI POSSÍVEL INSERIR A ESCALA, NÃO É POSSÍVEL DUAS ESCALAS DO MESMO TIPO ATUAREM NA MESMA DATA/HORA',16,1);

/* update*/  
CREATE TRIGGER  IMPEDIR_ESCALA_MESMA_DATA_TIPO_UPDATE ON ESCALA 

INSTEAD OF UPDATE
AS 
DECLARE 
@ID                   INTEGER,
@NOME                 VARCHAR(150),
@STS                  VARCHAR(20),
@TIPO                 VARCHAR(20),
@DATA_ATUACAO_INICIAL VARCHAR(11),
@DATA_ATUACAO_FINAL   VARCHAR(11),
@HORA_ATUACAO_INICIAL VARCHAR(8),
@HORA_ATUACAO_FINAL   VARCHAR(8),
@PESSOA_ID_CREATE     INTEGER,
@PESSOA_ID_UPDATE     INTEGER,
@DATACRIACAO          DATETIME,
@DATAMOD              DATETIME,

@UPDATE_NOME                  VARCHAR(150),
@UPDATE_STS               VARCHAR(20),
@UPDATE_TIPO                  VARCHAR(20),
@UPDATE_DATA_ATUACAO_INICIAL VARCHAR(11),
@UPDATE_DATA_ATUACAO_FINAL   VARCHAR(11),
@UPDATE_HORA_ATUACAO_INICIAL VARCHAR(8),
@UPDATE_HORA_ATUACAO_FINAL   VARCHAR(8),
@UPDATE_PESSOA_ID_CREATE     INTEGER,
@UPDATE_PESSOA_ID_UPDATE      INTEGER,
@UPDATE_DATACRIACAO       DATETIME,
@UPDATE_DATAMOD           DATETIME

        SELECT 
        @ID                   = ID                   ,
        @NOME                 = NOME                 , 
        @STS                  = STS                  ,
        @TIPO                 = TIPO                 , 
        @DATA_ATUACAO_INICIAL = DATA_ATUACAO_INICIAL ,
        @DATA_ATUACAO_FINAL   = DATA_ATUACAO_FINAL   ,
        @HORA_ATUACAO_INICIAL = HORA_ATUACAO_INICIAL ,
        @HORA_ATUACAO_FINAL   = HORA_ATUACAO_FINAL   ,
        @PESSOA_ID_CREATE     = PESSOA_ID_CREATE     ,
        @PESSOA_ID_UPDATE     = PESSOA_ID_UPDATE     , 
        @DATACRIACAO          = DATACRIACAO          ,
        @DATAMOD              = DATAMOD              

        FROM inserted   

        IF((SELECT 'FAIL'

                WHERE EXISTS (SELECT  * FROM ESCALA WHERE CONVERT(DATE, @DATA_ATUACAO_INICIAL ) BETWEEN DATA_ATUACAO_INICIAL AND DATA_ATUACAO_FINAL AND TIPO = @TIPO AND ID != @ID) 

                   AND EXISTS (SELECT  * FROM ESCALA WHERE CONVERT(DATE, @DATA_ATUACAO_FINAL ) BETWEEN DATA_ATUACAO_INICIAL AND DATA_ATUACAO_FINAL  AND TIPO = @TIPO  AND ID != @ID) 

                   OR EXISTS (SELECT  * FROM ESCALA WHERE CONVERT(TIME, @HORA_ATUACAO_INICIAL) BETWEEN HORA_ATUACAO_INICIAL AND HORA_ATUACAO_FINAL  AND TIPO = @TIPO   AND ID != @ID)

                   AND EXISTS (SELECT  * FROM ESCALA WHERE CONVERT(TIME, @HORA_ATUACAO_FINAL ) BETWEEN HORA_ATUACAO_INICIAL AND HORA_ATUACAO_FINAL  AND TIPO = @TIPO  AND ID != @ID) 

            )   != 'FAIL')
        BEGIN



                        UPDATE ESCALA SET

                                NOME                 = @NOME                 , 
                                STS                  = @STS                  ,
                                TIPO                 = @TIPO                 , 
                                DATA_ATUACAO_INICIAL = @DATA_ATUACAO_INICIAL ,
                                DATA_ATUACAO_FINAL   = @DATA_ATUACAO_FINAL   ,
                                HORA_ATUACAO_INICIAL = @HORA_ATUACAO_INICIAL ,
                                HORA_ATUACAO_FINAL   = @HORA_ATUACAO_FINAL   ,
                                PESSOA_ID_CREATE     = @PESSOA_ID_CREATE     ,
                                PESSOA_ID_UPDATE     = @PESSOA_ID_UPDATE     , 
                                DATACRIACAO          = @DATACRIACAO          ,
                                DATAMOD              = @DATAMOD     

                        WHERE ID = @ID


        END
        ELSE RAISERROR ('NÃO FOI POSSÍVEL ATUALIZAR A ESCALA, NÃO É POSSÍVEL DUAS ESCALAS DO MESMO TIPO ATUAREM NA MESMA DATA/HORA',16,1);

Answer 1

The problem with your Rigger is how you compare the dates of the scales and it may result from the way the information is stored in the table. Also note a difference between triggers INSTEAD OF INSERT and INSTEAD OF UPDATE how to compare the result of the various EXISTS clauses:

IF (SELECT 'FAIL' WHERE EXISTS ...)   != '')
vs
IF (SELECT 'FAIL' WHERE EXISTS ...)   != 'FAIL')

Before showing a possible solution, it is worth remembering that it is not guaranteed that Rigger will fire for each individually inserted record. This is probably the most common error in the programming of Rigger procedures, in T-SQL: assuming that at each call of the procedure only a single record exists in the virtual tables. You should take this into account whenever you create a Trigger.

This leads to an additional problem: If the application allows multiple records to be entered at the same time (via BULK INSERT or INSERT INTO SCALE FROM ...), it is not only necessary to compare the new records (in the pseudo INSERTED table) with the SCALE table, but also ensure that the new records, among themselves, do not violate their preconditions.

Having said that, I suggest you first change the way you represent the start and end dates. If instead of using VARCHAR, use DATETIME comparisons become much simpler. And in terms of space, comparing with the VARCHAR will be more efficient.

I leave here a solution that I believe will help you solve your problem, but with some limitations. Note that in this small example I made the change in the definition of your table Escala. I changed the definition of the columns that represent the beginning and end of the scale. Instead of using 4 columns of the type VARCHAR to represent date and time separately, use two of type DATETIME to represent the same information: DATA_ATUACAO_INICIAL and DATA_ACTUACAO_FINAL.

CREATE TABLE ESCALA 
(   
    ID INTEGER IDENTITY NOT NULL,
    NOME VARCHAR(150) NOT NULL,
    STS VARCHAR(20) NOT NULL, 
    TIPO VARCHAR(20) NOT NULL,
    DATA_ATUACAO_INICIAL DATETIME2 NOT NULL,
    DATA_ATUACAO_FINAL DATETIME2 NOT NULL
) 

Here’s Trigger INSTEAD OF INSERT:

CREATE TRIGGER IMPEDIR_ESCALA_MESMA_DATA_TIPO_INSERT ON ESCALA
INSTEAD OF INSERT AS
BEGIN
    --Termina se não há registos para processar
    IF NOT EXISTS (SELECT * FROM INSERTED) 
       RETURN;

    IF NOT EXISTS (
       SELECT 1 
         FROM INSERTED A
        INNER JOIN ESCALA B
           ON A.TIPO = B.TIPO -- Mesmo tipo
          AND A.DATA_ATUACAO_INICIAL <= B.DATA_ATUACAO_FINAL  -- Interseção entre as datas
          AND A.DATA_ATUACAO_FINAL >= B.DATA_ATUACAO_INICIAL  -- Interseção entre as datas
    ) 
        INSERT INTO ESCALA(NOME, STS, TIPO, DATA_ATUACAO_INICIAL, DATA_ATUACAO_FINAL)
        SELECT NOME, STS, TIPO, DATA_ATUACAO_INICIAL, DATA_ATUACAO_FINAL FROM INSERTED
    ELSE 
        RAISERROR ('NÃO FOI POSSÍVEL INSERIR A ESCALA, NÃO É POSSÍVEL DUAS ESCALAS DO MESMO TIPO ATUAREM NA MESMA DATA/HORA',16,1);

    END        
END;

Testing the INSERT:

INSERT INTO ESCALA(NOME, STS, TIPO, DATA_ATUACAO_INICIAL, DATA_ATUACAO_FINAL)
SELECT 'NOME1', '1', '1', '2019-03-21 14:00:00.000000', '2019-03-21 17:00:00.000000' 

Results:

1 Rows affected

Inserting another record:

INSERT INTO ESCALA(NOME, STS, TIPO, DATA_ATUACAO_INICIAL, DATA_ATUACAO_FINAL)
SELECT 'NOME2', '1', '1', '2019-03-20 14:00:00.000000', '2019-03-21 15:00:00.000000'

Results:

Msg 50000, Level 16, State 1, Procedure IMPEDIR_ESCALA_MESMA_DATA_TIPO_INSERT, Line 33 [Batch Start Line 53] IT WAS NOT POSSIBLE TO INSERT THE SCALE, IT IS NOT POSSIBLE TO INSERT TWO SCALES OF SAME TYPE ACTING ON SAME DATE/TIME

Here is Trigger INSTEAD OF UPDATE

CREATE TRIGGER IMPEDIR_ESCALA_MESMA_DATA_TIPO_UPDATE ON dbo.ESCALA 
INSTEAD OF UPDATE AS
BEGIN
    --Termina se não há registos para processar
    IF NOT EXISTS (SELECT * FROM INSERTED) 
       RETURN;

    IF NOT EXISTS (
       SELECT 1 
         FROM INSERTED A
        INNER JOIN dbo.ESCALA  B
           ON A.TIPO = B.TIPO -- Mesmo tipo
          AND A.DATA_ATUACAO_INICIAL <= B.DATA_ATUACAO_FINAL
          AND A.DATA_ATUACAO_FINAL >= B.DATA_ATUACAO_INICIAL  
          AND A.ID <> B.ID
    ) 
        UPDATE E 
           SET E.NOME = I.NOME
             , E.STS = I.STS
             , E.TIPO = I.TIPO
             , E.DATA_ATUACAO_INICIAL = I.DATA_ATUACAO_INICIAL
             , E.DATA_ATUACAO_FINAL = I.DATA_ATUACAO_FINAL
          FROM INSERTED I
         INNER JOIN ESCALA E
            ON E.ID = I.ID
    ELSE 
        RAISERROR ('NÃO FOI POSSÍVEL ATUALIZAR A ESCALA, NÃO É POSSÍVEL DUAS ESCALAS DO MESMO TIPO ATUAREM NA MESMA DATA/HORA',16,1);

END  

About the limitations:

These two triggers work when records are inserted 1 to 1. If you try to insert multiple records using an INSERT INTO SCALE FROM statement ....

INSERT INTO ESCALA(NOME, STS, TIPO, DATA_ATUACAO_INICIAL, DATA_ATUACAO_FINAL)
    SELECT 'NOME1', '1', '1', '2019-03-21 14:00:00.000000', '2019-03-21 17:00:00.000000' UNION ALL
    SELECT 'NOME2', '1', '1', '2019-03-20 14:00:00.000000', '2019-03-21 12:00:00.000000'

Will result:

2 Rows affected

Only you know if this situation is likely to happen or if access to the database is limited by a front end. If it’s possible to say, I try to put here a way to try to mitigate the problem.

Answer 2

TRIGGER INSTEAD OF INSERT

CREATE TRIGGER IMPEDIR_ESCALA_MESMA_DATA_TIPO_INSERT ON ESCALA
INSTEAD OF INSERT AS
BEGIN
    --Termina se não há registos para processar
    IF NOT EXISTS (SELECT * FROM INSERTED) 
       RETURN;

    IF NOT EXISTS (
       SELECT 1 
         FROM INSERTED A
        INNER JOIN ESCALA B
           ON A.TIPO = B.TIPO -- Mesmo tipo
          AND A.DATA_ATUACAO_INICIAL <= B.DATA_ATUACAO_FINAL  -- Interseção entre as datas
          AND A.DATA_ATUACAO_FINAL >= B.DATA_ATUACAO_INICIAL  -- Interseção entre as datas
    ) 
    BEGIN
        INSERT INTO ESCALA(NOME, STS, TIPO, DATA_ATUACAO_INICIAL, DATA_ATUACAO_FINAL, PESSOA_ID_CREATE, DATACRIACAO)
        SELECT NOME, STS, TIPO, DATA_ATUACAO_INICIAL, DATA_ATUACAO_FINAL, PESSOA_ID_CREATE, DATACRIACAO FROM INSERTED
    END
    ELSE 
    BEGIN
        RAISERROR ('NÃO FOI POSSÍVEL INSERIR A ESCALA, NÃO É POSSÍVEL DUAS ESCALAS DO MESMO TIPO ATUAREM NA MESMA DATA/HORA',16,1);

    END        
END

TRIGGER INSTEAD OF UPDATE

CREATE TRIGGER IMPEDIR_ESCALA_MESMA_DATA_TIPO_UPDATE ON dbo.ESCALA 
INSTEAD OF UPDATE AS
BEGIN
    --Termina se não há registos para processar
    IF NOT EXISTS (SELECT * FROM INSERTED) 
       RETURN;

    IF NOT EXISTS (
       SELECT 1 
         FROM INSERTED A
        INNER JOIN dbo.ESCALA  B
           ON A.TIPO = B.TIPO -- Mesmo tipo
          AND A.DATA_ATUACAO_INICIAL <= B.DATA_ATUACAO_FINAL
          AND A.DATA_ATUACAO_FINAL >= B.DATA_ATUACAO_INICIAL  
          AND A.ID <> B.ID
    ) 
    BEGIN
        UPDATE E 
           SET E.NOME                 = I.NOME
             , E.STS                  = I.STS
             , E.TIPO                 = I.TIPO
             , E.DATA_ATUACAO_INICIAL = I.DATA_ATUACAO_INICIAL
             , E.DATA_ATUACAO_FINAL   = I.DATA_ATUACAO_FINAL
             , E.PESSOA_ID_UPDATE     = I.PESSOA_ID_UPDATE
             , E.DATAMOD              = GETDATE()



          FROM INSERTED I
         INNER JOIN ESCALA E
            ON E.ID = I.ID
    END
    ELSE 
    BEGIN
        RAISERROR ('NÃO FOI POSSÍVEL ATUALIZAR A ESCALA, NÃO É POSSÍVEL DUAS ESCALAS DO MESMO TIPO ATUAREM NA MESMA DATA/HORA',16,1);
    END
END