Trigger

• 어떤 이벤트가 발생하며 자동적으로 실행되는 프로시져 일종
• 사용형식

 CREATE [OR REPLACE] TRIGGER 트리거명
   BEFORE|AFTER INSERT|UPDATE|DELETE
   --before(10%↓), atfer(90%↑) 타이밍? Trigger 실행되는 내용이 명령문인데
   --insert, update, delete Event가 발생되었을때  그전에(before) 그후에(after)
   ON 테이블명 -- Event가 발생된 테이블명, Trigger에 사용되어질 테이블이 아님
   [FOR EACH ROW] --행(Row)단위 Trigger, 행 하나씩 다 Trigger가 발동
                  --문장단위 트리거는 한쿼리에 한번만 발동
                  -- 생략 -> 문장단위, 기술 -> 행단위
   [WHEN 조건] --트리거 조건, 트리거를 좀더 Detail하게 발동할때
 BEGIN
   명령문(들);
 END;

• 1) 트리거 유형
  • (1) 문장단위 트리거 : 이벤트가 발생되었을 때 한번만 트리거 발생. 'FOR EACH ROW'생략
    :NEW, :OLE와 같은 의사레코드 사용 불가.
  • (2) 행단위 트리거 : 이벤트에 의해 여러 행이 영향 받는 경우 각 행마다 트리거 발생.
    'FOR EACH ROW' 기술해야 하고, :NEW, :OLD와 같은 의사레코드 사용 가능.
    ** 하나의 트리거가 종료 되지 않은 상태에서 다른 트리거 호출 불가, 무결성, 일관성을 유지하기 위해서
• 2)트리거 조건
  • 행단위 트리거에서만 사용 가능
  • 이벤트가 발생된 때 보다 구체적인 데이터검색 조건 부여할 때 기술
    예) 분류테이블에 새로운 데이터 하나를 입력하라. 입력된 후 '새로운 분류코드가 입력됨'을 출력하는 트리거를 작성하시오.
    -- event: 새로운 데이터 하나 입력, trigger: '새로운 분류코드가 입력됨'을 출력

 (새로운 분류코드 - LPROD_ID : 10, LPROD_GU : 'P501', LPROD_NM : '농산물')
  **(trigger)**   
 create or replace trigger tg_lprod01
   after insert on lprod
   --for each row 한번만 할꺼니까 생략
 begin
   dbms_output.put_line('새로운 분류코드가 입력됨');
 end;
  **(Event)**
 insert into lprod values(10,'P501','농산물');
 select * from lprod;

☆퇴직자 정보를 저장하는 테이블을 생성하시오.

---

테이블명 : RETIRE

---

속성
employee_id number(6,0) FK/PK
emp_name varchar2(80 byte) FK
department_id number(6,0) FK
job_id varchar2(10 byte) fk
r_date date

---

create table RETIRE(
   employee_id     number(6,0),
   emp_name        varchar2(80),
   department_id   number(6,0),
   job_id          varchar2(10),
   r_date          date    not null, 
 constraint pk_retire primary key(employee_id, r_date),
 constraint fk_retire foreign key(employee_id)
   references employees(employee_id));
--drop table retire; 잘못만들었을때 활용

예) 사원번호 105번 사원을 퇴직처리하시오. 단 퇴직자는 사원테이블에서
-- 사원테이블의 퇴직일자를 오늘 날짜로 변경
퇴직자 정보를 퇴직자테이블(RETIRE)에 보관하시오.

 (Trigger)
 create or replace trigger tg_retire01
   before update on employees
   for each row
 begin
   insert into retire(employee_id, emp_name, department_id, job_id, r_date)    
   	values(:old.employee_id,:old.emp_name,:old.department_id,
    		:old.job_id,sysdate);
 end;
 (Event)
 update employees
    set retire_date = sysdate
  where employee_id = 105;

select * from employees where employee_id = 105;
select * from retire;

alter table remain1
 add constraint pk_remain1
     primary key(remain_year, remain_prod);

alter table remain1
 add constraint fk_remain1
     foreign key(remain_prod) references prod(prod_id)
      on delete cascade; --연쇄 삭제 제약조건 설정

예시) 오늘이 2005년 8월 1일이라고 가정하고 제품코드 'P201000007'인 상품을 회원번호가 'r001'인 회원이 5개를 구매 했다고 하자 cart테이블에 해당 데이터를 추가하고 재고 수불테이블을 변경하시오.

 (Trigger 생성)
 create or replace trigger tg_remain02 
   after insert on cart
   for each row
 declare
   v_qty number := 0;
   v_prod_id prod.prod_id%type;
 begin
   v_qty := :new.cart_qty;
   v_prod_id := :new.cart_prod;
   update remain
      set remain_o = remain_o + v_qty,
          remain_j_99 = remain_j_99 - v_qty,
          remain_date = to_date('20050801')
    where remain_year = '2005'
      and remain_prod = v_prod_id;
 end;
(Event 수행전)
  select * from remain
  where remain_prod = 'P201000007'
    and remain_year = '2005'

(Event)
insert into cart
 values('r001','2005080100001','P201000007',5);
(Event확인)
select * from remain
where remain_prod = 'P201000007'
  and remain_year = '2005'

★ 의사(pseudo)레코드

• 행단위 트리거에만 사용 가능
  :NEW INSERT,UPDATE문에 사용.
  데이터가 삽입(갱신)될 때 새로 들어온 값, DELETE에는 모든 값이 NULL이다.
  :OLD DELETE,UPDATE문에 사용.
  데이터가 삭제(갱신)될 때 존재하고 있는 값, DELETE에는 INSERT에는 모든 값이 NULL이다.
  ★ 트리거 함수
  1) INSERTING : 트리거 문장이(Event) INSERT이면 참
  2) UPDATING : 트리거 문장(Event)이 UPDATE이면 참
  3) DELETING : 트리거 문장(Event)이 DELETE이면 참
  예시)

 create or replace trigger tg_remain02 
   after insert or update or delete 
   on cart
   for each row
 declare
   v_qty number := 0;
   v_prod_id prod.prod_id%type;
 begin
   -- 3가지 유형의 이벤트가와도 update문 하나만 쓸수 있게 변수를 둠??
   IF INSERTING THEN
     v_qty := :new.cart_qty;
     v_prod_id := :new.cart_prod;
   elsif UPDATING THEN
     v_qty := :new.cart_qty - :old.cart_qty; 
     v_prod_id := :new.cart_prod;
   elsif DELETING THEN
     v_qty := :old.cart_qty;
     v_prod_id := :old.cart_prod;
   end if;
   update remain
      set remain_o = remain_o + v_qty,
          remain_j_99 = remain_j_99 - v_qty,
          remain_date = to_date('20050801')
    where remain_year = '2005'
      and remain_prod = v_prod_id;
 end;
 (ㅇㅣ벤트)
 update cart
    set cart_qty = cart_qty + 2
  where cart_no = '2005080100001'
    and cart_prod = 'P201000007'; 
--
select * from cart where cart_no = '2005080100001';
select * from remain where remain_prod= 'P201000007';
 (8개를 반품)
update cart
   set cart_qty = cart_qty - 8
 where cart_no = '2005080100001'
   and cart_prod = 'P201000007';