How to use multiple identity numbers in one table?
- by vincer
I have an web application that creates printable forms, these forms have a unique number on them, the problem is I have 2 forms that separate numbers need to be created for them.
ie)
Form1- Numbered 2000000-2999999
Form2- Numbered 3000000-3999999
dbo.test2 - is my form information table
Tsel - is my autoinc table for the 3000000 series numbers
Tadv - is my autoinc table for the 2000000 series numbers
What I have done is create 2 tables with just autoinc row (one for 2000000 series numbers and one for 3000000 series numbers), I then created a trigger to add a record to the coresponding table, read back the autoinc number and add it to my table that stores the form information including the just created autoinc number for the right series of forms.
Although it does work, I'm concerned that the numbers will get messed up under load.
I'm not sure the @@IDENTITY will always return the right value when many people are using the system. (I cannot have duplicates and I need to use the numbering form show above.
See code below.
** TRIGGER **
CREATE TRIGGER MAKEANID2 ON dbo.test2
AFTER INSERT
AS
SET NOCOUNT ON
declare @someid int
declare @someid2 int
declare @startfrom int
declare @test1 varchar(10)
select @someid=@@IDENTITY
select @test1 = (Select name1 from test2 where sysid = @someid )
if @test1 = 'select'
begin
insert into Tsel Default values
select @someid2 = @@IDENTITY
end
if @test1 = 'adv'
begin
insert into Tadv Default values
select @someid2 = @@IDENTITY
end
update test2
set name2=(@someid2) where sysid = @someid
SET NOCOUNT OFF
