Challenging Insert Trigger

First, please reference the following document which shows the layout of the database and tables:

http://www.eastproject.org/cody/table_layout.pdf

Here's the deal--

I have two databases, SiteData and CommunityServer2.  Both run on the same MS SQL 2000 Server.  We're concerned about two tables on each database:  SiteData.dbo.Users, SiteData.dbo.UM_User_Access_Type, CommunityServer2.dbo.aspnet_Users and CommunityServer2.dbo.aspnet_UsersInRoles.

Whever a user is inserted into the CommunityServer2.dbo.aspnet_Users table, I need to give the user appropriate rules (e.g., moderator, student, etc.) by inserting the correct RoleID in the SiteData.dbo.aspnet_UsersInRoles table.

Have I lost you yet?   Good.

Please reference the following trigger:

ALTER TRIGGER trig_UpdateForumRoles
   ON  CommunityServer2.dbo.aspnet_Users
   AFTER INSERT
AS
DECLARE @Username VARCHAR(100) --variable to hold the username from CommunityServer2.aspnet_Users
DECLARE @UserType VARCHAR(50) --variable to hold the username from SiteData.dbo.UM_User_Access_Type
DECLARE @UserID uniqueidentifier --variable to hold the username from CommunityServer2.aspnet_Users
BEGIN
 
SELECT @Username=Username, @UserID=UserID FROM Inserted --should only return one record, meaning we'd only insert one record in aspnet_Users at a time.

SELECT @UserType=UserType
FROM SiteData.dbo.UM_User_Access_Type UM, SiteData.dbo.Users U, aspnet_Users AU, aspnet_Roles AR, aspnet_UsersInRoles AUIR
WHERE U.Username=@UserName and U.ID = UM.Student_ID and U.Username = AU.UserName and AU.UserID = AUIR.UserID and AUIR.RoleID = AR.RoleID
  
 
 -- if usertype is a SuperAdmin as determined from SiteData.dbo.UM_User_Access_Type, we insert the appropriate RoleID in aspnet_UsersInRoles
 IF (@UserType = 'SuperAdmin')
 BEGIN
 UPDATE aspnet_UsersInRole
 SET RoleID = 'EDA73944-1B67-4DAA-B6CB-792847B6C694' WHERE UserID = @UserID
 END

 IF (@UserType = 'StaffAdmin' or @UserType='Partner')
 BEGIN
 UPDATE aspnet_UsersInRole
 SET RoleID = '7B317FF8-7C3C-4D95-AC44-E27E849D4520' WHERE UserID = @UserID
 END

 IF (@UserType = 'Facilitator')
 BEGIN
 UPDATE aspnet_UsersInRole
 SET RoleID = 'D04FEA9F-35E9-41A5-B062-B9C1524D4266' WHERE UserID = @UserID
 END

 IF (@UserType = 'Student')
 BEGIN
 UPDATE aspnet_UsersInRole
 SET RoleID = '4F94ABB9-1371-4834-95D6-E8364824F484' WHERE UserID = @UserID
 END
 
 
END
GO

However, it's not yet working.  I can create the trigger fine (so no syntax problems); however, it's not updating the aspnet_UsersInRoles table.

 

Any ideas?

Thank you very much!
-Cody