RE: SQL Script for Forum Subscriptions

Ryan Healey — Mon, 19 Sep 2005 08:38:55 GMT

Hi Jon,

Many thanks for making this available. When i have a moment i'll review the SQL and provide any further comments. Good stuff :D

SQL Script for Forum Subscriptions

jon.vickers — Sun, 18 Sep 2005 16:51:43 GMT

Here is a SQL script I created that will automatically subscribe all memebers to all forums in which they are a group member.

The script was written quickly and probably needs to suggestions or adjustments.

I have added this script to execute as a trigger on the InstantForum_ForumsRoles and InstantASP_UsersRoles. Any time data is changed in these tables this trigger is fired.

One of the key points to this script is the second part. It automatically removes anyone from a forum subscription if their group permissions are removed from that forum. I am not that familiar with InstantASP Forum, my initial observation is if someone is removed from a forum, or banned from all the forums, they will still receive the subscription notices.

Anyway, take a look and let me know if this is problematic.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE sp_MITS_UpdateForumSubscriptions
as

/* Insert any users that are assigned to forum roles in the forum subscriptions table */

INSERT INTO InstantForum_ForumSubscriptions (ForumId, UserId, SubscriptionType)
SELECT fo.ForumId, u.UserId, SubType=4
FROM InstantForum_ForumsRoles fr
JOIN InstantASP_Roles r ON fr.RoleId=r.RoleId
JOIN InstantForum_Forums fo ON fr.ForumId=fo.ForumId
JOIN InstantASP_UsersRoles ur ON fr.RoleId = ur.RoleId
JOIN InstantASP_Users u ON u.UserId = ur.UserId
LEFT OUTER JOIN InstantForum_ForumSubscriptions fs ON fs.UserId=u.UserId AND fs.ForumId=fo.ForumId
WHERE fo.IsCategory=0 AND fs.UserId is null
GROUP BY u.UserId, fo.ForumId

/*Remove any users that no longer belong to the correct roles from the subscriptions tables */

DELETE FROM InstantForum_ForumSubscriptions
WHERE ForumSubscriptionId IN
(
SELECT fs.ForumSubscriptionId
FROM InstantForum_ForumSubscriptions fs
LEFT OUTER JOIN
(
SELECT fs.ForumId, fs.UserId, fr.ForumRoleId
FROM InstantForum_ForumSubscriptions fs
JOIN InstantASP_UsersRoles ur ON fs.UserId=ur.UserId
LEFT OUTER JOIN InstantForum_ForumsRoles fr ON fs.ForumId=fr.ForumId AND ur.RoleId=fr.RoleId
WHERE fr.ForumRoleId is NOT NULL
)
fj ON fj.ForumId=fs.ForumId AND fj.UserId=fs.UserId
WHERE fj.ForumId IS NULL
)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER tg_MITS_UpdateForumSubscriptions2
ON InstantForum_ForumsRoles
for DELETE,INSERT,UPDATE
AS
EXEC sp_MITS_UpdateForumSubscriptions

CREATE TRIGGER tg_MITS_UpdateForumSubscriptions
ON InstantASP_UsersRoles

for DELETE,INSERT,UPDATE
AS
EXEC sp_MITS_UpdateForumSubscriptions

InstantASP Community Forums / Old Forums / InstantForum.NET 4.x / Suggestions & Requests / SQL Script for Forum Subscriptions / Latest Posts

RE: SQL Script for Forum Subscriptions

SQL Script for Forum Subscriptions