/*-------------------------
*问题描述:表中采用了自增列删除记录会出现断号的情况,但是又想让它删除后自动重新编号?
*方法1:采用函数+触发器的方法替代IDENTITY的功能,并且解决断号问题
*方法2:采用存储过程(还是使用IDENTITY列)
------------------------------------*/
--创建实现自增列的函数
create function f_getID()
returns int
as
begin
return(select isnull(max(id),0)+1 from tb)
end
go
--创建表
create table tb(id int primary key default dbo.f_getID() ,name varchar(20))
go
--创建触发器,在删除表中的记录时,自动更新记录的id,解决断号的烦恼
create trigger t_s_duanhao on tb
AFTER delete
as
declare @mid int
select @mid=min(id)-1
from deleted
update tb
set id=@mid,@mid=@mid+1
where id>@mid+1
go
--插入记录测试(只能一条条记录插入)
insert into tb(name) values('1哥')
insert into tb(name) values('2哥')
insert into tb(name) values('3哥')
insert into tb(name) values('4哥')
insert into tb(name) values('5哥')
insert into tb(name) values('6哥')
insert into tb(name) values('7哥')
insert into tb(name) values('8哥')
--显示插入的结果
select * from tb
--删除部分记录
delete from tb where name in('1哥','4哥','7哥','8哥')
--显示删除后的结果
select * from tb
/*--测试结果
id name
----------- --------------------
1 2哥
2 3哥
3 5哥
4 6哥
--*/
--删除环境
drop table tb
drop function f_getID
--方法:存储过程解决断号烦恼--pt兄
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
--删除,3
delete tt where id=2 or id=3
go
--添加
exec insert_wsp 'ff'
exec insert_wsp 'gg'
exec insert_wsp 'hh'
--查询结果
select * from tt
drop table tt
drop proc insert_wsp