quote:

---

Originally posted by shapper

Hi,

My problems here are the behaviors I am looking for, and LINQ integration in Many to Many relationships.

Consider the following tables:

Posts (PostId PK)
Files (FileId PK)

PostsTags (PostId PK, TagId PK)
FilesTags (FileId PK, TagId PK)

Tags (TagId PK, TagName)

The behavior I am looking, which seems logic to me, is:
(This is the example for Posts. For files is the same)

- When a tag is added to a Post, a record is added to TagsInPosts.
If the Tag (given its TagName) does not exist in Tags then it is
created in Tags table.

- When a tag is removed from a post then it is deleted from PostsTags.
If that tag, i.e., is no longer associated with other Post or File
through PostsTags and FilesTags then it is deleted from Tags.

I have Stored Procedures that do this.
But can I simulate this with constrains and maybe triggers.

To be honest I am just starting with LINQ so I am not sure where do I need to ensure this behavior: in my LINQ classes or in my SQL tables.

Thanks,
Miguel

---

quote:

---

Yes, you would use a trigger for this. I am not sure why you want to do this; what if you want to eventually apply that tag to a different post? You'd have to keep deleting and then re-creating tags. Wouldn't it be better to have a "tag maintenance" page where you can see all tags and optionally delete any not associated with any posts?

---

-- Create post
INSERT INTO dbo.Posts(BlogId, AuthorId, PostTitle, PostBody)
OUTPUT Inserted.PostId INTO @Posts(PostId)
SELECT @BlogId, @AuthorId, @PostTitle, @PostBody

-- Create post tags
INSERT INTO dbo.Tags(TagText)
SELECT data 
FROM dbo.SplitText(@PostTags, ',') AS st
WHERE NOT EXISTS(
  SELECT TagText 
  FROM dbo.Tags
  WHERE TagText = st.data)

-- Add tags ids to @Tags
INSERT INTO @Tags(TagId)
SELECT TagId 
FROM dbo.Tags AS t
JOIN dbo.SplitText(@PostTags, ',') AS st 
ON t.TagText = st.data
		
-- Associate tag to post
INSERT INTO dbo.TagsInPosts(PostId, TagId)
SELECT p.PostId, t.TagId
FROM @Posts AS p
CROSS JOIN @Tags AS t