[Oracle] PL/SQL 트리거(Trigger)

<트리거> Trigger

- 이벤트처리 (~했을때 수반되는 처리)

- 특정테이블에 이벤트(insert,delete,update)가 발생했을 시

  다른 테이블에 연관된 내용을 변경하도록 하는 일.

  

형식)  

  CREATE TRIGGER 트리거명

  (BEFORE|AFTER) (INSERT|DELETE|UPDATE) --이벤트

  ON 테이블명         -- 이벤트가 발생하는 테이블

  [FOR EACH ROW] -- 실행될 문장 행에 각각 적용

  [WHEN 조건식]

  BEGIN

    -- 이벤트 발생시 실행할 문장(주로 DML문장)  ===> 이벤트 핸들러!!

  END;

문제) 사원테이블에 사원정보가 새로 입력될 때 마다 입사 환영메시지를 출력하시오.

  drop table emp2;

  create table emp2

  as select empno, ename, deptno

     from emp

     where 1=0;

  create or replace trigger welcome

  after insert on emp2 -- emp2테이블에 행입력이 발생했다면!!(발생한 후)

  for each row

  begin

    dbms_output.put_line(:new.ename || '님의 회사 입사를 환영합니다^^*!!');

  end;

  /

 

SQL> insert into emp2 values (7000,'영환',10);

회사 입사를 환영합니다^^*!!

SQL> insert into emp2 values (7002,'정현',10);

회사 입사를 환영합니다^^*!! 

<바인드 변수> 전달변수

 :new - 새로 입력(insert,update)된 데이터

 :old - 기존 입력 

 

  ===> begin~end에서 사용법) :new.컬럼명   :old.컬럼명

 

SQL> insert into emp2 values (7004,'준석',10);

준석님의 회사 입사를 환영합니다^^*!!    

    

문제) 사원테이블(emp3)에 사원정보가(empno, ename, sal) 입력되었을때

   급여테이블(no,empno,sal)에 그 사원에 대한 급여 정보를 자동으로 입력하도록

  트리거를 생성하시오.

  drop table emp3;

  create table emp3

  as select empno,ename,sal

     from emp

     where 1=0;

     

  create table salary

  (

     no     number,

     empno  number(4),

     sal    number(7,2)

  );   

  create sequence salary_seq

         start with 1

         increment by 1

         nocycle

         nocache;

  <트리거 생성> - 사원테이블에 입력된 정보 중 sal정보만 모아서 salary테이블에 입력!!

   create or replace trigger sal_info

   after insert on emp3

   for each row

   begin

      -- insert이벤트가 발생한 후 실행할 일 정의!!

      insert into salary (no,empno,sal)

                  values (salary_seq.nextval, :new.empno, :new.sal); 

   end;

   /

SQL> insert into emp3 (empno,ename,sal) values (7000,'영환',1000);

SQL> insert into emp3 (empno,ename,sal) values (7002,'정현',2000);

SQL> insert into emp3 (empno,ename,sal) values (7004,'형님',3000);

SQL> select * from emp3;

     EMPNO ENAME                 SAL

---------- -------------------- ----------

      7000 영환                       1000

      7002 정현                       2000

      7004 형님                       3000

    

SQL> select * from salary;

        NO      EMPNO        SAL

---------- ---------- ----------

         1       7000       1000

         2       7002       2000

         3       7004       3000    

=====================================================

<테이블 만들기>

1. 상품테이블

  drop table 상품;

  create table 상품

  (

        상품코드 char(4) constraint 상품_pk primary key,

        상품명 varchar2(15) not null,

        제조사 varchar2(15),

        소비자가격 number,

        재고수량 number default 0 );

2. 입고테이블

  drop table 입고;

  create table 입고

  (

      입고번호 number primary key,

      상품코드 char(4) references 상품(상품코드),

      입고일자 date,

      입고수량 number,

      입고단가 number,

      입고금액 number);

  dropt sequence 입고_seq;

  create sequence 입고_seq

      start with 1

      increment by 1

      nocycle

      nocache;

<상품테이블에 정보입력>

 - 상품코드,상품명,제조사,소비자가격,재고수량

 insert into 상품 (상품코드,상품명,제조사,소비자가격)

            values ('a001', '마우스', '삼송', 1000);

            

 insert into 상품 (상품코드,상품명,제조사,소비자가격)

            values ('a002', '키보드', 'LC', 2000);

            

 insert into 상품 (상품코드,상품명,제조사,소비자가격)

            values ('a003', '모니터', 'HB', 5000);

            

