|
|
Posted by Erland Sommarskog on 10/10/07 21:56
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(UserID, 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_aspnet_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@sommarskog.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
[Back to original message]
|