带前缀自动生成的员工编号 [英] employee id automatic generate with prefix

问题描述

我很困惑，我希望员工ID自动以前缀格式
i生成，并且知道在sql server
i中触发之前是可能的，请遵循帖子
http://www.aspdotnet-suresh.com/2012/04/set-custom -auto- generate-increment.html

i am so confuse i want employee id is automatic generate with prefix format i know that is possible from before trigger in sql server i am follow the post http://www.aspdotnet-suresh.com/2012/04/set-custom-auto-generatedincrement.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

如何自动创建在SQL中具有以下系列A001000001 ... A001xxxxxx的主键？

但我想从触发器
之前的插入操作中完成此操作，并且此触发器在同一表和同一行
中起作用-

but i want to do it from insert before trigger and this trigger work in same table and same row ex -

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

和自动创建的用户ID？就像--08U13015,08U13014

and userid automatic created ? like --08U13015,08U13014

我是通过插入插入表来完成的，但无法在插入或删除的表中插入错误

或其他方法
请先帮助我

i was doing it from insert into inserted table but error can not insert in inserted or deleted table
or any other method please help me thanks in advance

推荐答案

@marc_s 的问题是解决方法。

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

In your case it would look like this:

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)
)

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

I would not do it like suggested here. You will have duplicates if you ever delete a row or in case of concurrency.

SQL小提琴

这篇关于带前缀自动生成的员工编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！