Create a function that performs the logic you want and then create a check constraint or index that leverages that function.
Here is a functional example, the final insert fails. The function is evaluated row by row, so if you need to insert as a set and evaluate after, you'd need to do an "instead of" trigger:
CREATE TABLE dbo.Test(ListID INT, ItemID CHAR(1))
GO
CREATE FUNCTION dbo.TestConstraintPassed(@ListID INT, @ItemID CHAR(1))
RETURNS TINYINT
AS
BEGIN
DECLARE @retVal TINYINT = 0;
DECLARE @data TABLE (ListID INT, ItemID CHAR(1),[Match] INT)
INSERT INTO @data(ListID,ItemID,[Match]) SELECT ListID,ItemID,-1 AS [Match] FROM dbo.Test
UPDATE @data
SET [Match]=1
WHERE ItemID IN (SELECT ItemID FROM @data WHERE ListID=@ListID)
DECLARE @MatchCount INT
SELECT @MatchCount=SUM([Match]) FROM @data WHERE ListID=@ListID
IF NOT EXISTS(
SELECT *
FROM (
SELECT ListID,SUM([Match]) AS [MatchCount]
FROM @data
WHERE ListID<>@ListID
GROUP BY ListID
) dat
WHERE @MatchCount=[MatchCount]
)
BEGIN
SET @retVal=1;
END
RETURN @retVal;
END
GO
ALTER TABLE dbo.Test
ADD CONSTRAINT chkTest
CHECK (dbo.TestConstraintPassed(ListID, ItemID) = 1);
GO
INSERT INTO dbo.Test(ListID,ItemID) SELECT 1,'a'
INSERT INTO dbo.Test(ListID,ItemID) SELECT 1,'b'
INSERT INTO dbo.Test(ListID,ItemID) SELECT 2,'a'
INSERT INTO dbo.Test(ListID,ItemID) SELECT 2,'b'
Related