Use with check option in oracle

If the view definition includes conditions (such as the WHERE clause) and its intention is to ensure that the WHERE clause is applied to any INSERT or UPDATE statements that reference the view, you must use with check option to define the view. This option ensures the integrity of the data being modified in the database. If conditions are violated during the INSERT or UPDATE operation, an SQL error is returned.
The following is an example of using the with check option view definition. To always CHECK the conditions, with check option is required. In this case, we want to ensure that DEPT is always 10. This limits the input values of the DEPT column. When a new value is inserted using a view, the with check option is always enforced.
Copy codeThe Code is as follows:
Create view EMP_VIEW2
(EMPNO, EMPNAME, DEPTNO, JOBTITLE, HIREDATE)
As select id, NAME, DEPT, JOB, HIREDATE FROM EMPLOYEE
Where dept = 10
With check option;
If this clause does not exist, a user using this view may update a record so that it does not belong to this view. For example, the following SQL statement may cause some problems.
UPDATE EMP_VIEW2 set dept = 20 where dept = 10;
The result of this statement is that the view does not contain any records now, because Department 10 no longer has employees.