# VBForums CodeBank > CodeBank - Other >  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. 
 :wave:

----------


## 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.*


 :wave:

----------

