오라클 기초#11(Trigger)

[ 트리거(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;