create view 视图 as select Table1.A1,Table1.B1,Table1.C1,Table2.A2,Table2.B2,Table2.C2,Table3.A3,Table3.B3,Table3.C3 from table1,table2,table3 where Table1.A1=Table2.A2;Table1.B1=Table3.A3 goCREATE TRIGGER 名 on 视图 INSTEAD OF update AS BEGIN update table1 set c1=inserted.c1 from inserted where inserted.a1=table1.a1 and inserted.b1=table1.b1 update table2 set b2=inserted.b2,c2=inserted.c2 from inserted where inserted.a2=table1.a2 update table3 set a3=inserted.a3,c3=inserted.c3 from inserted where inserted.b3=table1.b3 END goupdate 视图 set C1='xxx',B2='xxx',C2='xxx',A3='xxx',C3='xxx' where a1='xx' and b1='yy'
一个更新语句只能更新一个表,要用三条更新语句:begin tran --利用事务处理,保证三张表都会被更新 update Table1 Set A1='xxx',B1='xxx',C1='xxx' update Table2 Set B2='xxx',C2='xxx' update Table3 Set B3='xxx',C3='xxx' commit tran
as
select Table1.A1,Table1.B1,Table1.C1,Table2.A2,Table2.B2,Table2.C2,Table3.A3,Table3.B3,Table3.C3 from table1,table2,table3 where Table1.A1=Table2.A2;Table1.B1=Table3.A3
goCREATE TRIGGER 名 on 视图
INSTEAD OF update
AS
BEGIN
update table1 set c1=inserted.c1 from inserted where inserted.a1=table1.a1 and inserted.b1=table1.b1
update table2 set b2=inserted.b2,c2=inserted.c2 from inserted where inserted.a2=table1.a2
update table3 set a3=inserted.a3,c3=inserted.c3 from inserted where inserted.b3=table1.b3
END
goupdate 视图 set C1='xxx',B2='xxx',C2='xxx',A3='xxx',C3='xxx' where a1='xx' and b1='yy'
update Table1 Set A1='xxx',B1='xxx',C1='xxx'
update Table2 Set B2='xxx',C2='xxx'
update Table3 Set B3='xxx',C3='xxx'
commit tran