SQLTeam.com | Weblogs | Forums

Auto-add two columns to a new column when create new user


down vote
I want to ask something about in MSSQL database.

I have a database one and it has E-mail,Mobile Number,AuthCode,UserID.

I want to that ; When i add new user AuthCode Column will be like this : UserID+Mobile Number.

Like this : if new user has 05518602015 mobile number and UserID(Autoincrement)=2 Automatically AuthCode Columun will be create like : 205518602015

How can i do this ? I'm new sql server


First you insert without authcode:

insert into yourtable ([e-mail],[mobile number],authcode)
values ('users emailaddress','05518602015','not set yet')

Immediately after inserting, you update authcode like this:

update yourtable
   set authcode=cast(userid as varchar(10))+[mobile number]
 where userid=@@identity


Just in case a trigger is ever added to the table or some other oddity occurs, I recommend that you use SCOPE_IDENTITY() instead of @@IDENTITY.