Updating oracle views Chat to slut without member login
Alternatively, assume that the ACCOUNTS_STAFF view is defined by the following statement (that is, excluding the DEPTNO column): CREATE VIEW Accounts_staff AS SELECT Empno, Ename FROM Emp WHERE Deptno = 10 WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst; Considering this view definition, you can update the EMPNO or ENAME fields of existing records, but you cannot insert rows into the EMP table through the ACCOUNTS_STAFF view because the view does not let you alter the DEPTNO field. Loc IN ('HYD', 'BOM', 'DEL'); then there are restrictions on modifying either the EMP or the DEPT base table through this view.
If you had defined a DEFAULT value of 10 on the DEPTNO field, then you could perform inserts. Loc FROM Emp e, Dept d /* JOIN operation */ WHERE e. A modifiable join view is a view that contains more than one table in the top-level FROM clause of the SELECT statement, and that does not contain any of the following: Any UPDATE, INSERT, or DELETE statement on a join view can modify only one underlying base table.
Furthermore, WITH CHECK OPTION creates the view with the constraint that INSERT and UPDATE statements issued against the view are not allowed to create or result in rows that the view cannot select.
Considering the example above, the following INSERT statement successfully inserts a row into the EMP table through the ACCOUNTS_STAFF view: INSERT INTO Accounts_staff VALUES (110, 'ASHI', 10); However, the following INSERT statement is rolled back and returns an error because it attempts to insert a row for department number 30, which could not be selected using the ACCOUNTS_STAFF view: INSERT INTO Accounts_staff VALUES (111, 'SAMI', 30); A view can be created even if the defining query of the view cannot be executed, as long as the CREATE VIEW command has no syntax errors. For example, if a view refers to a non-existent table or an invalid column of an existing table, or if the owner of the view does not have the required privileges, then the view can still be created and entered into the data dictionary.
To preserve the grants of object privileges that you have made, you can replace the current version of the ACCOUNTS_STAFF view with the following statement: CREATE OR REPLACE VIEW Accounts_staff AS SELECT Empno, Ename, Deptno FROM Emp WHERE Deptno = 30 WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst; Replacing a view has the following effects: With some restrictions, rows can be inserted into, updated in, or deleted from a base table using a view.
The following statement inserts a new row into the EMP table using the ACCOUNTS_STAFF view: INSERT INTO Accounts_staff VALUES (199, 'ABID', 30); Restrictions on DML operations for views use the following criteria in the order listed: The constraint created by WITH CHECK OPTION of the ACCOUNTS_STAFF view only allows rows that have a department number of 10 to be inserted into, or updated in, the EMP table.
SQL, SQL Server, Tutorials, Oracle, PL/SQL, Interview Questions & Answers, Joins, Multiple Choice Questions, Quiz, Stored Procedures, Select, Insert, Update, Delete and other latest topics on SQL, SQL Server and Oracle.
Consider the following simple view: CREATE VIEW Emp_view AS SELECT Ename, Empno, deptno FROM Emp; This view does not involve a join operation. In general, all modifiable columns of a join view must map to columns of a key-preserved table. Empno WITH CHECK OPTION; No deletion can be performed on this view because the view involves a self-join of the table that is key preserved.
If you issue the SQL statement: UPDATE Emp_view SET Ename = 'SHAHRYAR' WHERE Empno = 109; then the EMP base table that underlies the view changes, and employee 109's name changes from ASHI to SHAHRYAR in the EMP table. If the view is defined using the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are not modifiable. The following INSERT statement on the EMP_DEPT view succeeds, because only one key-preserved base table is being modified (EMP), and 40 is a valid DEPTNO in the DEPT table (thus satisfying the FOREIGN KEY integrity constraint on the EMP table).
However, if you create a view that involves a join operation, such as: CREATE VIEW Emp_dept_view AS SELECT e. So, for example, if the EMP_DEPT view were defined using WITH CHECK OPTION, then the following UPDATE statement would fail: UPDATE Emp_dept_view SET Deptno = 10 WHERE Ename = 'SAMI'; The statement fails because it is trying to update a join column. Empno; If a view is defined using the WITH CHECK OPTION clause and the key-preserved table is repeated, then rows cannot be deleted from such a view. INSERT INTO Emp_dept (Ename, Empno, Deptno) VALUES ('ASHU', 119, 40); The following INSERT statement fails for the same reason: This UPDATE on the base EMP table would fail: the FOREIGN KEY integrity constraint on the EMP table is violated.
You can delete from a join view provided there is one and only one key-preserved table in the join. INSERT INTO Emp_dept (Ename, Empno, Deptno) VALUES ('ASHU', 110, 77); The following INSERT statement fails with an ORA-01776 error ("cannot modify more than one base table through a view").
The following DELETE statement works on the EMP_DEPT view: DELETE FROM Emp_dept_view WHERE Ename = 'SMITH'; This DELETE statement on the EMP_DEPT view is legal because it can be translated to a DELETE operation on the base EMP table, and because the EMP table is the only key-preserved table in the join. INSERT INTO Emp_dept (Ename, Empno, Deptno) VALUES (110, 'TANNU’, 'BOMBAY'); An INSERT cannot, implicitly or explicitly, refer to columns of a non-key-preserved table. select * from user_views; To see which columns are updatable in join views.
Instead of giving this join query again and again, we can create a view on these table by using a CREATE VIEW command given below create view emp_det as select e.empno, e.ename,e.sal,e.deptno,d.dname,from emp e, dept d where e.deptno=d.deptno; Now to see the employee details and department names we don’t have to give a join query, we can just type the following simple query.