一个触发器的简单问题？在线等待

[yjy7604]

我建立了两个表Real和His，当Real中记录被全部删除时，把最后500个记录另存到His表中，触发器如何写？希望详细一点，我很菜。另外，我在表His中设了一个字段ID，自动增加的，如何能每次增加500个记录时，重新从1开始计数？多谢了！！

[yjy7604]

我刚试了一下，错误如下：SQL无法使用SELECT INTO语句向表#'tb'中添加标识列，该表中已有继承了标识属性的列‘ID’，问题不知如何解决？

[welyngj]

我在表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

[txlicenhe]

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

[yujohny]

你不要设为自动增加字段，自己来控制
这样你可以这样写触发器

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

insert his
select * from #temp

--这样写触发器，每次插入的ID都是1开始

[yujohny]

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

insert his
select * from #temp

drop table #temp

[yjy7604]

我试了一下，没有写进his 表中。

[zjcxc]

如何能每次增加500个记录时，重新从1开始计数？多谢了！！
标识字段不能做这样的效果,你不要用标识字段.直接用整型字段就行了.

触发器:
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

[yjy7604]

多谢大家了，我的问题虽然没有解决，可能是没有表达清楚。我把问题重新整理出来，盼望高手的指点。

[yujohny]

其实我们的答案早就解决了你的问题，只是你不懂使用而已

首先你把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

insert his select * from #temp