怎样在SQL视图里面些写触发器（inserrt、update、delete）？基本语法是怎样的？
发布网友
发布时间：2022-05-26 23:15
共2个回答
热心网友
时间：2023-11-29 12:09
有表:
表A(Aid,Bid,c1)
表B(Bid,c2)
有视图:
create view vw1
as
select a.Aid,a.Bid,a.c1,b.c2 from 表A a left join 表B b on a.Bid=b.Bid
在vw1视图上建立insert触发器:
create trigger tr1
on vw1
instead of insert
as
insert into 表A select Aid,Bid,c1 from inserted
insert into 表B select Bid,c2 from inserted
在vw1视图上建立update触发器:
create trigger tr2
on vw1
instead of update
as
update 表A set Aid=a.Aid,Bid=a.Bid,c1=a.c1
from
(select
i.Aid,i.Bid,i.c1,i.c2,
d.Aid as dAid,d.Bid as dBid,d.c1 as dc1,d.c2 as dc2
from inserted i,deleted d
) a
where 表A.Aid=a.dAid and 表A.Bid=a.Bid and 表A.c1=a.dc1
update 表B set Bid=a.Bid,c2=a.c2
from
(select
i.Aid,i.Bid,i.c1,i.c2,
d.Aid as dAid,d.Bid as dBid,d.c1 as dc1,d.c2 as dc2
from inserted i,deleted d
) a
where 表B.Bid=a.dBid and 表B.c2=a.dc2
在vw1视图上建立delete触发器:
create trigger tr3
on vw1
instead of delete
as
delete from 表A where exists(select 1 from deleteed a where a.Aid=表A.Aid and a.Bid=表A.Bid)
delete from 表B where exists(select 1 from deleteed a where a.Bid=表A.Bid)
热心网友
时间：2023-11-29 12:10
