SQL自动创建一个值列

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

我在SQL Server数据库中有一个表，其中包含列电子邮件、移动号码、AuthCode、UserID。

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

添加新用户时，AuthCode列应该填入如下值:

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.

如下图所示:如果新用户的手机号为05518602015,UserID (Autoincrement)为2，则AuthCode列自动填上205518602015。

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

我该怎么做呢?我是SQL Server新手

SOLVED : Using Triggers.

解决:使用触发器。

4 个解决方案

#1

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.

它将在插入的行上进行插入/更新操作之后更新所需的列。

#2

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'[e-mail]', N'0123456790')

SELECT * FROM #Test

UserID      Email                 MobileNumber     AuthCode
----------- --------------------- ---------------- ----------------
1           [e-mail]    0123456790       10123456790

(1 row(s) affected)

#3

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

你需要做两步，首先添加用户，然后更新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 = '[e-mail]'
        ,@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           [e-mail]    05518602015    105518602015

#4

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

上述查询将根据需要设置数据，您可以使用该条件更新特定数据