关于保存记录时自动更新序号的问题，请大家来讨论下

Question

比如是产品的结构信息 
主表：存储产品的的信息 
字表:存储产品的构成（也就是此产品有哪些物料组成） 
例如：A产品由物料A1,A2,A3,A4,A5,A6组成 主表结构：产品号       产品名称 
                      A                   A产品 从表结构：项次     物料号     产品号 
                    1             A1             A 
                    2             A2             A 
                    3             A3             A 
                    4             A4             A 
                    5             A5             A 
                    6             A6             A 问题是： 
            （1）从表的这个“项次”字段如何添加序号值 
            （2）如果我删除项次为3的记录，如何让项次重新更新 
各位请发表下意见，多谢

Answer 1

if object_id('tb') is not null
drop table tb
go
create table tb(ItemID int, ItemName varchar(10),ProductNum varchar(4))
go--插入操作对应触发器
--不支持同时插入多条数据
create trigger OnItemInsert
On tb
Instead of insert
AS
begin 
SET NOCOUNT ON;
DECLARE @s int
SET @s = 0
select @s = isnull(max(a.ItemID),0) from tb a 
inner join inserted b on a.ProductNum=b.ProductNum
--假如不允许插入同ProductNum下，相同ItemName的重复项，则加此判断
if(not exists (select 1 from tb a inner join inserted b on a.ProductNum=b.ProductNum and a.ItemName = b.ItemName))
insert into tb(ItemID, ItemName,ProductNum)
select @s+1, a.ItemName, a.ProductNum 
from inserted a
ENDdelete from tb
insert into tb(ItemName,ProductNum)
select 'A1', 'A' 
insert into tb(ItemName,ProductNum)
select 'A1', 'A' 
insert into tb(ItemName,ProductNum)
select 'A2', 'A' 
insert into tb(ItemName,ProductNum)
select 'A3', 'A' 
insert into tb(ItemName,ProductNum)
select 'A1', 'B' 
select * from tb
/*
ItemID      ItemName   ProductNum
----------- ---------- ----------
1           A1         A
2           A2         A
3           A3         A
1           A1         B(4 行受影响)
*/--删除对应触发器
create trigger OnItemDeleted
on tb
FOR delete
as
begin 
update tb 
set ItemID = (
case when tb.ItemID>i.ItemID then tb.ItemID-1
else tb.ItemID
end
)
from deleted i
where tb.ProductNum = i.ProductNum
end
select * from tb
delete from tb where
ItemID = 1 and ItemName='A1' and ProductNum = 'A'
select * from tb
/*
ItemID      ItemName   ProductNum
----------- ---------- ----------
1           A2         A
2           A3         A
1           A1         B(3 行受影响)
*/
delete from tb where
ItemID = 2 and ItemName='A3' and ProductNum = 'A'
select * from tb
/*
ItemID      ItemName   ProductNum
----------- ---------- ----------
1           A2         A
1           A1         B(2 行受影响)
*/

Answer 2

修改插入触发器，对应同时插入多条
--插入操作对应触发器
alter trigger OnItemInsert
On tb
Instead of insert
AS
begin 
SET NOCOUNT ON;
insert into tb(ItemID, ItemName,ProductNum)
select (case when c.MaxNum is null then b.Num else c.MaxNum+b.Num end), b.ItemName, b.ProductNum 
from 
(
select Num= row_number() over (partition by a.ProductNum order by a.ProductNum), a.ItemName, a.ProductNum 
from (select distinct ItemName,ProductNum from inserted) a  --去除同时插入的重复项
where not exists(select 1 from tb d where a.ProductNum=d.ProductNum and a.ItemName = d.ItemName) --数据库中已存在该项则不再插入
) b
left outer join
(select a.ProductNum,MaxNum=isnull(max(a.ItemID),0) from tb a group by a.ProductNum) c
on c.ProductNum=b.ProductNum
ENDdelete from tb
insert into tb(ItemName,ProductNum)
select 'A1', 'A' 
union all select 'A1', 'A' 
union all select 'A2', 'A' 
union all select 'A2', 'B' 
insert into tb(ItemName,ProductNum)
select 'A3', 'A' 
insert into tb(ItemName,ProductNum)
select 'A1', 'B' 
insert into tb(ItemName,ProductNum)
select 'A1', 'B' 
select * from tb order by ProductNum,ItemID
/*
ItemID      ItemName   ProductNum
----------- ---------- ----------
1           A1         A
2           A2         A
3           A3         A
1           A2         B
2           A1         B(5 行受影响)
*/

Answer 3

如果可以做视图的话，可以考虑直接做个视图,然后rowover()

Answer 4

我已经有了实现的思路：
（1）第一次添加多条记录时：根据把记录的索引号添加到库中就行，索引号也就是序号
（2）在前台对DATATable表进行删除多行时并反映到数据库中话保持序号：记住第一个删除记录的序号NO.,将大于NO.的序号重新排序