SQL Server 2005 database - Problem

[Zeljko Bilandzija]

Hello!

I have a problem, and I looking for help if someone can handle this.
I use asp.net 2.0, and I create web site which support users from internet
(Web Site Administration Tool) and with that option i get database named
ASPNETDB.MDF.
When someone via page is creating account, database creates a new record in
aspnet_users table.
I made another table (table User_Data, columns UserID, Level, Points, ...),
and i want that these data are set to some default values automatically when
user press Create Account button (something like this, Columns Level to 1,
column Points to 0, ...)
Which relationship i must use between two tables, and what else i must to do
to be able make this.
I made relathionship FK_aspnet_Users _Users_Data, and set UPDATE statement to
Set Default, and i was set default values for columns in User_Data table,
and when user press Create Account on page, i get error.

I don't know if you understand what i want, but i want to, when user create
new account, that data in other tables are populated automatically for that
user, with some default values, that i specified earlier.

Thanks

[Erland Sommarskog]

Re: SQL Server 2005 database - Problem

Zeljko Bilandzija (zac9393@email. t-com.hr) writes:

I have a problem, and I looking for help if someone can handle this.
I use asp.net 2.0, and I create web site which support users from internet
(Web Site Administration Tool) and with that option i get database named
ASPNETDB.MDF.
When someone via page is creating account, database creates a new record
in aspnet_users table.
I made another table (table User_Data, columns UserID, Level,
Points, ...), and i want that these data are set to some default values
automatically when user press Create Account button (something like
this, Columns Level to 1, column Points to 0, ...)
>
Which relationship i must use between two tables, and what else i must
to do to be able make this.
>
I made relathionship FK_aspnet_Users _Users_Data, and set UPDATE
statement to Set Default, and i was set default values for columns in
User_Data table, and when user press Create Account on page, i get
error.
>
I don't know if you understand what i want, but i want to, when user
create new account, that data in other tables are populated
automatically for that user, with some default values, that i specified
earlier.

You would need a trigger to add data to User_Data when a new row is
added to aspnet_users:

CREATE TRIGGER add_user_data ON aspnet_Users FOR INSERT AS
INSERT User_Data(UserI D, Level, Points, ...)
SELECT UserID, 1, 0, --- more defaults goes here
FROM inserted

What you set up was a foreign-key relation, which you indeed should have
one, but it serves a different purpose. When you do:

ALTER TABLE user_data ADD
CONSTRAINT fk_user_data_as pnet_users FOREIGN KEY(UserID)
REFERENCES aspnet_Users (UserID)

you state that whenever a row is added to user_data, there must be a
row in aspnet_Users with that UserID. As written above, the constraint
also prohibits deleting a row in aspnet_users if there is a referencing
UserID in user_data. Same applies if you try to change the user id in
aspnet_Users.

When you added:

ON UPDATE SET DEFAULT

this changes what happens if you update a UserID in aspnet_Users. Instead
of getting an error, the referencing UserID in User_data will be set to
its default value, if it has one. This is a very rare thing to do.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx