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
WHEREU.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

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
WHEREU.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

|||

I'm afraid I don't understand your reply.

-Cody

|||You said you have to insert the correct RoleID in the aspnet_UsersInRole table, but all you issue is update statements. Update isn't what you want. Insert is.|||My apologies for the confusion --

The record will already exist in the aspnet_UsersInRoles table; I just need to update the roleID field of the appropriate record.
-Cody|||

Could be anything, try putting print statements in your trigger, then test it using transactions like:

BEGIN TRANSACTION
INSERT ...
ROLLBACK TRANSACTION

then execute that and see what messages you get. You'll need to use sql management studio or query analyzer though to get the print results.