SQL Server テーブルをViewにしたけど一部の項目を更新したいという要望に答えるサンプル

drop table if exists sdemp
create table sdemp (empid int, name nvarchar(200));
drop table if exists emppoint
create table emppoint (empid int, point int);
drop view if exists emp
create view emp as
select se.empid empid, se.[name] [name], isnull(ep.point,0) point
from sdemp se left outer join emppoint ep on ep.empid = se.empid
go
create trigger triggerupdeteemp on emp
instead of update as begin
set nocount on
merge into emppoint using inserted on emppoint.empid = inserted.empid
when matched then update set point = inserted.point
when not matched then insert values(inserted.empid, inserted.point);
end
--sdのテーブル
insert sdemp values(1,'abe')
insert sdemp values(2,'kiyo')
select * from emp
/*
empid name point
1 abe 0
2 kiyo 0
*/
begin tran
update  emp set point = point + 10 where empid = 1
update  emp set point = point + 10 where empid = 1
select * from emp
/*
empid name point
1 abe 10
2 kiyo 0
*/
rollback tran