select table1.*,table2.g from table1 join table2 on table1.a=table2.a
楼上的方法建立完以后SELECT *会出现两条重复数据,怎么解决,不让他重复
--视图 create view 视图名 as select a.*,b.g from 表1 a join 表2 b on a.id=b.id
--如果以后更新删除表1和表2的内容要操作视图来进行,就写下面的触发器--处理的触发器 create trigger tr_process on 视图名 instead of insert,update,delete as if exists(select 1 from inserted) begin if exists(select 1 from deleted) begin update 表1 set a=i.a,b=i.b,c=i.c,d=i.d from 表1 a join inserted i on a.id=i.id update 表2 set g=i.g from 表2 a join inserted i on a.id=i.id end insert 表1 select a,b,c,d from inserted insert 表2 select g from inserted end else begin delete 表1 from 表1 a join deleted d on a.id=d.id delete 表2 from 表2 a join deleted d on a.id=d.id end go
--看错了,楼主是通过A来关联的,改一下: create view 视图名 as select a.*,b.g from 表1 a join 表2 b on a.a=b.b go
--上面有误,修正:--视图 create view 视图名 as select a.*,b.g from 表1 a join 表2 b on a.a=b.a go--处理的触发器 create trigger tr_process on 视图名 instead of insert,update,delete as if exists(select 1 from inserted) if exists(select 1 from deleted) begin select id=identity(int,1,1),a,b,c,d,g into #i from inserted select id=identity(int,1,1),a into #d from deleted update 表1 set a=i_a,b=i_b,c=i_c,d=i_d from 表1 a join( select i_a=i.a,i_b=i.b,i_c=i.c,i_d=i.d,d_a=d.a from #i i join #d d on i.id=d.id )b on a.a=d_a update 表2 set a=i_a,g=i_g from 表2 a join( select i_a=i.a,i_g=i.g,d_a=d.a from #i i join #d d on i.id=d.id )b on a.a=d_a end else begin insert 表1(a,b,c,d) select a,b,c,d from inserted insert 表2(a,g) select a,g from inserted end else begin delete 表1 from 表1 a join deleted d on a.a=d.a delete 表2 from 表2 a join deleted d on a.a=d.a end go
--测试--测试数据 create table 表1(ID int identity(1,1),A varchar(10),B varchar(10),C varchar(10),D varchar(10) ,CONSTRAINT idx_表1_a UNIQUE(a)) insert 表1 select 'a', 'b','c','d' union all select 'a2','b','c','d2' union all select 'a3','c','b','a'create table 表2(ID int identity(1,1),E varchar(10),A varchar(10),F varchar(10),G varchar(10) ,CONSTRAINT idx_表2_a UNIQUE(a)) insert 表2 select 'm','a' ,'w','q' union all select 'x','a2','p','k' union all select 'u','a3','i','r' go--视图 create view 视图名 as select a.*,b.g from 表1 a join 表2 b on a.a=b.a go--处理的触发器 create trigger tr_process on 视图名 instead of insert,update,delete as if exists(select 1 from inserted) if exists(select 1 from deleted) begin select id=identity(int,1,1),a,b,c,d,g into #i from inserted select id=identity(int,1,1),a into #d from deleted update 表1 set a=i_a,b=i_b,c=i_c,d=i_d from 表1 a join( select i_a=i.a,i_b=i.b,i_c=i.c,i_d=i.d,d_a=d.a from #i i join #d d on i.id=d.id )b on a.a=d_a update 表2 set a=i_a,g=i_g from 表2 a join( select i_a=i.a,i_g=i.g,d_a=d.a from #i i join #d d on i.id=d.id )b on a.a=d_a end else begin insert 表1(a,b,c,d) select a,b,c,d from inserted insert 表2(a,g) select a,g from inserted end else begin delete 表1 from 表1 a join deleted d on a.a=d.a delete 表2 from 表2 a join deleted d on a.a=d.a end go--显示视图 select * from 视图名 go--测试触发器 update 视图名 set b=b+'bb',g=g+'gg' insert 视图名 select 1,'aa','b','c','d','g' delete 视图名 where a='a2' go--显示处理结果 select * from 视图名 go--删除测试 drop view 视图名 drop table 表1,表2/*--测试结果--视图的效果 ID A B C D g ----------- ---------- ---------- ---------- ---------- ---------- 1 a b c d q 2 a2 b c d2 k 3 a3 c b a r(所影响的行数为 3 行) --触发器处理的效果 ID A B C D g ----------- ---------- ---------- ---------- ---------- ---------- 1 a bbb c d qgg 3 a3 cbb b a rgg 4 aa b c d g(所影响的行数为 3 行)--*/
create view 视图名
as
select a.*,b.g
from 表1 a join 表2 b on a.id=b.id
create trigger tr_process on 视图名
instead of insert,update,delete
as
if exists(select 1 from inserted)
begin
if exists(select 1 from deleted)
begin
update 表1 set a=i.a,b=i.b,c=i.c,d=i.d
from 表1 a join inserted i on a.id=i.id
update 表2 set g=i.g
from 表2 a join inserted i on a.id=i.id
end
insert 表1 select a,b,c,d from inserted
insert 表2 select g from inserted
end
else
begin
delete 表1 from 表1 a join deleted d on a.id=d.id
delete 表2 from 表2 a join deleted d on a.id=d.id
end
go
create view 视图名
as
select a.*,b.g
from 表1 a join 表2 b on a.a=b.b
go
create view 视图名
as
select a.*,b.g
from 表1 a join 表2 b on a.a=b.a
go--处理的触发器
create trigger tr_process on 视图名
instead of insert,update,delete
as
if exists(select 1 from inserted)
if exists(select 1 from deleted)
begin
select id=identity(int,1,1),a,b,c,d,g into #i from inserted
select id=identity(int,1,1),a into #d from deleted
update 表1 set a=i_a,b=i_b,c=i_c,d=i_d
from 表1 a join(
select i_a=i.a,i_b=i.b,i_c=i.c,i_d=i.d,d_a=d.a
from #i i join #d d on i.id=d.id
)b on a.a=d_a update 表2 set a=i_a,g=i_g
from 表2 a join(
select i_a=i.a,i_g=i.g,d_a=d.a
from #i i join #d d on i.id=d.id
)b on a.a=d_a
end
else
begin
insert 表1(a,b,c,d) select a,b,c,d from inserted
insert 表2(a,g) select a,g from inserted
end
else
begin
delete 表1 from 表1 a join deleted d on a.a=d.a
delete 表2 from 表2 a join deleted d on a.a=d.a
end
go
create table 表1(ID int identity(1,1),A varchar(10),B varchar(10),C varchar(10),D varchar(10)
,CONSTRAINT idx_表1_a UNIQUE(a))
insert 表1 select 'a', 'b','c','d'
union all select 'a2','b','c','d2'
union all select 'a3','c','b','a'create table 表2(ID int identity(1,1),E varchar(10),A varchar(10),F varchar(10),G varchar(10)
,CONSTRAINT idx_表2_a UNIQUE(a))
insert 表2 select 'm','a' ,'w','q'
union all select 'x','a2','p','k'
union all select 'u','a3','i','r'
go--视图
create view 视图名
as
select a.*,b.g
from 表1 a join 表2 b on a.a=b.a
go--处理的触发器
create trigger tr_process on 视图名
instead of insert,update,delete
as
if exists(select 1 from inserted)
if exists(select 1 from deleted)
begin
select id=identity(int,1,1),a,b,c,d,g into #i from inserted
select id=identity(int,1,1),a into #d from deleted
update 表1 set a=i_a,b=i_b,c=i_c,d=i_d
from 表1 a join(
select i_a=i.a,i_b=i.b,i_c=i.c,i_d=i.d,d_a=d.a
from #i i join #d d on i.id=d.id
)b on a.a=d_a update 表2 set a=i_a,g=i_g
from 表2 a join(
select i_a=i.a,i_g=i.g,d_a=d.a
from #i i join #d d on i.id=d.id
)b on a.a=d_a
end
else
begin
insert 表1(a,b,c,d) select a,b,c,d from inserted
insert 表2(a,g) select a,g from inserted
end
else
begin
delete 表1 from 表1 a join deleted d on a.a=d.a
delete 表2 from 表2 a join deleted d on a.a=d.a
end
go--显示视图
select * from 视图名
go--测试触发器
update 视图名 set b=b+'bb',g=g+'gg'
insert 视图名 select 1,'aa','b','c','d','g'
delete 视图名 where a='a2'
go--显示处理结果
select * from 视图名
go--删除测试
drop view 视图名
drop table 表1,表2/*--测试结果--视图的效果
ID A B C D g
----------- ---------- ---------- ---------- ---------- ----------
1 a b c d q
2 a2 b c d2 k
3 a3 c b a r(所影响的行数为 3 行)
--触发器处理的效果
ID A B C D g
----------- ---------- ---------- ---------- ---------- ----------
1 a bbb c d qgg
3 a3 cbb b a rgg
4 aa b c d g(所影响的行数为 3 行)--*/
真是猛啊