Updating view in Oracle plsql

As we all know that a view columns cannot be updated like we do in table. But there are certain ways to update the corresponding table columns of a view using instead of triggers.

A trigger need to be created on the view that need to be updated with instead of clause and inside the trigger the corresponding table columns can be updated.

Let us see this with an example.

Say we have to tables.

1. Employee
The structure of the employee table is as such below.


EMPID    NUMBER(20),
EMPNAME  VARCHAR2(100),
DEPTID   NUMBER(20),
AGE      NUMBER(3),
CTC      NUMBER

2. Department
DEPTID    NUMBER(10),
DEPTNAME  NUMBER

Now we have a view name employee_view whose query is as below

CREATE OR REPLACE VIEW EMPLOYEE_VIEW AS
SELECT EMPNAME,DEPTNAME,AGE,CTC
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON D.DEPTID = E.DEPTID

This view can be easily inserted / updated / deleted using an insert / update / delete statement.

update employee_view set empname = :empname, deptname = :deptname where empid = :empid;

So in what case a view is not updatable?

1. Using distinct operator
2. Using group or order by clause
3. Using connect by, start with clause
4. Using a sub query
5. Using any kind of functions or oracle internal aggregated functions (point number 2 applicable)
6. Using join condition.

In the above case a instead of trigger need to be created. Let us assume that the view query is a such

CREATE OR REPLACE VIEW EMPLOYEE_DEPT_VIEW AS
SELECT EMPNAME,DEPTNAME,AGE,CTC
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON D.DEPTID = E.DEPTID
ORDER BY DEPTNAME,EMPNAME
Now if we try to insert / update / delete  it will not allow as join condition have been used in the query.

In this scenario we need to add one instead of trigger which will insert into (may not be for the above view query as not all columns are present and depending on not null constraints or check constraints) the table, update the table or delete from table based on the dml query that will be written within the instead of trigger.

Here given below are given 2 instead of trigger on insert / update or delete.

Update

CREATE OR REPLACE TRIGGER TRG_EMPLOYEE_VIEW AS
INSTEAD OF UPDATE ON EMPLOYEE_VIEW
FOR EACH ROW
BEGIN
    UPDATE EMPLOYEE SET EMPNAME=:NEW.EMPNAME, AGE = :NEW.AGE, CTC = :NEW.CTC WHERE EMPID = :OLD.EMPID;
    UPDATE DEPARTMENT SET DEPTNAME = :NEW.DEPTNAME WHERE DEPTID = :OLD.DEPTID;
EMD;/
As from the above we observe that for update we are taking all :new.fieldname for the updated value and since key field is empid for employee and deptid is key field for deprtment.

Now lets insert data into the EMPLOYEE_DEPT_VIEW

CREATE OR REPLACE TRIGGER TRG_EMPLOYEE_DEPT_VIEW
INSTEAD OF INSERT ON EMPLOYEE_DEPT_VIEW
FOR EACH ROW
BEGIN
 INSERT INTO EMPLOYEE VALUES(:NEW.EMPID,:NEW.EMPNAME,:NEW.DEPTID,:NEW.AGE,:NEW.CTC);
 INSERT INTO DEPARTMENT VALUES(:NEW.DEPTID,:NEW.DEPTNAME);
END;

CREATE OR REPLACE TRIGGER TRG_EMPLOYEE_DEPT_VIEW
INSTEAD OF DELETE ON EMPLOYEE_DEPT_VIEW
FOR EACH ROW
BEGIN
 DELETE FROM EMPLOYEE WHERE EMPID=:NEW.EMPID;
 DELETE FROM DEPARTMENT WHERE DEPTID = :NEW.DEPTID;
END;
Now if we write a delete query like delete from EMPLOYEE_DEPT_VIEW where empid = :old.empid and deptid = :old.deptid;
This will delete corresponding record from employee and deprtment. IF the value of any of the field like empid or deptid does not match then that record will not be deleted but the other matching record will be deleted.


CREATE OR REPLACE TRIGGER TRG_EMPLOYEE_DEPT_VIEW
INSTEAD OF UPDATE ON EMPLOYEE_DEPT_VIEW
FOR EACH ROW
BEGIN
 UPDATE EMPLOYEE SET EMPNAME = :NEW.EMPNAME, AGE = :NEW.AGE, CTC = :NEW.CTC WHERE EMPID=:NEW.EMPID;
 UPDATE DEPARTMENT SET DEPTNAME = :NEW.DEPTNAME WHERE DEPTID = :NEW.DEPTID;
END;
Now if we write a delete query like update EMPLOYEE_DEPT_VIEW where empid = :old.empid and deptid = :old.deptid;
This will update corresponding record columns from employee and deprtment. IF the value of any of the field like empid or deptid does not match then that record will not be updated but the other matching record will be updated.