Hugo Kornelis (5/8/2013)

---

Mighty (5/8/2013)

---

It also might be because it is still early here, but what is the point exactly?

The view definition is such that...

. What is it exactly why it works? The fact that in the view only one table is used or...?

Views can be "always updatable", "never updatable", or "sometimes updatable" (not official terms). All those apply to views without INSTEAD OF triggers, or to updates to the view from within the INSTEAD OF trigger (this is correct functionality, and required to make things work, especially for INSTEAD OF triggers on a table rather than a view. Inside an INSTEAD OF trigger, you can perform the action that causes the trigger to fire and it won't fire again. So you can e.g. have an instead of insert trigger on a table that splits the inserted rows into valid and invalid ones, logs the invalid ones in an error table, then inserts the remaining rows in the original table without firing itself and causing an infinite loop).

The view in this example is "always updatable"; updates to the x and id column translate to the underlying table; updates to y and z are lost.

An example of a view that is "never updatable" is a view that select constant values, or a view that aggregates data - rows is that view have no 1-to-1 mapping to rows in the underlying table.

Views based on a join are often "sometimes updatable". If you update a set of columns that all come from the same underlying table, the update is accepted as it can internally be translated to an update on a single table, but if you affect rows coming from multiple underlying table, you'll get an error.

For a view with an INSTEAD OF trigger, updatability rules are irrelevant, because the trigger is supposed to handle the updates. However, if you update the view from within the INSTEAD OF trigger, the updatability rules apply again, because (as explained above) the INSTEAD OF trigger will be bypassed.

That being said - when using an INSTEAD OF trigger on a view, I personally think that the modifications made inside that trigger should always address the underlying table, not the view. Makes the code much easier to understand! The only situation where I would want an INSTEAD OF trigger to modify the object it is defined for would be in the case of an INSTEAD OF trigger on a table, not a view. And INSTEAD OF triggers on a table should be used very rarely.