ORACLE 트리거(trigger)

스터디/DB(ORACLE)

ORACLE 트리거(trigger)

고무곰(GomuGom) 2017. 9. 21. 13:31

/*
  [ 트리거 ]
  : 개발자가 호출해서 사용하는 것이 아니라, 특정 이벤트와 연동해서 그 이벤트, 조건이 발생시
    자동적으로 수행하는 동작을 의미한다.
    (데이터베이스가 미리 정해 놓은 조건들을 만족하거나, 특정 이벤트가 발생하면 자동적으로
     수행되는 동작(PL/SQL 블럭)으로 오라클에서 자동적으로 실행되는 PL/SQL 블럭을 의미)
    
    - [ 트리거의 유형 ]
    // 여기선 dml트리거에 대해서만 알아봄
      - insert, update, delete의 결과로 실행되는 "<DML 트리거>"
      
    - ** 트리거는 commit, rollback을 수행할 수 없고, commit,rollback을 수행하는 함수도 사용할 수 없다.
    
    - [ 트리거 구문 형식 ]
    create (or replace) trigger 트리거이름
    timming[before | after] event [insert | update | delete]
    ON 테이블명
    
    begin
      실행명령;
    end;
    
    트리거 내에서는 new와 old키워드를 통해서 DML작업이 일어난 테이블의 필드값을 가지고 올 수 있는데
    :NEW.컬럼명: DML트리거의 수정 또는 삽입문 내에서 사용이 가능하다. -> SQL 반영 후의 컬럼 데이터
    :OLD.컬럼명 : DML트리거의 수정 또는 삭제문 내에서 사용이 가능하다. -> SQL 반영전의 컬럼 데이터
    를 의미하게 됩니다.
    
*/
-- EX)
-- 테스트용 테이블 하나 생성
create table sample_dept(
  dept_id number,
  dept_name varchar2(15),
  loc varchar2(10)
);
desc sample_dept;

-- 위의 sample_dept에 데이터가 insert될 때 자동으로 동작하는 트리거를 작성해보자.
create or replace trigger print_msg
-- sample_dept테이블에 insert작업 이후에 실행되도록
after insert ON sample_dept 
BEGIN
  DBMS_OUTPUT.PUT_line('부서가 추가되었습니다.');
END;
/
-- 화면 출력 가능하게 세팅
set serveroutput on;
-- 테이블에 insert작업 발생시킴
insert into sample_dept values(10,'마케팅부','서울');
-- 자동으로 print_msg 트리거가 동작하게됨
--------------------------------------------------------------------------------

-- 물건 관리를 위한 테이블
create table item(
  code char(6) primary key, -- 물품 코드
  name varchar2(15) not null,
  company varchar2(15),
  price number(8),
  cnt number default 0 -- 재고 수량
);

create table warehouse(
  num number(6) primary key, --물품 입고 번호
  code char(6),
  indate date default sysdate, -- 입고 날짜
  incnt number(6),
  inprice number(6),
  totalprice number(8),
  constraint fk_code foreign key(code) references item(code)
);

insert into item(code,name,company,price) values('c0001','선풍기','삼성',100000);
insert into item(code,name,company,price) values('c0002','에어컨','LG',50000);
select * from item;

-- 창고(warehouse)에 상품이 입고될 때마다 상품(item)의 수량이 늘어나도록!(
    --  재고수량이 자동으로 늘어나도록 !! 트리거로 작성해 보자.

-- 재고수량 갱신을 위한 트리거 생성
create or replace trigger cnt_add
after insert on warehouse
for each row -- 각 row마다 반복한다는 의미
  begin
    update item set cnt = cnt + :new.incnt -- new 선언은 insert문,update문에서만 사용가능
    -- new키워드를 통해 warehouse 테이블 데이터에접근할 수 있고, warehouse 테이블에 insert작업이 이루어진 후의
    -- 데이터를 가지고 온다는 의미이다.(new)
    where code = :new.code; 
  end;
/

insert into warehouse(num, code, incnt, inprice, totalprice)
values(1,'c0001',2,100000,200000);
select * from item;
select * from warehouse;

-- 창고에서 물품이 삭제될 때마다 수량을 줄이는 트리거
create or replace trigger cnt_sub
after delete on warehouse
for each row
  begin
    -- delete에서는 new가 아닌 old를 사용해야 함
    -- delete작업이 반영되고 나버리면 데이터가 없기 때문에 반영할 incnt값이 없으니까
    update item set cnt = cnt - :old.incnt
    where code = :old.code;
  end;
/

delete from warehouse where code = 'c0001';
select * from item;
--------------------------------------------------------------------------------

-- **update**
  -- 수식 주의
create or replace trigger cnt_update
after update on warehouse
  for each row
    begin
      -- +- 는 +와 - 작업을 둘 다 해준다는 의미
      --  update item set cnt = cnt +- :old.incnt + :new.incnt -- 기존의 incnt값을 빼주고, 새로운 incnt값을 더해준다는 의미
      -- ex) 기존에 incnt값이 5가 있었으면 기존에 있었던 5를 빼주고 새로 입력된 7로 갱신해주기 위해 7을 더한다는 의미 5-5+7
      -- 기존의 값을 없애고 새 값을 반영해주도록 update를 짜야한다.
    --  where code = :new.code;
      
     -- update item set cnt = cnt - :old.incnt + :new.incnt where code = :new.code;
      update item set cnt = :new.incnt where code = :new.code;
    end;
/

update warehouse set incnt = 11, inprice = 800000 where code = 'c0001';
select * from warehouse;
select * from item;

-- 조건을 이용한 EX)
Create Trigger testTrigger
    after insert or update or delete -- 삽입, 업데이트, 삭제 중 하나 발생 이후에 실행
    on emp -- emp 테이블에 적용
    for each row -- DML작업에 의해 변경된 각각의 튜플(행)에 대해서
    when (:new.sal > :old.sal) -- 새로 변경된 sal이 변경전의 sal보다 큰 튜플에 대해서만
    -- 아래의 작업을 진행하라.
    BEGIN
        if (inserting) then dbms_output.put_line('inserting'); -- 삽입이면 
        elsif (updating) then dbms_output.put_line('updating'); -- 수정이면
        elsif (deleting) then dbms_output.put_line('deleting'); -- 삭제이면
        end if;
    END;
    
    -- 따라서, DML에 의해 변경된 모든 튜플들 중에 when의 조건에 해당하는 튜플(행)들에
    -- ~작업을 하도록 되는 것

[ 적용한 트리거의 활성화/비활성화 설정 방법 ]

Alter Trigger 트리거명 ENABLE|DISABLE

저작자표시 비영리