Oracle - Using an INSTEAD OF TRIGGER

[abhijit]

Two tables that store information about employees.

CREATE TABLE tblEmployee
       (employee_no varchar2(10) NOT NULL, f_name varchar2(30), l_name varchar2(30), dob date, ssn varchar2(12));
       
CREATE TABLE tblAddress
       (employee_no varchar2(10), address1 varchar2(20), 
                    address2 varchar2(20), city varchar2(10), 
                    state varchar2(2), zip varchar2(7),
                    effective_from date NOT NULL, effective_to date);

--Random Sample Data in the base tables
insert into tblEmployee values('JCASH', 'Johnny','Cash','12-Feb-1930', '184-12-3456');                 

insert into tblAddress values('JCASH','25 Fir St',NULL,'Memphis','TN','02345','01-Feb-1950','04-Apr-1970');
insert into tblAddress values('JCASH','25 Hollywood Ave',NULL,'Los Angel','CA','07345','04-Apr-1970',NULL);

View that allows users to see employee information.

CREATE OR REPLACE VIEW vw_EMPLOYEE_INFO
       AS
          SELECT tE.employee_no, tE.f_name,  tE.l_name, to_char(tE.dob,'mm/dd') dob,
                   tA.address1, tA.address2, ta.city, tA.state, ta.zip, ta.effective_from,
                   ta.effective_to
                 FROM tblEmployee tE, tblAddress tA
                 WHERE tE.employee_no = tA.employee_no(+);

'selecting columns from the view.
select * from vw_Employee_INFO

Any attempts to update columns in this view will be met with this error message.

update vw_Employee_Info
set city = 'Sun City'
where employee_no = 'JCASH'
and city = 'Los Angel'

ORA-01779: cannot modify a column which maps to a non key-preserved table.

To avoid this predicament, you can create an instead of view.

CREATE OR REPLACE TRIGGER ioft_vw_emp
INSTEAD OF UPDATE
ON  vw_EMPLOYEE_INFO
FOR EACH ROW
BEGIN
  UPDATE tblAddress
  SET address1 = :NEW.address1,
      address2 = :NEW.address2,
      city = :NEW.city,
      state = :NEW.state,
      zip = :NEW.zip,
      effective_from = :NEW.effective_From,
      effective_to = :NEW.effective_to
  WHERE employee_no = :OLD.employee_no;

  UPDATE tblEmployee
      SET f_name = :NEW.F_NAME,
      l_name = :NEW.l_name
        WHERE employee_no = :OLD.employee_no;
END ioft_vw_emp;

The above update code will now work, thanks to this instead of trigger.

update vw_Employee_Info
set city = 'Sun City'
where employee_no = 'JCASH'
and city = 'Los Angel'

Tested on Oracle 10G for windows.


More later.