1. 입력트리거 (입고테이블에 상품이 입력되었을때 재고수량 증가!!)

  예) 입고테이블에 키보드가 10개 입고 되었을 때

    자동으로 상품테이블의 'a002' 상품의 재고가 0 ----> 10으로 변경!!

  create or replace trigger product_insert

  after insert on 입고 -- 입고 테이블에서 입력이 발생했다면

  for each row

  begin

     -- 상품테이블에 대한 수정

     update 상품 set 재고수량= 재고수량  +  :new.입고수량

           where 상품코드 = :new.상품코드;

  end;

  /

SQL> select 상품코드,상품명,재고수량 from 상품;

상품코드     상품명                           재고수량

-------- ------------------------------ ----------

a001     마우스                                  0

a002     키보드                                  0

a003     모니터                                  0

-- 입고테이블)입고번호,상품코드, 입고일자,입고수량,입고단가 ,입고금액

 insert into 입고 values(입고_seq.nextval,'a003',sysdate,10,3000,30000);

SQL> select 상품코드,상품명,재고수량 from 상품;

상품코드      상품명                           재고수량

-------- ------------------------------ ----------

a001     마우스                                  0

a002     키보드                                  0

a003     모니터                                 10

insert into 입고 values(입고_seq.nextval,'a003',sysdate,20,3000,60000);

SQL> select 상품코드,상품명,재고수량 from 상품;

상품코드      상품명                           재고수량

-------- ------------------------------ ----------

a001     마우스                                  0

a002     키보드                                  0

a003     모니터                                 20   ??

SQL> update 상품 set 재고수량=0;

SQL> delete from 입고;

insert into 입고 values(입고_seq.nextval,'a003',sysdate,10,3000,30000);

insert into 입고 values(입고_seq.nextval,'a003',sysdate,20,3000,60000);

SQL> select 상품코드, 상품명, 재고수량 from 상품;

상품코드       상품명                           재고수량

-------- ------------------------------ ----------

a001     마우스                                  0

a002     키보드                                  0

a003     모니터                                 30

2. 수정트리거 (입고 테이블에 상품의 입고수량이 변경되었을 때 상품케이블의 재고수량변경)

 --> 예) 종업원의 실수로 잘못입력된 

               입고번호 3에 대해 입고수량 10 ---> 20으로 변경시

            

               해당 상품코드에 대한 재고수량이 30 ---> 40으로 변경!!     

        

SQL> select 입고번호,상품코드, 입고수량 from 입고;

  입고번호        상품코드             입고수량

---------- -------- ----------

         3 a003             10

         4 a003             20

   -- [입고]테이블의 입고 수량 변경 -----> [상품]테이블의 재고수량 변경

   create or replace trigger product_update

   after update on 입고   -- 입고테이블에서 수정이 발생했을 때 

   for each row

   begin

     -- 이벤트 발생후 실행할 문장 정의!!(상품테이블의 재고 수정)

     update 상품 set 재고수량=  재고수량  -  :old.입고수량  + :new.입고수량

                          -- 30     -    10 +           20

          where 상품코드=  :old.상품코드 ;     

   end;

   /

   

update 입고 set 입고수량=20

         where 입고번호=3;

SQL> select 상품코드,상품명,재고수량 from 상품;

상품코드       상품명                           재고수량

-------- ------------------------------ ----------

a001     마우스                                  0

a002     키보드                                  0

a003     모니터                                 40

SQL> select 입고번호, 상품코드, 입고수량 from 입고;

  입고번호        상품코드             입고수량

---------- -------- ----------

         3 a003             20

         4 a003             20

3. 삭제 트리거

   ---> 입고번호 4에 대한 삭제!!

        (다른 대리점에 전달되어야 하는 제품이 잘못 배달되었음)

   ---> [상품]테이블 재고수량 40 ---> 20 변경     

   create or replace trigger product_delete

   after delete on 입고 --입고테이블에서 삭제 발생시

   for each row

   begin

      -- 상품테이블의 재고 수량변경!!

      update 상품 set 재고수량= 재고수량 - :old.입고수량

                           --  40  -    20   

      where 상품코드=:old.상품코드;

   end;

   /

 delete from 입고 where 입고번호=4;

SQL> select 상품코드,상품명,재고수량 from 상품;

상품코드      상품명                           재고수량

-------- ------------------------------ ----------

a001     마우스                                  0

a002     키보드                                  0

a003     모니터                                 20

 delete from 입고 where 입고번호=3;

SQL> select 상품코드,상품명,재고수량 from 상품;

상품코드      상품명                           재고수량

-------- ------------------------------ ----------

a001     마우스                                  0

a002     키보드                                  0

a003     모니터                                  0