Updating table through view in oracle

Rated 3.99/5 based on 584 customer reviews

You can use subqueries in the SET portion of the UPDATE command.

The FROM clause of such subqueries can reference either the local database or a remote database.

For example, to change the RATING values for books in a remote BOOKSHELF table, you would execute the UPDATE command shown in the following listing: This UPDATE command uses the REMOTE_CONNECT database link to log into the remote database.

It then updates the BOOKSHELF table in that database, based on the SET and WHERE conditions specified.

Views, which are a type of virtual tables allow users to do the following − Database views are created using the CREATE VIEW statement.

Views can be created from a single table, multiple tables or another view.

To refer to the remote database in a subquery, append the database link name to the table names in the FROM clause of the subquery.

A view is actually a composition of a table in the form of a predefined SQL query.PUT_LINE('Record successfully inserted into emp_backup table'); END; Second Problem: We want o update a record in the ’emp_backup’ table if a corresponding record is updated in the ’emp’ table.As we’ve already created both the tables in above example, we will directly write a trigger for ‘AFTER UPDATE’.To create a view, a user must have the appropriate system privilege according to the specific implementation.The basic CREATE VIEW syntax is as follows − ---- ---------- ----- ----------- ---------- | ID | NAME | AGE | ADDRESS | SALARY | ---- ---------- ----- ----------- ---------- | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | ---- ---------- ----- ----------- ---------- The WITH CHECK OPTION is a CREATE VIEW statement option.

Leave a Reply