带前缀自动生成的员工编号

一尘不染

带前缀自动生成的员工编号

sql

我很困惑，我希望员工ID以前缀格式自动生成，我知道从sql服务器中的触发器触发之前是可能的，我按照帖子 http://www.aspdotnet-
suresh.com/2012/04/set-custom-自动生成的increment.html

USE [test1]
GO

/****** Object:  Table [dbo].[Users]    Script Date: 03/08/2013 12:28:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Users](
    [UserId] [varchar](50) NOT NULL,
    [UserName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [Location] [varchar](50) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
    [UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

显示器是

    UserId  UserName    LastName    Location
08U13000    SureshDasari    Dasari  Chennai
08U13001    SureshDasari    Dasari  Chennai
08U13002    SureshDasari    Dasari  Chennai
08U13003    SureshDasari    Dasari  Chennai
08U13004    SureshDasari    Dasari  Chennai
08U13005    SureshDasari    Dasari  Chennai
08U13006    SureshDasari    Dasari  Chennai
08U13007    SureshDasari    Dasari  Chennai
08U13008    SureshDasari    Dasari  Chennai
08U13009    SureshDasari    Dasari  Chennai
08U13010    SureshDasari    Dasari  Chennai
08U13011    SureshDasari    Dasari  Chennai
08U13012    SureshDasari    Dasari  Chennai
08U13013    SureshDasari    Dasari  Chennai
08U13014    SureshDasari    Dasari  Chennai
08U13015    SureshDasari    Dasari  Chennai

但是我想从触发器之前的插入中执行此操作，并且此触发器在同一表和同一行中工作-

INSERT INTO Users (UserName,LastName,Location) VALUES('SureshDasari','Dasari','Chennai')

和用户ID自动创建？喜欢–08U13015,08U13014

我是从插入插入表中执行此操作，但无法在插入或删除的表中插入错误
或任何其他方法，请事先帮助我

阅读 279

2021-03-08

共1个答案

一尘不染

@marc_s在问题中提供的答案就是做到这一点的方法。

在您的情况下，它看起来像这样：

create table Users
(
  Id int identity (3000, 1),
  UserId as '08U1'+right('0000'+cast(Id as varchar(5)), 5) persisted,
  UserName varchar(50),
  LastName varchar(50),
  Location varchar(50),
  constraint PK_Users primary key (UserId)
)

我不会像这里建议的那样做。如果删除行或并发，将有重复项。

SQL小提琴

2021-03-08