Oracle PL/SQL - Building INSTEAD OF trigger views

Introduction

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;
/

Oracle PL/SQL - Building INSTEAD OF trigger views

Introduction

Related Topic