Comments 0

Share to social media

Success always occurs in private and failure in full public view.
– Unknown

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:

If you query this view with this statement

then you would get this result:

EMPNO ENAME JOB DEPTNO
7566 JONES MANAGER 20
7788 SCOTT ANALYST 20
7902 FORD ANALYST 20
7369 SMITH CLERK 20
7876 ADAMS CLERK 20

But what happens when Adams is transferred to department 30. You can issue a command like this:

If you issue the above statement again you would get the following result:

EMPNO ENAME JOB DEPTNO
7566 JONES MANAGER 20
7788 SCOTT ANALYST 20
7902 FORD ANALYST 20
7369 SMITH CLERK 20

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.

When you try to issue an update like above you will get this error:

ORA-01402: view WITH CHECK OPTION where-clause violation

Also, when you insert a row into this view which would be outside of your visible data, this error will be raised.

Read-Only

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.

Triggers

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.

Conclusion

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.

ref:
Oracle PL/SQL Programming 5th Edition – Steven Feuerstein

Load comments

About the author

Patrick Barel's contributions