SQL表中的参考字段 [英] Reference Field in SQL Table

问题描述

大家好，

我创建了一个表，该表的int主键设置为自动增加.

但是，客户需要为每行添加一些可读键，例如PRD-12-00001，其中12是年份，而00001是相关数字.

为此，我创建了一个ID表，其中包含两个字段，即Key和Number.

我需要一个UDF来从该表中检索数字并递增该数字.

那边有样品吗?

在此先感谢
Antxon

Hi all,

I have created a table with an int primary key that is set to automatically increase.

But, customer neeeds some readable key for each row, for instance PRD-12-00001 where 12 is the year and 00001 a correlative number.

For this purpose I have created an ID table with two fields, Key and Number.

I need an UDF that retrieves the number from this table and increments this number.

Is there any sample over there?

Thanks in advance
Antxon

推荐答案

尝试类似的方法:

Try something like:

CREATE TABLE [dbo].[empWithTrigger](
        [sal] [decimal](10, 2) NULL,
        [code] [varchar](50) NULL,
        [tag] [varchar](50) NULL,
        [id] [int] IDENTITY(1,1) NOT NULL,
        [alphanumeric] [varchar](50)
)
GO

Create TRIGGER alphanumericColumn
   ON  empWithTrigger
   AFTER  INSERT
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

UPDATE a
SET    alphanumeric = 'ABC' + LEFT('00' + CAST(inserted.id AS VARCHAR(10)), 3)
FROM   empWithTrigger a
       INNER JOIN inserted
         ON a.id = inserted.id

END
GO

与此相关的讨论如下:如何自动递增字母数字主键. [

Discussion related to it here: how to auto increment alphanumeric primary key..[^]

这篇关于SQL表中的参考字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！