您可以为此使用而不是插入触发器
create table Cards_Types (Color nvarchar(128) primary key, Start int);
create table Cards (ID int primary key, Color nvarchar(128));
insert into Cards_Types
select 'RED', 0 union all
select 'BLUE', 50000 union all
select 'GREEN', 100000;
create trigger utr_Cards_Insert on Cards
instead of insert as
begin
insert into Cards (id, Color)
select
isnull(C.id, CT.Start) + row_number() over(partition by i.Color order by i.id),
i.Color
from inserted as i
left outer join Cards_Types as CT on CT.Color = i.Color
outer apply (
select max(id) as id
from Cards as C
where C.Color = i.Color
) as C
end
sql fiddle demo http://sqlfiddle.com/#!3/18019/1
它允许您一次插入多行:
insert into Cards (Color)
select 'GREEN' union all
select 'GREEN' union all
select 'RED' union all
select 'BLUE'
请注意,您最好在卡片列上建立索引Color, ID
.
另请注意,您只能为每种类型插入 50000 条记录。您可以使用不同的种子,例如 1 代表“红色”,2 代表“蓝色”等等,并为 预留位置,例如 100types卡片数量:
create table Cards_Types (Color nvarchar(128) primary key, Start int);
create table Cards (ID int primary key, Color nvarchar(128));
insert into Cards_Types
select 'RED', 1 union all
select 'BLUE', 2 union all
select 'GREEN', 3;
create trigger utr_Cards_Insert on Cards
instead of insert as
begin
insert into Cards (id, Color)
select
isnull(C.id, CT.Start - 100) + row_number() over(partition by i.Color order by i.id) * 100,
i.Color
from inserted as i
left outer join Cards_Types as CT on CT.Color = i.Color
outer apply (
select max(id) as id
from Cards as C
where C.Color = i.Color
) as C
end;
sql fiddle demo http://sqlfiddle.com/#!3/51ad9/1
这样,“红色”的 ID 始终以 1 结尾,“蓝色”的 ID 始终以 2 结尾,依此类推。