Using the WITH CHECK OPTION Clause.
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 following.
You can ensure that DML on the view stays within the domain of the view by using the WITH CHECK OPTION.
SQL> CREATE OR REPLACE VIEW empvu20
2 AS SELECT *
3 FROM emp
4 WHERE deptno = 20
5 WITH CHECK OPTION CONSTRAINT empvu20_ck;
View created.
Any attempt to change the department number for any row in the view will fail because it violates the WITH CHECK OPTION constraint.
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 following.
- Distinct Operator
- Aggregate Functions : AVG,COUNT,GLB,MAX,MIN,STDDEV,SUM or VARIANCE
- Set Operators : UNION , UNION ALL , MINUS ,INTERSECT .
- GROUP BY or HAVING Clauses.
- START WITH or CONNECT BY clauses
- PSEUDO Columns : ROWNUM ,ROWID
Any UPDAE ,INSERT , or DELETE statement on a join view can modify only one underlying base table.
The WITH CHECK OPTION Clause specifies the level of checking to be done when doing DML against the view . if specified every row that is inserted , updated or deleted through the view must confirm to the definition of the view.
Specify WITH READ ONLY to indicates that the table or view can not be updated.
You can ensure that DML on the view stays within the domain of the view by using the WITH CHECK OPTION.
SQL> CREATE OR REPLACE VIEW empvu20
2 AS SELECT *
3 FROM emp
4 WHERE deptno = 20
5 WITH CHECK OPTION CONSTRAINT empvu20_ck;
View created.
Any attempt to change the department number for any row in the view will fail because it violates the WITH CHECK OPTION constraint.
No comments:
Post a Comment