[DBMS/Oracle] 트리거(Trigger)

[DBMS/Oracle] 트리거(Trigger)

트리거(Trigger)

(사용형식)
  CREATE [OR REPLACE] TRIGGER 트리거명 
    BEFORE|AFTER INSERT|DELETE|UPDATE ON 테이블명
   [FOR EACH ROW]
   [WHEN 조건]
 [DECLARE
   선언영역]
  BEGIN
   트리거 본문;
   
  [EXCEPTION
    예외처리블록]
    
  END;

- 'BEFORE|AFTER' : 트리거 본문이 실행될 시점으로(timing) EVENT(INSERT,UPDATE,DELETE)발생 전 또는 발생 후를 설정
- 'INSERT|DELETE|UPDAT' : 이벤트를 의미하며 'ON 테이블'에 DML동작이 발생되기 전 또는 후에 트리거 본문이 실행되는 요인을 결정. OR연산자로 복합사용 가능
- 'FOR EACH ROW' : 행단위 트리거를 생성
- 'WHEN 조건' : EVENT 발생시 트리거가 수행되는데 좀 더 구체적으로 트리거 발생을 구체화 시킬때 사용하며, 반드시 행단위 트리거에서만 사용 가능

 

A. 트리거의 종류

1).문장단위 트리거

- 이벤트 결과집합의 행의 수에 관계없이 한 번만 트리거 발생
- FOR EACH ROW가 생략되면 문장단위 트리거로 취급

 

2) 행단위 트리거

- FOR EACH ROW를 사용한 트리거
- 이벤트의 결과 집합내의 행마다 트리거 본문 수행
- 하나의 트리거가 완료되지 않은 상태에서 또 다른 트리거를 호출하거나, 두 테이블이 서로 영향을 미치는 트리거는 실행 오류(mutable error)

사용예) 사원테이블에서 사원번호 125번 사원을 삭제(퇴직처리)하시오. 삭제 전 퇴직자테이블에 
        사원번호,사원명,부서코드,직무코드를 입력하는 트리거를 작성하시오.
  
  CREATE OR REPLACE TRIGGER TG_DEL_EMP      
    BEFORE DELETE on T_EMP
    FOR EACH ROW
  BEGIN
    INSERT INTO RETIRE VALUES(:old.EMPLOYEE_ID,:old.EMP_NAME);
  END;

  DELETE FROM T_EMP
    WHERE EMPLOYEE_ID=149;

--  DROP TRIGGER TG_DEL_EMP

 

사용예) LPROD 테이블에서 LPROD_DI=10인 자료를 삭제한 후 
        '자료가 정상적으로 삭제 되었습니다.'라는 메시지를 출력하는 트리거를 작성하시오.
        
  CREATE OR REPLACE TRIGGER TD_DEL_LPROD01
    AFTER DELETE ON LPROD
  BEGIN
    DBMS_OUTPUT.PUT_LINE('자료가 정상적으로 삭제 되었습니다.');
  END;

  DELETE FROM LPROD WHERE LPROD_ID>=7;
  SELECT * FROM LPROD;
  
--  ROLLBACK;
--  COMMIT;
  
--
  SELECT * FROM LPROD;

 

