In what you have tried you don't take into account which type of comparison to use depending on the condition column.
I would use an
INSTEAD OF INSERT
trigger to check value(s) and condition like that:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[Check_Insert]
ON [dbo].[RangeTest]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @OK bit;
DECLARE @FromValue int;
DECLARE @ToValue int;
DECLARE @Condition nvarchar(20);
DECLARE curTest CURSOR LOCAL FAST_FORWARD FOR
SELECT [FromValue]
, [ToValue]
, [Condition]
FROM inserted;
OPEN curTest;
WHILE (1 = 1)
BEGIN
FETCH NEXT FROM curTest INTO @FromValue, @ToValue, @Condition;
IF (@@FETCH_STATUS <> 0)
BREAK;
SET @OK = 1;
IF (@FromValue IS NOT NULL)
BEGIN
IF EXISTS(SELECT 1
FROM [dbo].[RangeTest]
WHERE ([Condition] = 'Equals')
AND (@FromValue = [FromValue])
)
BEGIN
SET @OK = 0;
RAISERROR ('FromValue conflicts with existing Equals entry', 1, 1);
END
IF EXISTS(SELECT 1
FROM [dbo].[RangeTest]
WHERE ([Condition] = 'Between')
AND (@FromValue >= [FromValue])
AND (@FromValue <= [ToValue])
)
BEGIN
SET @OK = 0;
RAISERROR ('FromValue conflicts with existing Between entry', 1, 1);
END
IF EXISTS(SELECT 1
FROM [dbo].[RangeTest]
WHERE ([Condition] = 'Greater Than')
AND (@FromValue > [FromValue])
)
BEGIN
SET @OK = 0;
RAISERROR ('FromValue conflicts with existing Greater-Than entry', 1, 1);
END
END
IF (@ToValue IS NOT NULL)
BEGIN
IF EXISTS(SELECT 1
FROM [dbo].[RangeTest]
WHERE ([Condition] = 'Equals')
AND (@ToValue = [FromValue])
)
BEGIN
SET @OK = 0;
RAISERROR ('ToValue conflicts with existing Equals entry', 1, 1);
END
IF EXISTS(SELECT 1
FROM [dbo].[RangeTest]
WHERE ([Condition] = 'Between')
AND (@ToValue >= [FromValue])
AND (@ToValue <= [ToValue])
)
BEGIN
SET @OK = 0;
RAISERROR ('ToValue conflicts with existing Between entry', 1, 1);
END
IF EXISTS(SELECT 1
FROM [dbo].[RangeTest]
WHERE ([Condition] = 'Greater Than')
AND (@ToValue > [FromValue])
)
BEGIN
SET @OK = 0;
RAISERROR ('ToValue conflicts with existing Greater-Than entry', 1, 1);
END
END
IF (@OK = 1)
INSERT INTO [dbo].[RangeTest]
([FromValue]
,[ToValue]
,[Condition])
VALUES
(@FromValue
,@ToValue
,@Condition)
END
CLOSE curTest;
DEALLOCATE curTest;
END
GO