Plsql_ Basic Series 5_ View Control with CHECK OPTION

2014-12-09 Baoxinjian

I. Summary

By manipulating the base table with a view with CHECK option (just facing a single table and looking for an answer to a view that joins multiple tables), there are the following conclusions:

First, the view only operates the data it can query, and for the data it cannot query, even if the base table has, it can not be manipulated by the view.

1. For update, with CHECK option, to ensure that the data is queried by the view after the update

2. For delete, there is no same as check option

3. For INSERT, with CHECK option, to ensure that the data is queried by the view after insert

With CHECK option is redundant for views without a WHERE clause.

Second, the case

Step1. Create a table

Create TableEMP (ID Number(5,0), namevarchar2( A), Addressvarchar2( A));Insert  intoEmpValues(5,'Zhang San','Kwong XI');Insert  intoEmpValues(6,'John Doe','Beijing');Insert  intoEmpValues(7,'Harry','Mountain East');

Step2. Create a view with CHECK option

Create View Emp_view  as Select *  from where id=5withcheckoption;

Step3. Create a view without check option

Create View Emp_view2  as Select *  from where id='5'

Step4. Update operation

UpdateEmp_viewSetName='Chen Liu' whereId=6;-, although the base table has an ID=6 of records, but Emp_view cannot see it, so this modification does not affect the base table contentUpdateEmp_viewSetId=6 whereId=5;--view with CHECK OPTION where-clause violation error appearsUpdateEmp_view2SetId=6 whereId=5;--Successful/plain line

Conclusion: For update, there is no with option to change only the records that appear in the view, for update with the WHIH option, to ensure that the changes can still appear in the view

Step5. Operation

--Update OperationUpdateEmpSetId=5 whereName='Zhang San';--Delete OperationDeleteEmp_viewwhereId='5'--Conclusion:--for Delete, the WITH option is the same.
--Insert OperationInsert  intoEmp_viewValues(8,'Wang','Jiangsu');--view with CHECK OPTION where-clause violation error appearsInsert  intoEmp_view2Values(8,'Wang','Jiangsu');--Successful Execution

Step6. Conclusion:

For insert, with the WITH option, the inserted data will eventually be visible in the view, and any view without the WITH option can insert any record that does not violate the constraint

Thanks and regards

Reference: http://blog.csdn.net/fredrickhu/article/details/4743204

Plsql_ Basic Series 5_ View Control with CHECK OPTION