Trigger to ban scale insertion that is already working on that SQL Server date

Question

Thank you for your time!

1° I’ll put the data and then I’ll point out my doubt, I’ve tried a thousand times and I can’t get the logic that works.

I have the following table

• Scale Table

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,
    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)  
) 

My goal is this:

When I enter or update a scale, it is necessary to check whether:

1. The date or time of the scale inserted or updated is already being used
2. The following is an illustration of exceptions to be covered by Trigger:

After several tests and attempts I reached Triggers(Insert and Update separately):

• INSERT

USE [RowDB]
GO
/****** Object:  Trigger [dbo].[IMPEDIR_ESCALA_MESMA_DATA_TIPO_INSERT]    Script Date: 24/05/2019 08:27:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[IMPEDIR_ESCALA_MESMA_DATA_TIPO_INSERT] ON [dbo].[ESCALA]
INSTEAD OF INSERT AS

BEGIN

    IF EXISTS(SELECT 1 FROM INSERTED I 
                            INNER JOIN DBO.ESCALA E
                       ON   I.TIPO = E.TIPO AND E.STS = 'ATIVO'

                            WHERE   



                                    CONVERT(date, I.DATA_ATUACAO_INICIAL) BETWEEN CONVERT(date, E.DATA_ATUACAO_INICIAL) AND CONVERT(date, E.DATA_ATUACAO_FINAL)
                                    OR 
                                    CONVERT(date, I.DATA_ATUACAO_FINAL)   BETWEEN CONVERT(date, E.DATA_ATUACAO_INICIAL) AND CONVERT(date, E.DATA_ATUACAO_FINAL)

                                    AND 

                                    CONVERT(time, I.DATA_ATUACAO_INICIAL) BETWEEN CONVERT(time, E.DATA_ATUACAO_INICIAL) AND CONVERT(time, E.DATA_ATUACAO_FINAL)

                                    OR
                                    CONVERT(time, I.DATA_ATUACAO_FINAL)   BETWEEN CONVERT(time, E.DATA_ATUACAO_INICIAL) AND CONVERT(time, E.DATA_ATUACAO_FINAL)

              )
    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
    ELSE
    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     
END 

• UPDATE

USE [RowDB]
GO
/****** Object:  Trigger [dbo].[IMPEDIR_ESCALA_MESMA_DATA_TIPO_UPDATE]    Script Date: 24/05/2019 08:27:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[IMPEDIR_ESCALA_MESMA_DATA_TIPO_UPDATE] ON [dbo].[ESCALA] 
INSTEAD OF UPDATE AS
BEGIN

    IF EXISTS(SELECT * FROM INSERTED I 
                            INNER JOIN DBO.ESCALA E
                       ON   I.TIPO = E.TIPO AND E.STS = 'ATIVO' AND E.ID != I.ID

                            WHERE   

                                    CONVERT(date, I.DATA_ATUACAO_INICIAL) BETWEEN CONVERT(date, E.DATA_ATUACAO_INICIAL) AND CONVERT(date, E.DATA_ATUACAO_FINAL)
                                    AND 
                                    CONVERT(time, I.DATA_ATUACAO_INICIAL) BETWEEN CONVERT(time, E.DATA_ATUACAO_INICIAL) AND CONVERT(time, E.DATA_ATUACAO_FINAL)

                                    OR

                                    CONVERT(date, I.DATA_ATUACAO_FINAL)   BETWEEN CONVERT(date, E.DATA_ATUACAO_INICIAL) AND CONVERT(date, E.DATA_ATUACAO_FINAL)
                                    AND
                                    CONVERT(time, I.DATA_ATUACAO_FINAL)   BETWEEN CONVERT(time, E.DATA_ATUACAO_INICIAL) AND CONVERT(time, E.DATA_ATUACAO_FINAL)


              )
    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
    ELSE
    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

END  

I hope I’ve managed to explain my problem, any questions just post. If you don’t know how to vote to rank the question, I really need a solution. With the triggers I created sometimes yes, prevents the registration when it should not, or lets register when you can not. Thanks for the help!