Oracle - Using an INSTEAD OF TRIGGER

[abhijit]

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.

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.

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.