[ 트리거(Trigger) ]
- 데이터베이스가 미리 정해은은 조건을 만족하거나 어떤 동작이 수행되면 자동적으로 수행되는 동작(특정 동작에 대한 이벤트로 인해서 실행되는 프로시져)
- 형식)
CREATE OR REPLACE TIGGER 트리거이름
TIMING [BEFORE|AFTER] EVENT[INSERT|UPDATE|DELETE] ON 테이블명
[FOR EACH ROW]
DECLARE
변수선언;
..
BEGIN
실행문장;
..
END;
/
예) DEPT테이블에 부서가 추가(INSERT)될때 부서가 추가되었음을 출력하는 트리거
SQL> SET SERVEROUTPUT ON
-- 트리거만들기
CREATE OR REPLACE TRIGGER PRINT_MSG
AFTER INSERT ON DEPT -- DEPT테이블에 INSERT가 수행된 후 자동호출
BEGIN
DBMS_OUTPUT.PUT_LINE('부서가 추가되었어요');
END;
/
-- DEPT테이블에 데이터 추가해보기
SQL> INSERT INTO DEPT VALUES(91,'개발부','종로');
부서가 추가되었어요 ==> 트리거가 자동 호출됨
-- 트리거 제거하기
SQL> DROP TRIGGER PRINT_MSG;
예) 예제테이블
#상품테이블
CREATE TABLE ITEM
(
CODE CHAR(6) PRIMARY KEY, -- 상품코드
NAME VARCHAR2(12) NOT NULL, --상품이름
COMPANY VARCHAR2(12) , --제조회사
PRICE NUMBER(8), --가격
CNT NUMBER DEFAULT 0 --재고수량
);
#입고테이블
CREATE TABLE WAREHOUSE
(
NUM NUMBER(6) PRIMARY KEY, --입고번호
CODE CHAR(6) REFERENCES ITEM(CODE), --상품코드
PUTDATE DATE DEFAULT SYSDATE,--입고날짜
PUTCNT NUMBER(6), --입고수량
PUTPRICE NUMBER(6),--입고단가
TOTALPRICE NUMBER(8) --입고총액
);
SQL> INSERT INTO ITEM(CODE,NAME,COMPANY,PRICE)
2 VALUES('A01','냉장고','삼성',700000);
SQL> INSERT INTO ITEM(CODE,NAME,COMPANY,PRICE)
2 VALUES('A02','세탁기','LG',500000);
SQL> SELECT * FROM ITEM;
CODE NAME ... PRICE CNT
----- ------- ------ ---
A01 냉장고 ... 700000 0
A02 세탁기 ... 500000 0
1) INSERT 트리거
입고테이블에 상품이 입고(INSERT)되면 자동으로 ITEM테이블의 해당상품의 재고수량이 증가(UPDATE)되는 트리거 작성
CREATE OR REPLACE TRIGGER TRG_ADDITEM
AFTER INSERT ON WAREHOUSE
FOR EACH ROW
BEGIN
UPDATE ITEM SET CNT=CNT+:NEW.PUTCNT
WHERE CODE=:NEW.CODE;
END;
/
SQL> INSERT INTO WAREHOUSE
2 VALUES(1,'A01',SYSDATE,10,1000,10000);
1 row created.
SQL> SELECT * FROM WAREHOUSE;
NUM CODE PUTDATE PUTCNT PUTPRICE TOTALPRICE
--- ---- -------- ------ -------- ----------
1 A01 12/11/23 10 1000 10000
==> 입고테이블에 A01제품이 추가되었으므로 트리거가 실행되어 상품테이블의 CNT수량이 입고수량만큼 증가됨
SQL> SELECT * FROM ITEM;
CODE NAME COMPANY PRICE CNT
---- ------ ------- ------ ---
A01 냉장고 삼성 700000 10
A02 세탁기 LG 500000 0
2) 삭제트리거
(입고테이블에 입고되었던 상황이 삭제되면 상품테이블에 재고수량에서 삭제된 입고수량만큼 삭제하는 트리거)
CREATE OR REPLACE TRIGGER TRG_DELETE
AFTER DELETE ON WAREHOUSE
FOR EACH ROW
BEGIN
UPDATE ITEM
SET CNT=CNT-:OLD.PUTCNT
WHERE CODE=:OLD.CODE;
END;
/
SQL> SELECT * FROM WAREHOUSE;
NUM CODE PUTDATE PUTCNT PUTPRICE TOTALPRICE
--- ---- -------- ------ -------- ----------
1 A01 12/11/23 10 1000 10000
2 A01 12/11/23 5 1000 10000
SQL> DELETE FROM WAREHOUSE WHERE CODE='A01';
2 rows deleted.
--> 삭제트리거가 실행되면서 ITEM테이블의 해당 상품의 재고수량이 감소됨
SQL> SELECT * FROM ITEM;
CODE NAME COMPANY PRICE CNT
---- ------- ------- ------ ---
A01 냉장고 삼성 700000 0
A02 세탁기 LG 500000 0
3) 갱신트리거
- 이미 입고된 상품에 대해서 입고 수량이 변경되면 상품 테이블의 재고 수량이 변경되는 트리거
CREATE OR REPLACE TRIGGER TRG_UPDATE
AFTER UPDATE ON WAREHOUSE
FOR EACH ROW
BEGIN
UPDATE ITEM SET CNT=CNT+(-:OLD.PUTCNT+:NEW.PUTCNT)
WHERE CODE=:NEW.CODE;
END;
/
SQL> SELECT * FROM WAREHOUSE;
NUM CODE PUTDATE PUTCNT PUTPRICE TOTALPRICE
--- ---- -------- ------ -------- ----------
1 A01 12/11/23 10 1000 10000
2 A01 12/11/23 5 1000 10000
SQL> SELECT * FROM ITEM;
CODE NAME COMPANY PRICE CNT
---- ------- ------- ------- ----
A01 냉장고 삼성 700000 15
A02 세탁기 LG 500000 0
-- 입고테이블의 수량변경(UPDATE하기)
UPDATE WAREHOUSE
SET PUTCNT=3
WHERE NUM=1;
- 입고테이블의 수량이 변경됨
SQL> SELECT * FROM WAREHOUSE;
NUM CODE PUTDATE PUTCNT PUTPRICE TOTALPRICE
--- ---- -------- ------ -------- ----------
1 A01 12/11/23 3 1000 10000
2 A01 12/11/23 5 1000 10000
-- 상품테이블의 재고수량(CNT)도 자동 수량이 변경됨
SQL> SELECT * FROM ITEM;
CODE NAME COMPANY PRICE CNT
---- ------- ------- ------- ----
A01 냉장고 삼성 700000 8
A02 세탁기 LG 500000 0
Q1) DEPT테이블의 부서가 삭제될때 해당부서에 근무하는 사원들도 자동 삭제되는 트리거를 만들어 보세요..(COMMIT하지말고 ROLLBACK하세요)
CREATE OR REPLACE TRIGGER EMPDEL
AFTER DELETE ON DEPT
FOR EACH ROW
BEGIN
DELETE FROM EMP
WHERE DEPTNO=:OLD.DEPTNO;
END;
/
-- DEPT테이블에서 부서 제거
SQL> DELETE FROM DEPT WHERE DEPTNO=10;
-- 해당부서에 근무하는 사원들도 같이 삭제됨
SQL> SELECT * FROM EMP;