CREATE TABLE [dbo].[PIF_MEP99] (
	[PIFRecID] [int] NOT NULL ,
	[MEPRecID] [int] NOT NULL ,
	[PrimaryInd] [char] (1) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PIF_MEP99] WITH NOCHECK ADD 
	CONSTRAINT [PIF_MEP99_PK] PRIMARY KEY  CLUSTERED 
	(
		[PIFRecID],
		[MEPRecID]
	)  ON [PRIMARY] 
GO

INSERT INTO PIF_MEP99(PIFRecID, MEPRecID, PrimaryInd)
SELECT 1,1,'Y' UNION ALL
SELECT 2,1,'N' UNION ALL
SELECT 3,2,'N'
GO

CREATE TRIGGER dbo_PIF_MEP99_tr_Rule1 ON dbo.PIF_MEP99 
	   FOR UPDATE, DELETE 
AS 
  SET NOCOUNT ON 

-- Rule 1:  Prevent and MEP from having more than 1 PIF as Primary

	IF  Exists ( SELECT * FROM inserted i 
		        INNER JOIN PIF_MEP99 p 
			        ON i.MEPRecID = p.MEPRecID 
			       AND i.PrimaryInd = 'Y'
			       AND p.PrimaryInd = 'Y')
	  BEGIN
		ROLLBACK TRAN
		RAISERROR  500003 'Attempting to Insert 2 Primary PIFs for an MEP'
	  END
GO

SELECT * FROM PIF_MEP99
GO

 SELECT * FROM (SELECT 4 AS PIFRecID,1  AS MEPRecID,'Y' AS PrimaryInd) AS i 
		        INNER JOIN PIF_MEP p 
			        ON i.MEPRecID = p.MEPRecID 
			       AND i.PrimaryInd = 'Y'
			       AND p.PrimaryInd = 'Y'
GO

BEGIN TRAN
INSERT INTO PIF_MEP99 (PIFRecID, MEPRecID, PrimaryInd) SELECT 4,1,'Y'
COMMIT TRAN
GO

SELECT * FROM PIF_MEP99
GO

 SELECT * FROM (SELECT 5 AS PIFRecID,1  AS MEPRecID,'Y' AS PrimaryInd) AS i 
		        INNER JOIN PIF_MEP p 
			        ON i.MEPRecID = p.MEPRecID 
			       AND i.PrimaryInd = 'Y'
			       AND p.PrimaryInd = 'Y'
GO

DROP TABLE PIF_MEP99
GO

CREATE TRIGGER dbo_PIF_MEP_tr_Rule1 ON dbo.PIF_MEP 
	   FOR UPDATE, INSERT 
AS 
  SET NOCOUNT ON 

-- Rule 1:  Prevent an MEP from having more than 1 PIF as Primary

	IF ( SELECT COUNT(*) FROM inserted i 
	 INNER JOIN PIF_MEP p 
		 ON i.MEPRecID = p.MEPRecID 
	        AND i.PrimaryInd = 'Y'
	        AND p.PrimaryInd = 'Y') <> 1
	  BEGIN
		ROLLBACK TRAN
		RAISERROR  500003 'Attempting to Insert 2 Primary PIFs for an MEP'
	  END
GO

CREATE TRIGGER	dbo_PIF_MEP_tr_Rule1
ON		dbo.PIF_MEP
FOR		UPDATE,
		INSERT
AS 

SET NOCOUNT ON 

-- For UPDATE, if neither MEPRecID nor PrimaryInd is updated then return now
IF NOT (UPDATE(MEPRecID) OR UPDATE(PrimaryInd))
	RETURN

DECLARE	@MaxItems INT

SELECT	@MaxItems = MAX(e.Primaries)
FROM	(
		SELECT		i.MEPRecID,
				COUNT(*) AS Primaries
		FROM		inserted AS i
		WHERE		i.PrimaryInd = 'Y'
		GROUP BY	i.MEPRecID
	) AS e

IF @MaxItems IS NULL
	RETURN
ELSE
	IF @MaxItems > 1
		BEGIN
			ROLLBACK TRAN
			RAISERROR 500003 'Attempting to INSERT/UPDATE two or more primary PIF for a MEP.'
			RETURN
		END

IF EXISTS	(
			SELECT		*
			FROM		inserted AS i
			INNER JOIN	PIF_MEP AS pm ON pm.MEPRecID = i.MEPRecID
			WHERE		i.PrimaryInd = 'Y'
					AND pm.PrimaryInd = 'Y'
					AND i.PIFRecID <> pm.PIFRecID
		)
	BEGIN
		ROLLBACK TRAN
		RAISERROR 500003 'Attempting to INSERT/UPDATE two or more primary PIF for a MEP.'
		RETURN
	END
ELSE
	RETURN

DECLARE	@Sample TABLE (Grp INT, Preferred INT)

INSERT	@Sample
SELECT	1, 1 UNION ALL
SELECT	2, 0 UNION ALL
SELECT	3, 0

-- Case
SELECT		Grp,
		COUNT(*)
FROM		@Sample
WHERE		Preferred = 1
GROUP BY	Grp
ORDER BY	Grp