Hello,

I am using ASP.NET 2.0 Membership services and to save some time I am
not extending the Membership. I realized that I can create a table
named "Users" and with a Trigger fill it with a replica of the
aspnet_membership table (UserID, Email, Password) and then continue to
use the other tables that I had already created before which are
linked to Users table.

Example of tables: Users(Suposed Copy of aspnet_membership),
Challenge, ChallengeRatings, aspnet_membership(contains all the data
from the registered users).

My "Users" table contains the same identical data. The biggest problem
is that the UserID in "aspnet_membership" table is a uniqueidentifier
so I cannot link it directly to the "Users" since it has different
data types. ([UniqueIdentifier] VS [Int] with identity)

I was wondering if there is a way for me to create a trigger, and
everytime that a user is created in aspnet_membership to insert it
also in User with a different primary key such as varchar(16) to avoid
data type colisions.

A wrong written rough idea:

GO
CREATE TRIGGER InsertIntoUser
ON aspnet_Membership
AFTER INSERT
AS
DECLARE
@userID varchar(16),
@Email nvarchar(50),
@Password nvarchar(50),
@Username nvarchar(50)

SET @userID = (Inserted userID in aspnet_membership)
@Email = (Inserted email in aspnet_membership)
@Password = (Inserted Password in aspnet_membership)
@Username = (Inserted Username in aspnet_membership)
BEGIN
INSERT INTO [WikedCompetitions].[dbo].[Users]
([email]
,[Password]
,[Username]
,[UserID])
VALUES
(@Email, nvarchar(50),
,@Password, nvarchar(50),
,@Username, nvarchar(50),
,@UserID, varchar(16),)
WHERE Users.UserID = inserted.UserID
END

Now just really have to make it right since I am quite a newbie at
Triggers!

Thank you in advance for any replies,

Ricardo

(ricardo.sobral.santos (AT) googlemail (DOT) com) writes:
It's not clear why you have different data type, and least of all
why you make things difficult to throw in an IDENTITY.

This you cannot do. Keep in mind that a trigger fires once per
statement, so there can be multiple rows. But there is no need
for variables. Just say:

INSERT (...)
SELECT ... FROM inserted

I don't really know that this is supposed to be, but maybe you mean

convert(nvarchar(50), Email)


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Yes that is something I was needing.
I need to convert an Hex type to Varchar and use that as a id. I would
like to use an integer but the cast would not work.

I think I might have the answer.

ALTER TRIGGER myTrigger_INSERT ON aspnet_Membership FOR INSERT AS

--this is an INSERT FROM SELECT
INSERT INTO WikedCompetitions.dbo.testTable
SELECT
CAST(userId AS VARCHAR(16)), --pay attention to the column sizes
because of the uniqueidentifier type
(SELECT UserName FROM aspnet_Users WHERE aspnet_Users.UserId =
inserted.UserId),
Email,
Password
FROM inserted

So this casts the userID(Hex) from aspnet_Users table to Users table
which has a field called UserID as a Varchar(16).

the only big problem is the ammount of space that this is going to use
because it is a varchar.

Tks for the help.

Ricardo

(ricardo.sobral.santos (AT) googlemail (DOT) com) writes:
You need char(36). A GUID is 128 bits, or 16 bytes. That's 32 bytes
as a string. To that comes some extra characters for the hyphens. See
this:

declare @guid uniqueidentifier
select @guid = newid()
select cast(@guid as char(36)), @guid



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx