Início / dba / Perguntas / 137512
Accepted
Muflix
Asked: 2016-05-05 06:00:20 +0800 CST

Como verificar se o intervalo entre dois números está disponível na tabela de intervalos

  • 772

Eu tenho a seguinte tabela de intervalos de números

Create table Ranges {
  Id int identity(1,1) primary key,
  RangeFrom int not null,
  RangeTo int not null
}

Insert into Ranges(RangeFrom, RangeTo) VALUES (1,4)
Insert into Ranges(RangeFrom, RangeTo) VALUES (5,9)
Insert into Ranges(RangeFrom, RangeTo) VALUES (10,14)
-- here is free space (15 - 19)
Insert into Ranges(RangeFrom, RangeTo) VALUES (20,24)

E eu quero inserir um novo registro por exemplo

Insert into Ranges(RangeFrom, RangeTo) VALUES (15,19) -- Ok
Insert into Ranges(RangeFrom, RangeTo) VALUES (15,30) -- Fail

A questão é que antes da inserção preciso verificar se os números entre o intervalo na instrução de inserção já não são usados ​​em nenhum outro intervalo da tabela.

Qualquer ideia ?

sql-server-2008

3 respostas

  1. select count(*)
from ( select RangeTo, LEAD(RangeFrom, 1, 0) OVER (ORDER BY RangeTo) 
                       AS LeadRangFrom 
       from table 
     ) ss 
where @RangeFrom > RangeTo 
  and ( @RangeTo < LeadRangFrom or LeadRangFrom is null)

    Se count(*) for um, então você pode inserir
    É essa a pergunta que você está fazendo?

    • 3
  2. Best Answer

    Você pode usar um gatilho de inserção para verificar os valores inseridos nos dados da tabela e falhar se o intervalo já existir.

    Mas, como você está usando um Stored Proc, pode verificar no proc. Algo assim:

    CREATE PROCEDURE blah (@rangeFrom int, @rangeTo int)
AS
BEGIN
IF EXISTS(
SELECT 1 FROM Ranges 
WHERE @rangeFrom BETWEEN RangeFrom AND RangeTo 
    OR @rangeTo BETWEEN RangeFrom AND RangeTo
    OR RangeTo BETWEEN @rangeTo AND @rangeFrom
    OR RangeFrom BETWEEN @rangeFrom AND @rangeTo
)
return

INSERT INTO Ranges VALUES(@rangeFrom, @rangeTo)


END

    Isso funcionará se você puder garantir que as inserções ocorram apenas por meio do procedimento armazenado. Se alguém inserir via t-sql, apenas o gatilho poderá impedir isso.

    • 0

  3. Como outros já mencionaram, um gatilho funcionaria bem. Você não precisa alterar o procedimento armazenado, basta colocar um INSTEAD OF INSERTgatilho na tabela.

    O gatilho irá verificar se o valor RangeFrom que está sendo inserido está entre algum dos intervalos já existentes no mesmo e, em seguida, fará o mesmo para o valor RangeTo. Se passar dessa lógica, ele vai inserir.

    Se falhar, ele reverterá. Observe que isso só funciona quando você faz inserções individuais como você postou. Se você quiser fazer inserções de mais de 1 linha de dados, precisará ajustar isso.

    CREATE TRIGGER tI_Ranges ON Ranges
INSTEAD OF INSERT 
AS
BEGIN 
    IF (SELECT COUNT(*) 
        FROM Ranges t 
        CROSS APPLY inserted 
        WHERE inserted.rangefrom BETWEEN t.rangefrom AND t.rangeto) = 0
          AND (SELECT COUNT(*) 
               FROM Ranges t 
               CROSS APPLY inserted 
               WHERE inserted.rangeto BETWEEN t.rangefrom AND t.rangeto) = 0
    BEGIN 
        INSERT INTO Ranges(rangefrom, rangeto)
            SELECT inserted.rangefrom, inserted.rangeto
            FROM inserted
    END
    ELSE 
    BEGIN 
        ROLLBACK TRAN
    END
END
GO
    • 0

relate perguntas