Updating oracle views

' '

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, now you realize that you must redefine the ACCOUNTS_STAFF view to correct the department number specified in the WHERE clause of the defining query, because it should have been 30.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.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.

Leave a Reply