我可以在插入上创建一个触发器，它将为SQL Server 2012中的键生成一个随机数吗？

I have the following table definition:

我有以下表定义：

        CREATE TABLE Content (
            [ContentId] INT IDENTITY (1, 1) NOT NULL,
            [Title] NVARCHAR (50) Not NULL,
            CONSTRAINT [PK_Content] PRIMARY KEY CLUSTERED ([ContentId] ASC)
        )";

Instead of an identity column I need to generate a random 5 digit number for the ContentId and for this number to have not been previously used.

我需要为ContentId生成一个随机的5位数字，而不是先前使用过的数字，而不是标识列。

Is there a way I can do this with some kind of database trigger for an insert in SQL Server 2012 ?

有没有办法在SQL Server 2012中使用某种数据库触发器进行插入？

2 个解决方案

#1

You are only dealing with 100,000 values. My suggestion is to create a lookup table for mapping an auto-incremented id to a new id. Here is the code for creating such a table:

您只处理100,000个值。我的建议是创建一个查找表，用于将自动递增的id映射到新的id。以下是创建此类表的代码：

with nums as (
      select 0 as n
      union all
      select n + 1
      from nums
      where n < 9
     ),
     nums5 as (
      select n1.n*10000+n2.n*1000+n3.n*100+n4.n+10+n5.n as val
      from nums n1 cross join nums n2 cross join nums n3 cross join
           nums n4 cross join nums n5
    )
select val,
       row_number() over (order by newid()) as new_content_id
into LookupTable
from nums5;

With this table, put an auto-incremented id in the table and then lookup the five character "new_content_id" from this table.

使用此表，在表中放置一个自动递增的id，然后从该表中查找五个字符“new_content_id”。

#2

This can be a good starting point to do what you want :

这可以是你想做的事情的一个很好的起点：

SQL Fiddle

SQL小提琴

MS SQL Server 2012 Schema Setup:

MS SQL Server 2012架构设置：

CREATE TABLE Content (
            [ContentId] INT NOT NULL,
            [Title] NVARCHAR (50) Not NULL,
            CONSTRAINT [PK_Content] PRIMARY KEY CLUSTERED ([ContentId] ASC)
        );

Query 1:

查询1：

DECLARE @key VARCHAR(5), @i int
DECLARE @query VARCHAR(120)
SET @i = 1
WHILE @i > 0
BEGIN
    SET @key = (SELECT ABS(Checksum(NewID()) % 89999) + 10000)
    SET @i = (SELECT count(*) FROM Content WHERE ContentId = @key)
END
SET @query = 'INSERT INTO Content (ContentId,Title) VALUES ('+@key+',''Whatever'+@key+''');'
exec(@query)

Results:

结果：

Query 2:

查询2：

DECLARE @key VARCHAR(5), @i int
DECLARE @query VARCHAR(120)
SET @i = 1
WHILE @i > 0
BEGIN
    SET @key = (SELECT ABS(Checksum(NewID()) % 89999) + 10000)
    SET @i = (SELECT count(*) FROM Content WHERE ContentId = @key)
END
SET @query = 'INSERT INTO Content (ContentId,Title) VALUES ('+@key+',''Whatever'+@key+''');'
exec(@query)

Results:

结果：

Query 3:

查询3：

select * from Content

Results:

结果：

| CONTENTID |         TITLE |
|-----------|---------------|
|     22537 | Whatever22537 |
|     66089 | Whatever66089 |