**ORDER 테이블과 ORDER_DETAIL 테이블에 다음 자료를 입력하시오.

  [ORDER 테이블]
  ------------------------------------------------------------
  ORDER_ID           ORDER_DATE         MEM_ID
  ------------------------------------------------------------
  20230209001        2023/02/09         b001 
  20230209002        2023/02/09         f001

  INSERT INTO ORDERS VALUES('20230209001', TO_DATE('20230209'), 'b001');
  INSERT INTO ORDERS VALUES('20230209002', TO_DATE('20230209'), 'f001');
  
  [ORDER_DETAIL 테이블]
  ------------------------------------------------------------
  ORDER_ID           PROD_ID         ORDER_QTY
  ------------------------------------------------------------
  20230209001        P201000001          2
  20230209001        P201000010          5
  20230209001        P201000016          1
  20230209001        P202000001          2
  20230209002        P302000003          2
  20230209002        P102000005          1
  
  insert into order_detail(order_id,prod_id,order_qty)
    values('20230209001','P201000001',2);
  insert into order_detail(order_id,prod_id,order_qty)
    values('20230209001','P201000010',5);
  insert into order_detail(order_id,prod_id,order_qty)
    values('20230209001','P201000016',1);
  insert into order_detail(order_id,prod_id,order_qty)
    values('20230209001','P202000001',2);
  insert into order_detail(order_id,prod_id,order_qty)
    values('20230209002','P302000003',2);
  insert into order_detail(order_id,prod_id,order_qty)
    values('20230209002','P102000005',1);   
    
  COMMIT;

 

B. 트리거 의사레코드와 트리거 함수

1) 트리거 의사레코드

- 행단위 트리거에서만 사용

--------------------------------------------------------------------------------------
  의사레코드   설명
--------------------------------------------------------------------------------------
    :NEW       insert, update event에 사용되며 데이터가 삽입(갱신)될때
               새롭게 입력된 자료를 지칭함. delete에 사용하면 모든 컬럼이 null로 설정됨
    :OLD       insert, update event에 사용되며 데이터가 삭제(갱신)될때
               새롭게 입력된 자료를 지칭함. delete에 사용하면 모든 컬럼이 null로 설정됨

 

2) 트리거 함수

- 이벤트가 복수개 사용되는 트리거에 사용

--------------------------------------------------------------------------------------
  트리거 함수  설명
--------------------------------------------------------------------------------------
  inserting    트리거 이벤트가 insert이면 true
  updating     트리거 이벤트가 update이면 true
  deleting     트리거 이벤트가 delete이면 true

 

사용예) orders테이블에서 오늘 날짜의 주문 중 'b001' 회원이 주문을 취소한 경우  
        orders와 order_detail테이블의 변동 사항을 처리하는 트리거를 작성하시오.
        
  create or replace trigger tg_del_order
    before delete on orders
    for each row
  declare
    l_order_id ORDERS.ORDER_ID%type;
  begin
    l_order_id:=(:OLD.ORDER_ID);
    
    delete from order_detail
     where ORDER_ID=l_order_id;
  end;

--  delete from orders where order_id='20230209001';

 

사용예) 오늘이 2020년 6월 15일이라고 가정하고 다음 입고자료를 처리하는 트리거를 작성하시오.

--시스템 시간 조정
  [입고자료]
  ------------------------------------------------
    입고일    입고상품코드    수량      단가
  ------------------------------------------------
  2020-06-15  p102000004       10     990000
  2020-06-15  p101000003       20     440000
  2020-06-15  p202000009       15      28500

create or replace trigger tg_update_buy
  after  insert or update or delete on buyprod
  for each row
declare
  l_qty number:=0;
  l_pid prod.prod_id%type;
  l_date date;
begin
  if inserting then
     l_qty:=(:new.buy_qty);
     l_pid:=(:new.buy_prod);
     l_date:=(:new.buy_date);
  elsif updating then
     l_qty:=(:new.buy_qty - :old.buy_qty);
     l_pid:=(:new.buy_prod);
     l_date:=(:new.buy_date);  
  elsif deleting then
     l_qty:= -(:old.buy_qty);
     l_pid:=(:old.buy_prod);
     l_date:=(:old.buy_date);  
  end if;
  
  update remain a
     set a.REMAIN_I=a.REMAIN_I+l_qty,
         a.REMAIN_J_99=a.REMAIN_J_99+l_qty,
         a.REMAIN_DATE=l_date
   where a.prod_id=l_pid;
   
  exception when others then
    dbms_output.put_line('예외발생 : '||sqlerrm);
end;
 
[매출처리 : 재고수불테이블 update,MEMBER테이블 update => 트리거 본문]
create or replace trigger tg_update_cart
  after  insert or update or delete on cart
  for each row
