首先你把id字段的自增属性删除掉,不要自增
create trigger 名 on real
for delete
As
if (select count(*) from deleted) = (select count(*) from real)
select top 500 IDENTITY(int,1,1) id,* into #temp from deleted
触发器:
create trigger t_delete on Real
for delete
as
if (select count(*) from deleted)=(select count(*) from real) --判断是否全部删除
begin
select id=identity(int,1,1),* into #tb from deleted
insert into His select * from #tb where id in(select top 500 id from #tb order by id desc)
end
create trigger 名 on real
for delete
As
if (select count(*) from deleted) = (select count(*) from real)
select top 500 IDENTITY(int,1,1) id,* into #temp from deleted
create trigger 名 on real
for delete
As
if (select count(*) from deleted) = (select count(*) from real)
select top 500 IDENTITY(int,1,1) id,* into #temp from deleted
1:如果用自增字段,是不可能达到楼主所述要求的。
2:
create trigger 名 on real
for delete
As
if (select count(*) from deleted) = (select count(*) from real)
insert his select top 500 * from deleted
我在表His中设了一个字段ID,自动增加的,如何能每次增加500个记录时,重新从1开始计数
answer:
create table aa(
a int identity(1,1) not null,
b int)
go
create trigger tri_a
on aa
for insert
as
begin
declare @b int
select @b=@@identity
if (@b=5)
DBCC CHECKIDENT (aa, RESEED,0)
end
insert into aa select 2
insert into aa select 2
insert into aa select 2
insert into aa select 2
insert into aa select 2
insert into aa select 2
select * from aa