Oracle and PL/SQL Recipes: A Problem-Solution Approach

5-4. Making a View Updatable

Problem

You are working with a database view, and it needs to be updated. However, the view is not a simple view and is therefore read-only. If you tried to update a column value on the view, then you would receive an error.

Solution

Use an INSTEAD OF trigger to specify the result of an update against the view, thus making the view updatable. For example, let's begin with the following view definition:

CREATE OR REPLACE VIEW EMP_JOB_VIEW AS   SELECT EMP.employee_ID, EMP.first_name, EMP.last_name,          EMP.email, JOB.job_title,          DEPT.department_name   FROM employees EMP,        jobs JOB,        departments DEPT   WHERE JOB.job_id = EMP.job_id   AND DEPT.department_id = EMP.department_id   ORDER BY EMP.last_name; ...