Subject Updateable Views question.
Author Greg At ACD
I have 2 tables (MyTable, MySubTable) where MySubTable's primary key
is also a foreign key to MyTable. So, MySubTable is truly a child of

Now, I have instances where I will have data in MyTable, but NOT in
MySubTable (since there is no relevant data for this record). So now
I have records in MyTable that do not have associated MySubTable
records (in practice, this is a good thing since MyTable has just a
few columns, and MySubTable has many columns, but are associated
with a subset of the main records; otherwise I would just create one
table containing all columns).

I would like to create a view that encompasses MyTable and
MySubTable. This is fairly straightforward except that the view must
join the 2 tables; in fact the join must be a left outer join since
the parent table may not have a record to join to in the child
table. Again, this is not really a problem.

Now, I would like to make this view updateable (by creating some
update/insert/delete triggers for the view). This is where I start
to have some problems... for example, there will be instances where
an update statement (e.g. update myview set f1 = 'a', f2 = 'b' where
pk = 6) may have to actually do an insert in the sub table (which
has column f2) since the sub-record may not exist. I can visualize
what has to happen here, but Im getting the feeling that I'm walking
on ground that isn't too stable...

Does anyone have any ideas how to best implement this type of
scenario? Is it best to abandon the idea of an updateable trigger
for this situation?