--Add CHECK for Area and Constituent domains..
create table StuffIt(Area char(1) NOT NULL, Constituent CHAR(1) NOT NULL
	, EntrySequence  INT NOT NULL CHECK (EntrySequence > 0)
	,PRIMARY KEY (Area, Constituent, EntrySequence))
GO 
CREATE TRIGGER trg_StuffIt_Hide
ON StuffIt
INSTEAD OF INSERT
AS
BEGIN
--Handles internal numbering
--Warning: May result in data lose! <EVIL LAUGH>
INSERT StuffIT (Area, Constituent, EntrySequence)
SELECT DISTINCT Area, Constituent, ISNULL((SELECT MAX(EntrySequence) FROM dbo.StuffIt S 
			WHERE S.Area = I.Area AND I.Constituent = S.Constituent),0)+1
FROM INSERTED I END
GO
INSERT StuffIT (Area, Constituent, EntrySequence)
	VALUES ('A','V',5)
INSERT StuffIT (Area, Constituent, EntrySequence)
	VALUES ('A','F',0)
INSERT StuffIT (Area, Constituent, EntrySequence)
	SELECT 'A','V',0
	UNION ALL
	SELECT 'B','F',1
--The DISTINCT in the trigger collapses this to just one entry of B,F ..
--ie You lose rows...
INSERT StuffIT (Area, Constituent, EntrySequence)
	SELECT 'B','F',2
	UNION ALL
	SELECT 'B','F',1
	UNION ALL
	SELECT 'A','C',1
GO
Select * from stuffit
go
drop table stuffit
go