How To Create an Updateable View in SQL Server

Relational databases typically break information into multiple tables to reduce the amount of repeated information they contain.

For example suppose you want to store notes about people.
You could (but probably should not) create a table like this:

create table PeopleNotes (
   name varchar(max)
  ,note varchar(max)
)

The reason this is a bad idea becomes clear when you consider the contents of the table when multiple notes are entered for the same person.

insert into PeopleNotes values
  ('Aaron', 'came to work')
 ,('Joe', 'left eary')
 ,('Aaron', 'posted to his blog')

name     note
-------  -------------
Aaron    came to work
Joe      left eary
Aaron    posted to his blog


The problem with the data in PeopleNotes is that the name "Aaron" is stored two times.  This doesn't seem like a big deal but if there were 1,000,000 notes then it would waste alot of space to repeat those 5 characters of the name over and over for each row.

The solution is to make a second table called People and then change PeopleNotes to store a reference to its id.

create table People (
   id int primary key not null identity(1,1)
  ,name varchar(max)
)

create table PeopleNotes (
   id int primary key not null identity(1,1)
   person_id varchar(max) references People(id)
  ,note varchar(max)
)


id      name
------- -------------
1       Aaron
2       Joe

id      person_id note
------- --------- -------------
1       1         came to work
2       2         left eary
3       1         Aaron posted to his blog

The extra complexity of a second table yields a savings every time a note is added for person that already exists because instead of storing all the characters of the name it just has to store the id of row in the Person table.

Now in order to get the same result a view is created to join the tables together.

create view v_PeopleNotes as
select P.name, N.note
from PeopleNotes N
inner join People P on N.person_id = P.id

The complexity of inserting increases since in order to add a row to PeopleNotes, the People table needs to be checked for the existance of the person's name and added to if not found.

By placing the required logic in an INSTEAD OF INSERT trigger, the insert statement that originally worked with PeopleNotes when it was a single table now works with v_PeopleNotes.

create trigger tr_v_PeopleNotes_IOI on v_PeopleNotes
instead of insert as begin
    set nocount on
    --
    -- insert People
    --
    if(not exists(      select P.id from People P
      inner join inserted I on P.name = I.name))
         insert into People
         select distinct name from inserted
    --
    -- insert PeopleNotes
    -- 
    insert into PeopleNotes (person_id, note)
    select P.id, I.note
    from People P inner join inserted I on P.name = I.name
end