[PL/SQL] TRIGGER

TRIGGER 란?
- 어떤 테이블에 dml이 발생했을때 자동으로 실행되는 프로그램 (before 트리거, after 트리거)

 

테이블 생성

CREATE TABLE panmae_table (
    ID   NUMBER(3),
    dan  NUMBER(5),
    soo  NUMBER(5) 
);
--dan: 단가, soo: 수량
 
INSERT INTO panmae_table VALUES (1,1000,200);
INSERT INTO panmae_table VALUES (2,500,50);
SELECT * FROM panmae_table;

--금액 테이블
CREATE TABLE  keum_table (
  ID  NUMBER(3),
  keum NUMBER(10)
);
INSERT INTO keum_table VALUES (1,200000);
INSERT INTO keum_table VALUES (2,25000);
COMMIT;

 

- after 트리거 생성 예시

Q) 판매테이블에서 단가, 수량컬럼중 수량이 변경되면 금액테이블에 단가*수량이 자동으로 적용되는 trigger 프로그램을 작성하시오 

CREATE OR REPLACE TRIGGER kyesan_trigger
AFTER UPDATE OF soo ON panmae_table 
FOR EACH ROW
--컬럼이 하나 바뀔때마다 트리거 실행, 이 옵션 없으면 단 한번만 실행
BEGIN 
UPDATE keum_table
SET keum = :OLD.dan * :NEW.soo
WHERE  ID= :OLD.ID;        
END;
/
 --이제 이거 실행하면 자동으로 트리거가 실행됨
SELECT * FROM keum_table;
UPDATE PANMAE_TABLE
SET SOO=57
WHERE ID=1;
SELECT * FROM keum_table;

 

- before 트리거 생성 예시

Q) 토요일과 일요일에는 작업하지 못하도록 trigger프로그램을 작성하시오

SET SERVEROUTPUT ON 
CREATE OR REPLACE TRIGGER no_working
BEFORE UPDATE OR INSERT OR DELETE ON panmae_table 
--select x
BEGIN 
IF(to_char(sysdate,'day') IN ('토요일','일요일')) THEN  
raise_application_error(-20000,'주말에는 작업을 수행할수 없습니다');
ELSE   
dbms_output.put_line ('Have a good time');
END IF;
END;
/ 
 --업데이트 실행(토,일 요일에 수행시 실행 안됨)
UPDATE panmae_table 
SET soo=300
WHERE ID=1;
 
 --참고) raise_application_error(-20000,'Who ar you');
-- dbms_standard package에 있는 procedure
-- 호출자에게 application 전용의 에러메시지를 리턴함
-- -20000 : user-specified error number로 -20000 ~ -20999까지 가능함

 

 

- 연습문제

--수량 테이블
CREATE TABLE soo_table (
    id NUMBER(3), --상품아이디
    jin NUMBER(5), --진열된수량
    bo NUMBER(5) --보유수량
);
INSERT INTO soo_table VALUES(1,5,20);
INSERT INTO soo_table VALUES(2,3,50);
COMMIT;
--재고 테이블
CREATE TABLE jaeko_table (
    id NUMBER(3), --상품아이디
    jaeko NUMBER(10) --총재고량 = 진열된수량+보유수량
);
INSERT INTO jaeko_table VALUES(1,25);
INSERT INTO jaeko_table VALUES(2,53);
COMMIT;
SELECT * FROM soo_table;
SELECT * FROM jaeko_table;

Q) 다음 단계를 참고하여 마트의 수량테이블에서 진열된수량, 보유수량 컬럼중 보유수량이 변경되면 재고테이블에서 진열된수량 + 보유수량이 자동으로 계산되어 재고수량으로 적용되는 trigger 프로그램을 작성하시오

CREATE OR REPLACE TRIGGER jaeko_trigger
AFTER UPDATE OF bo ON soo_table
FOR EACH ROW
BEGIN
UPDATE jaeko_table
SET jaeko = :OLD.jin + :NEW.bo
WHERE id = :OLD.id;
END;
/
SELECT * FROM jaeko_table;
UPDATE soo_table SET bo = 50 WHERE id=1;
SELECT * FROM jaeko_table;