행복한 스마일맨 ^^*

========
오라클
========
!! 트리거,트리거백업,트리거업뎃,after,before

create table tr1(name varchar2(10), age number(3));
create table test1(name varchar2(10), age number(3));
create table test(name varchar2(10), age number(3), tel varchar2(16));
==============================================
트리거 연습
create trigger t1_trigger
after insert on test
begin
insert into tr1 values('park',25);
end;
/

insert into test values('hong',25,'1234-44');
select * from tr1;
insert into test values('nam',25,'1334-44');
select * from tr1;

drop trigger t1_trigger;
===================================================
지우면 아래 값넣기
create trigger tr2_trigger
after delete on test1
for each row
begin
insert into tr1 values('행트리거',10);
end;
/

insert into test1 values('11',22);

delete test1;
하면
insert into tr1 values('행트리거',10);가 실행된다
====================================
지우기전에 빽업
create trigger tr3_trigger
after delete on test1
for each row
begin
insert into tr1 values(:old.name,:old.age);
end;
/

insert into test1 values('qqqq',31);

delete test1;

select * from tr1;
=====================================

사원 테이블에서 2000보다 많은 봉급을 받는 여사원들의 봉급이
update 되면 update_tab3 table 에('sawon',1,'female') data 를 insert (행 trigger)

create table update_tab3(name varchar2(10), su number(3), tel varchar(10));

create trigger ex_tri5
after update of sapay on sawon for each row
when(old.sapay>2000 and old.sasex='여자')
begin
insert into update_tab3 values('sawon',1,'femail');
end;
/

select * from sawon;
update sawon set sapay=sapay*0.9 where sasex='여자'; 
select * from update_tab3;
select * from sawon;
===========================================================
사원 테이블의 data 가 update 될때 이전에 있던 데이터를 savesawon 테이블에 insert
create table savesawon(name, job,pay,bouns)
as select saname, sajob, sapay,sapay*0.7 from sawon;

create trigger up_tri
before update on sawon for each row
begin
insert into savesawon values
(:old.saname,:old.sajob,:old.sapay,:old.sapay*0.7);
end;

select * from sawon;
update sawon set sapay=sapay*1.1;
select * from savesawon;
select * from sawon;
===========================================
sawon table data 가 update 될때 새로운 data 를 newsawon table 에 insert

create table newsawon(name,job,pay,bouns)
as select saname,sajob,sapay,sapay*0.7
from sawon;

create or replace trigger up_tri2
after update on sawon for each row
begin
insert into newsawon values
(:new.saname,:new.sajob,:new.sapay,:new.sapay*0.7);
end;
/

select * from sawon;

update sawon set sapay=sapay*0.9;
select * from newsawon;
===================================================================
if book_loan(책대출 테이블)에 data insert 시 같은 책의 id를 가진책을
book_loan tale(책정보)에서 loan column을 Y 대출중 표시 로 update

create table book_info
(id number primary key, bookname varchar2(10),loan char(2));

create table book_loan
(id number, anem varchar2(10),
constraints book_fk foreign key(id)
references book_info(id));

insert into book_info values(1,'computer','n');
insert into book_info values(2,'economy','n');
insert into book_info values(3,'science','n');

create trigger book_tri
after insert on book_loan for each row
begin
update book_info set loan='y'
where book_info.id = :new.id;
end;
/

insert into book_loan values(1,'kim');
select * from book_info;