Question

sql server 触发器实现数据库内的表格之间的传数

我sql server2000数据库中有两种表 有种实时，一种历史。实时的每30秒更新一次，对实时表执行的是先删除在插入。我想把实时的数据都存到历史数据表中，所有用了触发器，代码如下
CREATE TRIGGER zx506 ON [dbo].[kv_jkdata506]
FOR INSERT
AS
set identity_insert kv_jkHTR506 on
insert into kv_jkHTR506 (
[id],
[td],
[rq],
[sj],
[gswd1] ,
[gsyl1] ,
[hswd1] ,
[hsyl1] ,
[hsll1] ,
[gswd2] ,
[gsyl2] ,
[hswd2] ,
[hsyl2] ,
[bsll] ,
[kaidu] ,
[outwd]
)
select *from kv_jkdata506
set identity_insert kv_jkHTR506 off
经过一段时间查看历史表时，发现有的数据是断断续续的，有的中间隔上好几个小时没有数据。求各位大神求指点？

Answer 1

select *from kv_jkdata506

改为
select *from inserted

Answer 2

你是删除实时表中数据是更新到历史资料表中吧，那应该建立delete 触发器才对

CREATE TRIGGER zx506 ON [dbo].[kv_jkdata506]
FOR delete　--删除时触发插入到历史资料

AS
set identity_insert kv_jkHTR506 on
insert into kv_jkHTR506 (
[id],
[td],
[rq],
[sj],
[gswd1] ,
[gsyl1] ,
[hswd1] ,
[hsyl1] ,
[hsll1] ,
[gswd2] ,
[gsyl2] ,
[hswd2] ,
[hsyl2] ,
[bsll] ,
[kaidu] ,
[outwd]
)
select * from deleted --此处应为删除的资料

set identity_insert kv_jkHTR506 off