ORACLE 트리거(trigger)

/* [ 트리거 ] : 개발자가 호출해서 사용하는 것이 아니라, 특정 이벤트와 연동해서 그 이벤트, 조건이 발생시 자동적으로 수행하는 동작을 의미한다. (데이터베이스가 미리 정해 놓은 조건들을 만족하거나, 특정 이벤트가 발생하면 자동적으로 수행되는 동작(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