Oracle forms updating a view devitto dating
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.
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.
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").
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).
Search for oracle forms updating a view:
If conditions later change so that the query of an invalid view can be executed, then the view can be recompiled and become valid.