Updating sql view

Rated 3.95/5 based on 788 customer reviews

Views can provide advantages over tables: Just as a function (in programming) can provide abstraction, so can a database view.In another parallel with functions, database users can manipulate nested views, thus one view can aggregate data from other views.The accuracy of a materialized view depends on the frequency of trigger mechanisms behind its updates.IBM DB2 provides so-called "materialized query tables" (MQTs) for the same purpose.However, an INSTEAD OF trigger does not change the read-only or updatable property of the view itself.Various database management systems have extended the views from read-only subsets of data.This pre-established query command is kept in the database dictionary.Unlike ordinary base tables in a relational database, a view does not form part of the physical schema: as a result set, it is a virtual table computed or collated dynamically from data in the database when access to that view is requested.

updating sql view-52

If the database system can determine the reverse mapping from the view schema to the schema of the underlying base tables, then the view is updatable.INSERT, UPDATE, and DELETE operations can be performed on updatable views.Read-only views do not support such operations because the DBMS cannot map the changes to the underlying base tables. Some systems support the definition of INSTEAD OF triggers on views.However, sorted data can be obtained from a view, in the same way as any other table — as part of a query statement on that view.Nevertheless, some DBMS (such as Oracle Database) do not abide by this SQL standard restriction.

Leave a Reply