Check Constraint to check view (a no-no) or multiple tables (subquery or UDF)

[wbodger]

I have a relationship table which I only want to allow certain entities to enter a record. And those entities could exist in one several places/tables. I have a view that aggregates all of them, but you cannot reference a view in a constraint. So, I was thinking UDF, but now I see that is often problematic. I saw mention of using an instead of trigger, but not sure how that would be accomplished. I have a simple table

CREATE TABLE [dbo].[Entity_Tag](
	[TagId] [int] NOT NULL,
	[TaggedEntityId] [int] NOT NULL,
	[TaggedBy] [int] NOT NULL,
	[TaggedByDate] [datetime2](7) NOT NULL
)

where TaggedEntityId has to exist in either Table1, Table2 or Table3. As mentioned earlier, I have a view to look that up, but cannot use a view in a check constraint. I was going to create a UDF

CREATE FUNCTION dbo.fn_check_entity
  (@key int)
RETURNS bit
AS
BEGIN
DECLARE @Ret INT
IF EXISTS (SELECT * FROM dbo.Vw_AllUserEmails_Advanced WHERE EntityISN = @key)
BEGIN
	SET @Ret = 1
END
ELSE
BEGIN
	SET @Ret = 0
END
RETURN @Ret
END

then add the constraint

ALTER TABLE [dbo].[Entity_Tag] ADD CONSTRAINT [FK_Entity_Tag_EntityISN] CHECK (dbo.fn_check_entity(TaggedEntityId)=1)

But it seems this UDF method may not be reliable as I have seen reports that it is actually not checked until AFTER the insert?

Any insight you can share would be greatly appreciated. I am sure I am not the first person t run into this, I have just not found a good solution.

Thanks,
wb