2 cədvəlin birləşməsi olan VİEW-ya trigger vasitəsi ilə insert


SYSTEM@valeh> create table emp_list (id number, name varchar2(20));
 SYSTEM@valeh> insert into emp_list values (1,'Valeh');
 SYSTEM@valeh> create table emp_age (age_id number, emp_id number, emp_age number);
 SYSTEM@valeh> create view emp_view as select * from emp_list el,emp_age age where el.id=age.emp_id;
create or replace trigger view_trigger
 instead of insert on emp_view
 referencing new as new old as old
 declare
 v_id VARCHAR(20);
 begin
 if :new.id is not null then
 insert into emp_list (id,name) values (:new.id,:new.name);
 insert into emp_age (age_id,emp_id,emp_age) values (:new.age_id,:new.emp_id,:new.emp_age)
 returning :new.id into v_id;
 else
 raise_application_error (-20999, 'Cannot create employee without name');
 end if;
 end;
 
SYSTEM@valeh> insert into emp_view values (2,'taleh',2,2,20);

SYSTEM@valeh> select * from emp_view;

 

Insert into view with trigger


[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SYSTEM@valeh> create table emp_list (id number, name varchar2(20));
SYSTEM@valeh> insert into emp_list values (1,'Valeh');
SYSTEM@valeh> create table emp_age (age_id number, emp_id number, emp_age number);
SYSTEM@valeh> insert into emp_age values (1,1,26);
SYSTEM@valeh> create view emp_view as select * from emp_list el,emp_age age where el.id=age.emp_id;

create or replace trigger view_trigger
instead of insert on emp_view
referencing new as new old as old
declare
v_id VARCHAR(20);
begin
if :new.id is not null then
insert into emp_list values (:new.id,:new.name);
insert into emp_age values (:new.age_id,:new.emp_id,:new.emp_age)
returning :new.id into v_id;
else
raise_application_error (-20999, 'Cannot create employee without name');
end if;
end;

SYSTEM@valeh> insert into emp_view values (2,'taleh',2,2,20);

SYSTEM@valeh> select * from emp_view;

[/pcsh]

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SYSTEM@valeh> create table emp_list (id number, name varchar2(20));
SYSTEM@valeh> insert into emp_list values (1,'Valeh');
SYSTEM@valeh> create table emp_age (age_id number, emp_id number, emp_age number);
SYSTEM@valeh> insert into emp_age values (1,1,26);
SYSTEM@valeh> create view emp_view as select * from emp_list el,emp_age age where el.id=age.emp_id;

create or replace trigger view_trigger
instead of insert on emp_view
referencing new as new old as old
declare
v_id VARCHAR(20);
begin
if :new.id is not null then
insert into emp_list values (:new.id,:new.name);
insert into emp_age values (:new.age_id,:new.emp_id,:new.emp_age)
returning :new.id into v_id;
else
raise_application_error (-20999, 'Cannot create employee without name');
end if;
end;

SYSTEM@valeh> insert into emp_view values (2,'taleh',2,2,20);

SYSTEM@valeh> select * from emp_view;

[/pcsh]