Prevent Insert or Update If date is in a Registered range

Question

I am with the following doubt:

I have the table:

 Escala
 Data_Atuacao_Inicial, Data_Atuacao_Final, tipo

The guy can be 1 or 2

I want to create a Rigger to stop the Insert or update of this table, if I insert a scale or edit an existing one and put a date between the limits of another existing in the bank prevent and return a message.

Example:

Escala 
Data_Atuacao_Inicial = 09-10-2019
Data_Atuacao_Final   = 10-10-2019
tipo                 = 1

When I try inserir or editar another scale and place the dates between 09-10-2019 and 10-10-2019 and being type 1 should prevent(Instead off) and give a message (There is already a scale of type 1 acting on that date)

Can you help me?

Answer 1

On your Rigger do this check:

IF EXISTS(SELECT * FROM inserted WHERE inserted.DATA1 < DATA2)
   rollback tran

Alas for your problem, treat as necessary the following part DATA1 < DATA2

Answer 2

Try something like

create Trigger lock on TABLE for Insert as declare @int code, @data date, @int type select @codigo = codigo, @data = date, @type = type from inserted IF (@data between '09-10-2019' and ' 09-10-2019' and @tipo = 1 ) BEGIN delete from TABLE Where code = @code and type = @type and data between '09-10-2019' and ' 09-10-2019' raiserror('There is already a scale of type 1 acting on that date !',16,1) END

To do the same thing only that with UPDATE just switch from Insert to Update in the Trigger type declaration " for update "