Oracle - Using an INSTEAD OF TRIGGER

Two tables that store information about employees.

Code:

---

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.

Code:

---

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(+);

---

Code:

---

'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.

Code:

---

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.

Code:

---

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.

Code:

---

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

---

Tested on Oracle 10G for windows.


More later.
:wave:

You can also use an INSTEAD of trigger to insert new records from a view.

Using the same example in the above post, if you attempt to insert a record.

Code:

---

INSERT INTO vw_Employee_Info(employee_no, address1, address2, city, state, zip, effective_from)
      values('JCASH','100 Swede Rd','Apt-101','Riverdale','CA','09851',trunc(sysdate));

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

---

To prevent this error from occurring, introduce the following code.

Code:

---

CREATE OR REPLACE TRIGGER ioft_vw_emp_ins
INSTEAD OF INSERT
ON  vw_EMPLOYEE_INFO
FOR EACH ROW
BEGIN
  INSERT INTO tblAddress values(:NEW.employee_no,
  :NEW.address1,
  :NEW.address2,
  :NEW.city,
  :NEW.state,
  :NEW.zip,
  :NEW.effective_From,
  :NEW.effective_to);
END ioft_vw_emp_ins;

---

This will now allow you to insert records in the child table.

You could also have a trigger to insert records in the main table, but that will require adding some extra logic.

Tested on Oracle 10G running on windows.


:wave: