A view is stored SQL that you can query as if it were a table.
Some views allow INSERT UPDATE and DELETE commands.
With an INSTEAD OF trigger you can define the behavior of INSERT, UPDATE, and DELETE for any view.
The INSTEAD OF triggers override the default Oracle behavior of the INSERT, UPDATE, or DELETE command and substitute your custom code.
create or replace view v_customer as select c.customer_id,c.lastname_tx,c.firstname_tx, w.address_id work_id, w.street_tx work_street_tx, w.stateprovince_cd work_state_cd, w.postal_cd work_postal_cd, w.country_tx work_country_tx, h.address_id home_id, h.street_tx home_street_tx, h.stateprovince_cd home_state_cd, h.postal_cd home_postal_cd, h.country_tx home_country_tx from customer c left outer join address w on c.customer_id = w.customer_id and w.type_cd = 'W' left outer join address h on c.customer_id = h.customer_id and h.type_cd = 'H'; create or replace trigger v_customer_id instead of delete on v_customer referencing new as new old as old begin delete from address where customer_id=:old.customer_id; delete from customer where customer_id=:old.customer_id; end; / create or replace trigger v_customer_ii instead of insert on v_customer referencing new as new old as old declare v_customer_id NUMBER; begin if :new.lastname_tx is not null or :new.firstname_tx is not null then insert into customer (customer_id,lastname_tx, firstname_tx) values (object_seq.nextval,:new.lastname_tx, :new.firstname_tx) returning customer_id into v_customer_id; if :new.work_street_tx is not null then insert into address (address_id,street_tx, stateprovince_cd, postal_cd,country_tx, type_cd, customer_id) values (object_seq.nextval,:new.work_street_tx, :new.work_state_cd,:new.work_postal_cd, :new.work_country_tx, 'W', v_customer_id); end if; else raise_application_error (-20999, 'Cannot create customer without name'); end if; end; /