title: Update/Insert into View wia Trigger
body: <p>It occurred in my project that I had to extend an existing table with a few more columns. Unfortunately I can't alter or drop/recreate it, so I just chose to create another table with a 1:1-Relationship and merge them in a view with Insert/Update/Delete-Triggers.</p>
  
  <p>So far, insert and Delete works, but I have my issues with the Update-Trigger. The success-message is even more confusing.</p>
  
  <p>The View looks like this:</p>
  
  <pre><code>Create View [dbo].[JoinedGroups] as
  SELECT [dbo].[MaterialGroups].[GroupID]
        ,[Name]
        ,[SupGroup_ID]
        ,[ExtGroup_ID]
    FROM [dbo].[MaterialGroups]
   left outer join [dbo].[MaterialGroups_Extend]
   ON [dbo].[MaterialGroups].[GroupID]=[dbo].[MaterialGroups_Extend].[GroupID]
  </code></pre>
  
  <p>As said the new additional Table isn't filled yet, that's the reason for the left outer join, but if you got a different idea, let me know it.</p>
  
  <p>The Trigger:</p>
  
  <pre><code>CREATE TRIGGER [dbo].[UpdateTriggerJG] ON [dbo].[JoinedGroups]
  INSTEAD OF Update
  AS
  BEGIN
  IF EXISTS (SELECT * FROM [dbo].[MaterialGroups_Extend] E,inserted I WHERE E.[GroupID] = I.[GroupID])
  BEGIN
     UPDATE [dbo].[MaterialGroups_Extend]
     SET [SupGroup_ID] = I.[SupGroup_ID]
        ,[ExtGroup_ID] = I.[ExtGroup_ID]
        FROM [Buran].[dbo].[MaterialGroups_Extend] E,inserted I
     WHERE E.[GroupID] = I.[GroupID]
  END
     ELSE
     BEGIN
     INSERT INTO [dbo].[MaterialGroups_Extend]
             ([GroupID]
             ,[SupGroup_ID]
             ,[ExtGroup_ID])     
        SELECT [GroupID]
             ,[SupGroup_ID]
             ,[ExtGroup_ID]
        FROM inserted
        END
     UPDATE [dbo].[MaterialGroups]
     SET [dbo].[MaterialGroups].[Name] = I.Name
     FROM [dbo].[MaterialGroups],inserted I
     WHERE [dbo].[MaterialGroups].[GroupID] = I.[GroupID]
  </code></pre>
  
  <p>A statement like:</p>
  
  <pre><code>UPDATE [dbo].[JoinedGroups]
     SET 
        [SupGroup_ID] = 1
        ,[ExtGroup_ID] = 1
  </code></pre>
  
  <p>Gives the following output:</p>
  
  <pre><code>(2 row(s) affected)
  
  (23 row(s) affected)
  
  (23 row(s) affected)
  </code></pre>
  
  <p>Which is kinda interesting:
  [MaterialGroups] contains 23 rows of data
  [MaterialGroups_Extend] contains 2 rows of data
  The view has 23 as well.</p>
  
  <p>But why isn't anything inserted? Am I doing something wrong or have I forgotten something?</p>