CODEWITHSUNDEEP
sql-serverdatabase
Ozan Manav
Ozan Manav

Reputation: 439

SQL auto-create a value column

I have a table in a SQL Server database with columns E-mail, Mobile Number, AuthCode, UserID.

When I add a new user, the AuthCode column should be filled with a value like this: 

UserID+Mobile Number.

Like this: if new user has a mobile number of 05518602015 and UserID (Autoincrement) of 2, then the AuthCode column should automatically be filled with 205518602015.

How can I do this ? I'm new to SQL Server

SOLVED : Using Triggers.

Upvotes: 1

Views: 4354

Answers (4)

Liesel
Liesel

Reputation: 2979

What about a simple computed column?

CREATE TABLE #Test (
        UserID INT IDENTITY(1,1),
        Email NVARCHAR(50), 
        MobileNumber NVARCHAR(100), 
        AuthCode AS CAST(UserID AS NVARCHAR(10)) + MobileNumber
        )


INSERT INTO #Test(Email, MobileNumber) VALUES (N'[email protected]', N'0123456790')

SELECT * FROM #Test

UserID      Email                 MobileNumber     AuthCode
----------- --------------------- ---------------- ----------------
1           [email protected]    0123456790       10123456790

(1 row(s) affected)

Upvotes: 1

gofr1
gofr1

Reputation: 15977

You can use triggers for that purpose:

CREATE TRIGGER triggername  
ON dbo.YourTableName
AFTER INSERT, UPDATE   
AS
BEGIN
    UPDATE Y
    SET AuthCode = CAST(i.UserID AS NVARCHAR(10)) + CAST(i.[Mobile Number] AS NVARCHAR(20))
    FROM YourTableName Y
    INNER JOIN inserted i ON Y.someID = i.someID
END

It will update column you need after insert/update operation on rows which was inserted.

Upvotes: 1

Shrikant Dandile
Shrikant Dandile

Reputation: 404

UPDATE dbo.City 
SET Auth = CONCAT(CONVERT(VARCHAR(10), CityId), CityName)

Above query will set data as you want you can use the condition to update particular data

Upvotes: 0

Jatin Patel
Jatin Patel

Reputation: 2104

you have to do it in two steps, add the user first and then update the AuthCode,

CREATE TABLE #Users (
        UserID INT IDENTITY(1,1),
        Email VARCHAR(100), 
        MobileNumber VARCHAR(20), 
        AuthCode VARCHAR(50)
        )

DECLARE @NewUserID      INT,
        @Email          VARCHAR(100),
        @MobileNumber   VARCHAR(20),
        @AuthCode       VARCHAR(50)

SELECT  @Email = '[email protected]'
        ,@MobileNumber = '05518602015'

-- Add new user
INSERT INTO #Users(Email, MobileNumber)
VALUES (@Email, @MobileNumber)

SET @NewUserID = SCOPE_IDENTITY()  -- this will give the Identity of the newly added row

SET @AuthCode = CAST(@NewUserID AS VARCHAR(20)) + @MobileNumber

--SELECT @AuthCode

-- update the Auth code
UPDATE u
SET AuthCode = @AuthCode
FROM #Users u
WHERE u.UserID = @NewUserID

SELECT * FROM #Users

OUTPUT

UserID      Email                     MobileNumber   AuthCode     
----------- ------------------------- -------------- -------------
1           [email protected]    05518602015    105518602015

Upvotes: 0

Related Questions