CODEWITHSUNDEEP
sql-server
user1943020
user1943020

Reputation:

Can I create a trigger on insert that will generate a random number for a key in 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.

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

Upvotes: 0

Views: 886

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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:

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.

Upvotes: 2

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

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

SQL Fiddle

MS SQL Server 2012 Schema Setup:

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

Query 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:

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:

select * from Content

Results:

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

Upvotes: 1

Related Questions