我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
经过一段时间查看历史表时,发现有的数据是断断续续的,有的中间隔上好几个小时没有数据。邹建大哥和各位大神求指点?
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
经过一段时间查看历史表时,发现有的数据是断断续续的,有的中间隔上好几个小时没有数据。邹建大哥和各位大神求指点?
drop trigger tri_test
go
create trigger tri_test on 事实表
instead of insert
as
--吧数据放入历史表
insert 历史表
select * from 事实表
--删除事实表中的旧的数据
delete from 事实表 where 1=1
insert 事实表
select * from inserted
你会也设置了主键吧那有重复id 是查不进去的。
语句改一下改成
insert into kv_jkHTR506 (
[id],
[td],
[rq],
[sj],
[gswd1] ,
[gsyl1] ,
[hswd1] ,
[hsyl1] ,
[hsll1] ,
[gswd2] ,
[gsyl2] ,
[hswd2] ,
[hsyl2] ,
[bsll] ,
[kaidu] ,
[outwd]
)
select * from inserted
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 inserted
set identity_insert kv_jkHTR506 off