declare
  l_qty number:=0;
  l_pid prod.prod_id%type;
  l_date date;
  l_mileage number:=0;
  l_mem_id member.mem_id%type;
begin
  if inserting then
     l_qty:=(:new.cart_qty);
     l_pid:=(:new.cart_prod);
     l_date:=(:new.cart_date);
     l_mem_id:=(:new.cart_member);  -- 마일리지 변경
  elsif updating then
     l_qty:=(:new.cart_qty - :old.cart_qty);
     l_pid:=(:new.cart_prod);
     l_date:=(to_date(substr(:new.cart.no,1,8)));  
     l_mem_id:=(:new.cart_member);
  elsif deleting then
     l_qty:= -(:old.cart_qty);
     l_pid:=(:old.cart_prod);
     l_date:=(to_date(substr(:old.cart.no,1,8)));  
     l_mem_id:=(:old.cart_member);
  end if;

-- 재고처리  
  update remain a
     set a.REMAIN_I=a.REMAIN_I+l_qty,
         a.REMAIN_J_99=a.REMAIN_J_99-l_qty,
         a.REMAIN_DATE=l_date
   where a.prod_id=l_pid;

-- 마일리지 변경
  select prod_mileage*l_qty into l_mileage
    from prod
   where prod_id=l_pid;
   
  update member
     set mem_mileage+l_mileage
   where mem_id=mem_id;
    
  exception when others then
    dbms_output.put_line('예외발생 : '||sqlerrm);
end;

 

함수생성 : 장바구니번호(cart_no)를 자동으로 생성하는 함수
     
create or replace function fn_create_cart_no(p_date in date, p_mem_id in member.mem_id%type)
  return varchar2
is
  l_cart_no cart.cart_no%type;
  l_flag number:=0;
  l_no varchar2(8):=to_char(p_date,'yyyymmdd');
  l_member member.mem_id%type;
begin
  select count(*) into l_flag
    from cart
   where cart_no like l_no||'%';

  if l_flag=0 then
     l_cart_no:=l_no||trim('00001');
  else
    select max(cart_no) into l_cart_no
      from cart
     where cart_no like l_no||'%';
     
     select cart_member into l_member
       from cart
      where cart_no=l_cart_no;
      
     if l_member!=p_mem_id then
        l_cart_no:=l_cart_no+1;
     end if;
     
  end if;
  
  return l_cart_no;
end;

 

사용예) 오늘이 2020년 7월 28일인 경우 다음 매출자료를 cart 테이블에 저장하는 프로시져를 작성하시오.

-- 재료수불테이블 변경 -> 마일리지 변경
  create or replace procedure proc_insert_cart(p_cmem in member.mem_id%type,
    p_pid in prod.prod_id%type, p_qty number)
  is 
  begin
    insert into cart values(p_cmem,fn_create_cart_no(sysdate,p_cmem),p_qty,p_pid);
    commit;
  end;

  ------------------------------------
   회원번호 상품번호           수량
  ------------------------------------
   b001     P102000005          1
   d001     P102000004          2
    ""      P101000003          1
    ""      P202000009          2

**prod_mileage : (매출단가 - 매입단가)*0.1%

select prod_id,
       round((prod_price-prod_cost)*0.001)
  from prod;

update prod
   set prod_mileage=round((prod_price-prod_cost)*0.001);
   
update prod
   set prod_mileage=10
 where prod_mileage<10;

commit;
  
(실행) 기초 매입 매출 현재고 
2020 P102000005 8 38 18+10 28-10 2020/07/31 mileage 130
2020 P102000005 8 38    28    18 2020/07/31 mileage 130
2020 P102000009 9 22  6+2 25-2 2020/04/30 mileage 16
2020 P102000009 9 22    8   23 2020/04/30 mileage 16

  execute proc_insert_cart('d001','P102000005',10);
  execute proc_insert_cart('d001','P102000009',2);