create table A ( ID bigint, Name nvarchar(20) ) create table B ( A_ID bigint, Item nvarchar(20) ) go--创建视图 create view v_A_B as select B.A_ID, A.Name, B.Item from A join B on A.ID=B.A_ID go--建立触发器 create trigger tr_insert on v_A_B instead of insert as begin tran insert A select A_ID,Name from insertedinsert B select A_ID,Item from insertedif @@error=0 begin commit tran end else begin rollback tran raiserror('数据插入失败',12,1) end go--建立update触发器 create trigger tr_update on v_A_B instead of update as begin tran update A set Name=I.Name from A join inserted I on A.ID=I.A_IDupdate B set Item=I.Item from B join inserted I on B.A_ID=I.A_ID
if @@error=0 begin commit tran end else begin rollback tran raiserror('数据更新失败',12,1) end go--测试 insert v_A_B select 100,'AAAAAA','BBBBBB'select * from A select * from Bupdate v_A_B set Name='CCCCCC'select * from A select * from B--删除测试环境 drop view v_A_B drop table A,B
思路是使用触发器来实现: --创建视图 create view v_A_B as select A_Id,Name,Item from A,B where A.ID=B.A_Id--insert触发器 create trigger trg_ins_v_A_B on v_A_B for insert as insert into A(ID,Name) select A_Id,Name from inserted insert into B(A_ID,Item) select A_Id,Item from inserted go--delete触发器 create trigger trg_del_v_A_B on v_A_B for delete as delete from A where A.ID in (select A_Id from deleted) delete from B where B.A_ID in (select A_Id from deleted)--update触发器 create trigger trg_upd_v_A_B on v_A_B for update as update A set Name=d.Name from inserted d where d.A_Id=ID update B set Item=d.Item from inserted d where d.A_Id=B.A_Id
(
ID bigint,
Name nvarchar(20)
)
create table B
(
A_ID bigint,
Item nvarchar(20)
)
go--创建视图
create view v_A_B
as
select B.A_ID,
A.Name,
B.Item
from A
join B on A.ID=B.A_ID
go--建立触发器
create trigger tr_insert
on v_A_B
instead of insert
as
begin tran
insert A
select A_ID,Name from insertedinsert B
select A_ID,Item from insertedif @@error=0
begin
commit tran
end
else
begin
rollback tran
raiserror('数据插入失败',12,1)
end
go--建立update触发器
create trigger tr_update
on v_A_B
instead of update
as
begin tran
update A set Name=I.Name
from A join inserted I on A.ID=I.A_IDupdate B set Item=I.Item
from B join inserted I on B.A_ID=I.A_ID
if @@error=0
begin
commit tran
end
else
begin
rollback tran
raiserror('数据更新失败',12,1)
end
go--测试
insert v_A_B select 100,'AAAAAA','BBBBBB'select * from A
select * from Bupdate v_A_B set Name='CCCCCC'select * from A
select * from B--删除测试环境
drop view v_A_B
drop table A,B
--创建视图
create view v_A_B
as
select A_Id,Name,Item
from A,B
where A.ID=B.A_Id--insert触发器
create trigger trg_ins_v_A_B
on v_A_B for insert
as
insert into A(ID,Name) select A_Id,Name from inserted
insert into B(A_ID,Item) select A_Id,Item from inserted
go--delete触发器
create trigger trg_del_v_A_B
on v_A_B for delete
as
delete from A where A.ID in (select A_Id from deleted)
delete from B where B.A_ID in (select A_Id from deleted)--update触发器
create trigger trg_upd_v_A_B
on v_A_B for update
as
update A set Name=d.Name from inserted d where d.A_Id=ID
update B set Item=d.Item from inserted d where d.A_Id=B.A_Id