我把序号ID设置成identity(1,1),结果是自增长了。
但是如果将表中的某条记录删了,后面再插入的就错了一个数。。比如:
ID 内容
1 sjfsdfjsdkf
2 skdflksdlfsk
3 sldfksld
如果我删除了2号数据,3号数据不会自动变成2号。。
这个怎么改啊??是什么问题呢?
在线急等!!!!
但是如果将表中的某条记录删了,后面再插入的就错了一个数。。比如:
ID 内容
1 sjfsdfjsdkf
2 skdflksdlfsk
3 sldfksld
如果我删除了2号数据,3号数据不会自动变成2号。。
这个怎么改啊??是什么问题呢?
在线急等!!!!
if object_id('tb') is not null
drop table tb
go
create table tb(ItemID int , ItemName varchar(10),ProductNum varchar(4))
go--插入操作对应触发器
--修改了下,对应同时插入多条
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 行受影响)
*/--删除对应触发器
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 行受影响)
*/
create table tt(id int identity(1,1),name varchar(10))
insert into tt select 'aa'
insert into tt select 'bb'
insert into tt select 'cc'
insert into tt select 'dd'
insert into tt select 'ee'
go--创建补号添加存储过程
create proc insert_wsp
@name varchar(10)
as
set identity_insert tt on
insert into tt(id,name) select isnull(min(a.number),(select max(id)+1 from tt)),@name from master..spt_values a inner join (select mid=max(id) from tt)b on a.number between 1 and mid
left join tt on a.number=tt.id
where type='p' and tt.id is null
set identity_insert tt off
go
--删除4,5
delete tt where id>3
go--添加
exec insert_wsp 'ff'
exec insert_wsp 'gg'
exec insert_wsp 'hh'--查询结果
select * from tt
--结果:
id name
----------- ----------
1 aa
2 bb
3 cc
4 ff
5 gg
6 hh
http://blog.csdn.net/xys_777/archive/2010/07/22/5755762.aspx
非要实现删除以后ID自动重新生成的话创建个触发器。
if object_id ('tt') is not null
drop table tt
create table tt(id int identity(1,1),name varchar(10))
insert into tt select 'aa'
insert into tt select 'bb'
insert into tt select 'cc'
insert into tt select 'dd'
insert into tt select 'ee'
go
--创建触发器,删除后重新生成id
create trigger trig_id_sort on tt
for delete
as
begin
select * into #tt from tt
truncate table tt
insert into tt(name) select name from #tt
endselect * from tt
/*
id name
1 aa
2 bb
3 cc
4 dd
5 ee
*/
delete from tt where id = 2
select * from tt
/*
id name
1 aa
2 cc
3 dd
4 ee
*/
再插入是也不会短号。DBCC CHECKIDENT ('tb', RESEED, 1)
DBCC CHECKIDENT ('tb', RESEED)
如果是从中间删除的话就不好办了。
alter table tb
drop column id
alter table tb
add id int identity不知道这样会不会消耗很大