You can change rows with an UPDATE command against the view, and then the rows don't show up in the view anymore.
You add rows with an INSERT command against the view; however, the rows don't show up when you query the view.
Check out what happens by updating four rows, they disappear from the view.
SQL> select * from dept20_v; SQL> update dept20_v 2 set deptno = 30 3 where job ='TRAINER'; 4 rows updated. SQL> select * from dept20_v; Apparently, the updates in Listing 10-15 are propagated to the underlying EMPLOYEES table. All trainers from department 20 don't show up anymore in the DEPT20_V view, because their DEPTNO column value is changed from 20 to 30.
You insert a new row for employee 9999, and you get the message 1 row created. However, the new employee does not show up in the query.
create view dept20_v as select * from emp where deptno = 20; insert into dept20_v values ( 9999,'BOS','D', null, null , date '1939-01-01' , '10', null, 30); select * from dept20_v;
You can create your views with the WITH CHECK OPTION clause to Prevent These Two Scenarios.
You can assign a name to WITH CHECK OPTION constraints, as follows:
create [or replace] view ... with check option constraint <cons-name>;
If you don't provide a constraint name, the Oracle DBMS generates a rather cryptic one for you.
The following code replaces the DEPT20_V view, using WITH CHECK OPTION.
create or replace view dept20_v as select * from emp where deptno = 20 with check option constraint dept20_v_check; insert into dept20_v values ( 9999,'BOS','D', null, null , date '1939-01-01' , '10', null, 30); , '10', null, 30) * ERROR at line 4: ORA-01402: view WITH CHECK OPTION where-clause violation