sql - Update/Insert into View wia Trigger -

it occurred in project had extend existing table few more columns. unfortunately can't alter or drop/recreate it, chose create table 1:1-relationship , merge them in view insert/update/delete-triggers.

so far, insert , delete works, have issues update-trigger. success-message more confusing.

the view looks this:

create view [dbo].[joinedgroups] select [dbo].[materialgroups].[groupid]       ,[name]       ,[supgroup_id]       ,[extgroup_id]   [dbo].[materialgroups]  left outer join [dbo].[materialgroups_extend]  on [dbo].[materialgroups].[groupid]=[dbo].[materialgroups_extend].[groupid] 

as said new additional table isn't filled yet, that's reason left outer join, if got different idea, let me know it.

the trigger:

create trigger [dbo].[updatetriggerjg] on [dbo].[joinedgroups] instead of update begin if exists (select * [dbo].[materialgroups_extend] e,inserted e.[groupid] = i.[groupid]) begin    update [dbo].[materialgroups_extend]    set [supgroup_id] = i.[supgroup_id]       ,[extgroup_id] = i.[extgroup_id]       [buran].[dbo].[materialgroups_extend] e,inserted    e.[groupid] = i.[groupid] end    else    begin    insert [dbo].[materialgroups_extend]            ([groupid]            ,[supgroup_id]            ,[extgroup_id])            select [groupid]            ,[supgroup_id]            ,[extgroup_id]       inserted       end    update [dbo].[materialgroups]    set [dbo].[materialgroups].[name] = i.name    [dbo].[materialgroups],inserted    [dbo].[materialgroups].[groupid] = i.[groupid] 

a statement like:

update [dbo].[joinedgroups]    set        [supgroup_id] = 1       ,[extgroup_id] = 1 

gives following output:

(2 row(s) affected)  (23 row(s) affected)  (23 row(s) affected) 

which kinda interesting: [materialgroups] contains 23 rows of data [materialgroups_extend] contains 2 rows of data view has 23 well.

but why isn't inserted? doing wrong or have forgotten something?

try rewrite trigger like:

create trigger [dbo].[updatetriggerjg] on [dbo].[joinedgroups] instead of update begin     update dbo.materialgroups_extend set         supgroup_id = i.supgroup_id,         extgroup_id = i.extgroup_id     dbo.materialgroups_extend e         inner join inserted on i.groupid = e.groupid      insert dbo.materialgroups_extend     (         groupid,         supgroup_id,         extgroup_id     )          select         groupid,         supgroup_id,         extgroup_id,     inserted             not exists         (              select *              dbo.materialgroups_extend e              e.groupid = i.groupid         )      update dbo.materialgroups set         name = i.name     dbo.materialgroups mg         inner join inserted on i.groupid = mg.groupid end