--加一个触发器就行了.
create trigger tr_insert_update on 表
for insert,update
as
if exists(select 1 from inserted where len(序号)<6)
update 表 set 序号=right('000000'+序号,6)
where len(序号)<6
--创建得到最大id的函数
create function f_getid()
returns char(6)
as
begin
declare @id char(6)
select @id=max(id) from tb
return(case when @id is null then '000001' else right(1000001+cast(@id as bigint),6) end)
end
go
--创建触发器,在删除表中的记录时,自动更新记录的id(**如果不要此功能,则删除此触发器)
create trigger t_delete on tb
AFTER delete
as
declare @id bigint,@mid char(6)
select @mid=min(id),@id=cast(@mid as bigint)-1 from deleted
update tb set id=right(1000000+@id,6),@id=@id+1 where id>@mid
go
--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')
--显示插入的结果
select * from tb
--删除部分记录
delete from tb where name in('张五','张七','张八','张十')
--创建得到最大id的函数
create function f_getid()
returns char(6)
as
begin
declare @id char(6)
select @id=max(id) from tb
return(case when @id is null then '000001' else right(1000001+cast(@id as bigint),6) end)
end
go
--创建触发器,在删除表中的记录时,自动更新记录的id(**如果不要此功能,则删除此触发器)
create trigger t_delete on tb
AFTER delete
as
declare @id bigint,@mid char(6)
select @mid=min(id),@id=cast(@mid as bigint)-1 from deleted
update tb set id=right(1000000+@id,6),@id=@id+1 where id>@mid
go
--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')
--显示插入的结果
select * from tb
--删除部分记录
delete from tb where name in('张五','张七','张八','张十')