用一个函数来建表,再加一触发器.--函数 create function emp_getid() returns int as begin declare @id int select @id=max(id) from emp set @id=isnull(@id,0)+1 return(@id) end--表 CREATE TABLE [dbo].[emp] ( [id] [int] NOT NULL , a varchar(10) ) ON [PRIMARY] GOALTER TABLE [dbo].[emp] WITH NOCHECK ADD CONSTRAINT [PK_emp] PRIMARY KEY CLUSTERED ( [id] ) ON [PRIMARY] GOALTER TABLE [dbo].[emp] WITH NOCHECK ADD CONSTRAINT [DF__emp__id__208CD6FA] DEFAULT ([dbo].[emp_getid]()) FOR [id] GO --触发器 create trigger emp_delete on dbo.emp AFTER delete as declare @id int,@mid int select @mid=min(id),@id=@mid-1 from deleted update emp set id=@id,@id=@id+1 where id>@mid--测试数据 insert emp (a) select '10' union all select '20' union all select '30'delete emp where id =2select * from empdrop table emp drop function emp_getid/* id a ----------- 1 10 2 30*/
可以
create function emp_getid()
returns int
as
begin
declare @id int
select @id=max(id) from emp
set @id=isnull(@id,0)+1
return(@id)
end--表
CREATE TABLE [dbo].[emp] (
[id] [int] NOT NULL ,
a varchar(10)
) ON [PRIMARY]
GOALTER TABLE [dbo].[emp] WITH NOCHECK ADD
CONSTRAINT [PK_emp] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[emp] WITH NOCHECK ADD
CONSTRAINT [DF__emp__id__208CD6FA] DEFAULT ([dbo].[emp_getid]()) FOR [id]
GO
--触发器
create trigger emp_delete on dbo.emp
AFTER delete
as
declare @id int,@mid int
select @mid=min(id),@id=@mid-1 from deleted
update emp set id=@id,@id=@id+1 where id>@mid--测试数据
insert emp (a)
select '10'
union all select '20'
union all select '30'delete emp where id =2select * from empdrop table emp
drop function emp_getid/*
id a
-----------
1 10
2 30*/