Update a join view

Summary 

A lot of views based on joins and it is very often the need to make an update on the view. But all the columns of view are updatabale? We can see which columns we are able to update by looking at USER_UPDATABALE_COLUMNS for this view:

select * from user_updatable_columns 
where table_name = 'VIEW_NAME';

Example
Create the tables MASTER and SLAVE with some data and the view SLAVES_M

CREATE TABLE MASTER (master_id NUMBER(3), 
MASTER VARCHAR2(100));

INSERT INTO MASTER VALUES (1, 'master01');
INSERT INTO MASTER VALUES (2, 'master02');
INSERT INTO MASTER VALUES (3, 'master03');
INSERT INTO MASTER VALUES (4, 'master04');
COMMIT;

CREATE TABLE slave (slave_id NUMBER(3), 
slave VARCHAR2(100), master_id NUMBER(3));

INSERT INTO slave VALUES (1, 'slave01', 1);
INSERT INTO slave VALUES (2, 'slave02', 2);
INSERT INTO slave VALUES (3, 'slave03', 1);
INSERT INTO slave VALUES (4, 'slave04', 3);
INSERT INTO slave VALUES (5, 'slave05', 1);
INSERT INTO slave VALUES (6, 'slave06', 4);
INSERT INTO slave VALUES (7, 'slave07', 4);
COMMIT;

CREATE OR REPLACE VIEW SLAVES_M AS SELECT 
S.slave_id, S.slave, S.master_id, M.MASTER 
FROM slave S, MASTER M 
WHERE S.master_id(+) = M.master_id;

If you check the view you will see that no column can be modified.

SQL> SELECT * FROM user_updatable_columns 
  2  WHERE table_name = 'SLAVES_M';

OWNER                          TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------ ------------------------------ --- --- ---
HELPDESK                       SLAVES_M                       MASTER                         NO  NO  NO
HELPDESK                       SLAVES_M                       MASTER_ID                      NO  NO  NO
HELPDESK                       SLAVES_M                       SLAVE_ID                       NO  NO  NO
HELPDESK                       SLAVES_M                       SLAVE                          NO  NO  NO

If you want to update the master column in the view and in the MASTER table use the trick with the INSTEAD OF UPDATE trigger

CREATE OR REPLACE TRIGGER SLAVES_M_U INSTEAD OF UPDATE 
ON SLAVES_M 
FOR EACH ROW
BEGIN
IF (:NEW.master_id <> :OLD.master_id) THEN
RAISE_APPLICATION_ERROR(-20001, 'Updates are prohibited to the primary key of the MASTER table');
END IF;
IF (:NEW.MASTER <> :OLD.MASTER)
THEN
UPDATE MASTER 
SET    MASTER = :NEW.MASTER
WHERE master_id = :OLD.master_id;
END IF;
END;
/

If you check again for the view

SQL> SELECT * FROM user_updatable_columns 
  2  WHERE table_name = 'SLAVES_M';

OWNER                          TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------ ------------------------------ --- --- ---
HELPDESK                       SLAVES_M                       SLAVE_ID                       YES NO  NO
HELPDESK                       SLAVES_M                       SLAVE                          YES NO  NO
HELPDESK                       SLAVES_M                       MASTER_ID                      YES NO  NO
HELPDESK                       SLAVES_M                       MASTER                         YES NO  NO

Make an update and check for the results

UPDATE SLAVES_M SET MASTER = 'NEW' WHERE slave_id = 7;
COMMIT;
SELECT * FROM MASTER;