Q:

FullText Search on Many-to-Many relationships

I implemented a many-to-many tag system, and now I want to peform a full text search.  The problem is AND is not working.  If I search on an item with the search terms tag1 AND tag2 AND tag3, I get no rows even though my record is tagged by all three tags. 
To solve this, I tried a scalar function for concatenating my tags and wrapping that in a view.  However, this failed with the following error:

Cannot create index on view "view" because function "dbo. GetTags" referenced by the view performs user or system data access. 

I've restricted my search to ORs only, but I now have another Many-To-Many relationship that has additional info in the join table that I also want to search on and that requires an AND. 
The only options I can think of are unioning a bunch of inner joins or using triggers. 
Does anyone have any experience or advice on solving this problem? 

A:

If you're looking up tags, can you just search using the like operator?  Or is there some reason that you need full text searching?  
Otherwise to use full text searching in an AND fashion searching tags, assuming that you store one tag per row in your tag data model, two ideas:

Break down your full text search into 1 query per full text search.  So if there are 3 and conditions, create 3 separate full text search queries per AND condition
Figure out how to concatenate all tags into a comma-delimited list.  Store the comma-delimited list in your data model, perhaps in the table that contains the actual data, not the tags.  As you're thinking, you could use a trigger to store the values.  Then run your full text search operation against that one column, rather than your tagging model.  This option is a workaround to not being able to create an indexed view due to the function call in your view

Option 2 may perform better than option 1, but would require more storage. 