create table t306(id int,f3 varchar(10),f38 varchar(10)) create table t55(id int,f1 varchar(10)) GOCREATE TRIGGER gz ON [dbo].[T306] FOR INSERT AS insert t55(ID,f1) select id ,f3 from inserted where F38='党员' GOinsert into t306 select 225,'张','党员' insert into t306 select 266,'刘','群众' insert into t306 select 248,'王','党员' GOselect * from t55 GO/* id f1 ----------- ---------- 225 张 248 王 */ drop TRIGGER gz drop table t306,t55 GO
也可以这样写,不过随着数据量的增加,性能将不怎么样:CREATE TRIGGER gz ON [dbo].[T306] FOR INSERT AS insert t55(ID,f1) select id,f3 from T306 where F38='党员' and not exists(select 1 from t55 where ID=T306.ID and f1=T306.f1) GO
多谢子陌兄侠骨柔肠,同时也拜谢新的一天,小弟感激不尽! 在用了子陌inserted方法后以解决问题,(可将新增的数据插入t55表中且不重复,)但是前期(编写触发器前)输入的数据 (225 张 党员 ,248 王 党员)不能带回,如若数据量较多的维护后期势必造成信息丢失,不知子陌可有两全之法! 而用过not exists(select 1 from t55 where ID=T306.ID and f1=T306.f1)方法后,会有列名无效之错,不知为何,可能为小弟还为参误,
考虑以下做法: CREATE TRIGGER gz ON [dbo].[T306] FOR INSERT AS insert t55(ID,f1) select id,f3 from T306 where F38='党员' and id not in(select distinct id from t55)
GO 以前输入数据只能用重新INSERT才可以 insert into t55 select id,f3 from T306 where F38='党员' and id not in(select distinct id from t55)
楼上仁兄,方法很好,问题圆满解决,多谢, 建议各位xdjm一同学习
not exists(select 1 from t55 where ID=T306.ID and f1=T306.f1)改成 not exists(select 1 from t55 where ID=T306.ID and f1=T306.f3) lz似乎没理解语句里的意思 已经存在的重复数据可以用语句剔掉,参考一下语句 alter table dbo.tb1 add autoid int identity(1,1)--增加自动编号字段 go delete dbo.tb1 --删除 where autoid not in( select min(autoid) from dbo.tb1 group by clo...)--...是autoid外所有字段 go alter table dbo.tb1 drop column autoid--删除自动编号字段
CREATE TRIGGER gz ON [dbo].[T306] FOR INSERT AS insert t55(ID,f1) select id,f3 from T306 where F38='党员' and not exists(select 1 from t55 where ID=T306.ID and f1=T306.f3) --此处修改为f3 GO另外,如果存在数据遗失的情况,可以在查询分析器中写SQL把前期写入T306的数据追加到T55: insert t55(ID,f1) select id ,f3 from inserted where F38='党员'
create table t55(id int,f1 varchar(10))
GOCREATE TRIGGER gz ON [dbo].[T306]
FOR INSERT
AS
insert t55(ID,f1)
select id ,f3 from inserted where F38='党员'
GOinsert into t306 select 225,'张','党员'
insert into t306 select 266,'刘','群众'
insert into t306 select 248,'王','党员'
GOselect * from t55
GO/*
id f1
----------- ----------
225 张
248 王
*/
drop TRIGGER gz
drop table t306,t55
GO
FOR INSERT
AS
insert t55(ID,f1)
select
id,f3
from
T306
where
F38='党员'
and
not exists(select 1 from t55 where ID=T306.ID and f1=T306.f1)
GO
在用了子陌inserted方法后以解决问题,(可将新增的数据插入t55表中且不重复,)但是前期(编写触发器前)输入的数据 (225 张 党员 ,248 王 党员)不能带回,如若数据量较多的维护后期势必造成信息丢失,不知子陌可有两全之法!
而用过not exists(select 1 from t55 where ID=T306.ID and f1=T306.f1)方法后,会有列名无效之错,不知为何,可能为小弟还为参误,
CREATE TRIGGER gz ON [dbo].[T306]
FOR INSERT
AS
insert t55(ID,f1)
select
id,f3
from
T306
where
F38='党员'
and
id not in(select distinct id from t55)
GO
以前输入数据只能用重新INSERT才可以
insert into t55
select
id,f3
from
T306
where
F38='党员'
and
id not in(select distinct id from t55)
建议各位xdjm一同学习
not exists(select 1 from t55 where ID=T306.ID and f1=T306.f3)
lz似乎没理解语句里的意思
已经存在的重复数据可以用语句剔掉,参考一下语句
alter table dbo.tb1 add autoid int identity(1,1)--增加自动编号字段
go
delete dbo.tb1 --删除
where autoid not in(
select min(autoid) from dbo.tb1 group by clo...)--...是autoid外所有字段
go
alter table dbo.tb1 drop column autoid--删除自动编号字段
FOR INSERT
AS
insert t55(ID,f1)
select
id,f3
from
T306
where
F38='党员'
and
not exists(select 1 from t55 where ID=T306.ID and f1=T306.f3) --此处修改为f3
GO另外,如果存在数据遗失的情况,可以在查询分析器中写SQL把前期写入T306的数据追加到T55:
insert t55(ID,f1) select id ,f3 from inserted where F38='党员'