| 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
go
CREATE TRIGGER tg_MITS_UpdateForumSubscriptions
ON InstantASP_UsersRoles
for DELETE,INSERT,UPDATE
AS
EXEC sp_MITS_UpdateForumSubscriptions go
|