Success always occurs in private and failure in full public view.
When working with views you have several options to hide some of the data. Of course there is the possibility not to select certain columns of data in the view. This way, the user of the view doesn’t even have to know this column even exists. You can also limit the rows a user is able to see by adding a where clause to the view. The risk here is that the user changes the value of a column, making it invisible for him- or herself. If for instance the user only has access to records in the EMP table which belong to DEPTNO 20 then we could create a view like this:
CREATE OR REPLACE VIEW V_DEPT_20 AS SELECT * FROM EMP WHERE DEPTNO = 20
If you query this view with this statement
SELECT EMPNO, ENAME, JOB, DEPTNO FROM V_DEPT_20
then you would get this result:
But what happens when Adams is transferred to department 30. You can issue a command like this:
UPDATE V_DEPTNO_20 SET DEPTNO = 30 WHERE EMPNO = 7876
If you issue the above statement again you would get the following result:
You have effectively removed Adams from your visible data.
While this can be the desired behaviour you can be expected to build some guarding against this problem. If you add the WITH CHECK OPTION to your view creation you will be protected from removing rows from your control.
CREATE OR REPLACE VIEW V_DEPT_20 AS SELECT * FROM EMP WHERE DEPTNO = 20 WITH CHECK OPTION
When you try to issue an update like above you will get this error:
Also, when you insert a row into this view which would be outside of your visible data, this error will be raised.
You can prevent users from editing you data by creating triggers that prevent updates to your data. But triggers can be disabled which means users can update the data even though they are not supposed to. Another option is set the tablespace to readonly, but this means all tables in this tablespace are readonly. You can also add the WITH READ ONLY option to the view. This prevents updates issued to the view. Please note that a view can have either WITH CHECK OPTION or WITH READ ONLY but not both. You can leave both off though.
As you know you have the option to create triggers on tables. You can create the same type of triggers on views, but you have another option for your triggers, being the instead of trigger. Where the normal triggers allow you to execute code before or after the statement or modification to the row, these triggers let you execute code instead of the action the user initiated. So instead of executing the statement issued you can do something completely different. This can come in handy when you have a view on which it is not possible to perform DML. This can happen when you have a non-key-preserved view.
A view is key preserved if every key of the view can also be a key of the result of the join. In a key preserved view rows from the base tables appears at most once. Key preserved views guarantees to return only one copy of each row from the base tables.
This is only possible with a one-on-one relationship.
But if you want to create a view based on a couple of tables that cannot be key-preserved you can circumvent this problem by creating instead-of triggers on the view. In these trigger you can implement logic that performs the desired action for you. If for example you execute an insert on the view the logic in the trigger can perform inserts on the underlying tables. Same holds true for updates and deletes. You can also create these triggers on single table views or on key-preserved views. Keep in mind though that the logic issued will NOT be performed, but the logic in the INSTEAD-OF triggers will be performed instead. In these triggers you can also perform additional logic where you can for example add logging or perform some extra inserts to accomplish your own version of journalling.
Building views upon your tables gives you a bit more flexibilty on how you want your data to be handled. If you just want to have other processes to edit the data, simple views can do the trick. If you want more control over what happens when a process changes the data then you may want to add triggers to the views. If you implement a simple view layer now, without the instead-of triggers, other processes can start using you data right away. If you decide later you want more control, you can easily add the instead-of triggers. If you decide you want a different implementation of the tables, you can do so, without breaking other applications as long as you make sure the views deliver the expected results. It’s the first step in decoupling your backend (the data layer) and the frontend or multiple frontends.
Oracle PL/SQL Programming 5th Edition – Steven Feuerstein