3 ms·

The answers bring up the topic of updatable views, which got me curious. In what use-case are updatable views superior to updating directly on the underlying t

The answers bring up the topic of updatable views, which got me curious. In what use-case are updatable views superior to updating directly on the underlying table? Views are great for bringing together data from multiple tables, but it seems that such views cannot be updatable: (from the docs) "The view must have exactly one entry in its FROM list". Thus it seems, while it might be handy, views can't help you spread an update across tables. Presumably, a view with `LOCAL CHECK OPTION` or `CASCADED CHECK OPTION` could be granted to a user, providing fine-grained control over the kind of data that user can update. Is this accurate? What am I missing?

anarazel 8y ago
> The answers bring up the topic of updatable views, which got me curious. Even for those, INSTEAD OF triggers provide a better solution these days. > Views are great for bringing together data from multiple tables, but it seems that such views cannot be updatable: (from the docs) "The view must have exactly one entry in its FROM list". Thus it seems, while it might be handy, views can't help you spread an update across tables. Note that that's just talking about automatic updatability. If you manually create INSTEAD OF triggers that restriction doesn't exist. > In what use-case are updatable views superior to updating directly on the underlying table? It's not uncommon that views are used to keep older applications working in the face of schema chanes, or to merge previously independent databases together. That will often mean that DML to one table affects multiple underlying tables.