Oracle 21일차 - Trigger

2020.11.16(월)

Trigger (하나가 되면 연쇄적으로)

1) 데이터베이스에 미리 정해놓는 조건에 만족하면 자동으로 이벤트 처리

   (오라클에서 처리 => 자바에서는 처리 코드를 작성하지 않는다)

2) Trigger : DML(INSERT, UPDATE, DELETE)

3) 형식

CREATE (OR REPLACE) TRIGGER tri_name

BEFORE|AFTER (INSERT,UPDATE,DELETE) ON table_name

BEGIN

   TRIGGER 처리 (다른 테이블 처리)

END;

/

4) 삭제

DROP TRIGGER trigger_name

5) 수정

ALTER TRIGGER trigger_name => 수정과 동시에 생성

 

FUCNTION, PROCEDURE (AutoCommit(X))

TRIGGER (AutoCommit(O))

TRIGGER는 자동처리되서 commit을 주면 오류남

 

--상품
CREATE TABLE 상품(
    품번 NUMBER,
    항목명 VARCHAR2(100),
    단가 NUMBER
);
 
--입고
CREATE TABLE 입고(
    품번 NUMBER,
    수량 NUMBER,
    금액 NUMBER
);
 
--출고
CREATE TABLE 출고(
    품번 NUMBER,
    수량 NUMBER,
    금액 NUMBER
);
 
--재고
CREATE TABLE 재고(
    품번 NUMBER,
    수량 NUMBER,
    금액 NUMBER
);

 

INSERT INTO 상품 VALUES(100,'새우깡',1500);
INSERT INTO 상품 VALUES(200,'감자깡',1000);
INSERT INTO 상품 VALUES(300,'맛동산',2000);
INSERT INTO 상품 VALUES(400,'양파링',1800);
INSERT INTO 상품 VALUES(500,'고구마깡',1600);
 
-- 입고
INSERT INTO 입고 VALUES(100,2,1500);
 
-- 재고
INSERT INTO 재고 VALUES(100,2,3000);
COMMIT;

 

-- 자동 처리 (재고) 같은 상품이면 수량, 금액만바뀌고 새상품이면 INSERT됨
CREATE OR REPLACE TRIGGER 입고_trigger
AFTER INSERT ON 입고
FOR EACH ROW
DECLARE
    v_cnt NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_cnt
    FROM 재고
    WHERE 품번=:NEW.품번;
    --품번(재고)
    --:NEW.품번(입고)
    IF(v_cnt=0) THEN --새로운 상품 들어옴
        INSERT INTO 재고 VALUES(:NEW.품번,:NEW.수량,:NEW.금액*:NEW.수량);
    ELSE --재고에 있는 상품
        UPDATE 재고 SET
        수량=수량+:NEW.수량,
        금액=금액+(:NEW.수량*:NEW.금액)
        WHERE 품번=:NEW.품번;
    END IF;
END;
/

 

INSERT INTO 입고 VALUES(200,1,1000);
COMMIT;
 
SELECT * FROM 입고;
SELECT * FROM 재고;
INSERT INTO 입고 VALUES(100,3,1500);
COMMIT;

 

CREATE OR REPLACE TRIGGER 출고_Trigger
AFTER INSERT ON 출고
FOR EACH ROW
DECLARE
    v_cnt NUMBER;
    /*
        재고
        100 5 7500
        =====> 100 2 3000
        =====> 100 0 0 (X)
        출고
        INSERT INTO 출고 VALUES(100,3,1500)
    */
BEGIN
    SELECT 수량-:NEW.수량 INTO v_cnt
    FROM 재고
    WHERE 품번=:NEW.품번;
    IF (v_cnt=0) THEN
        DELETE FROM 재고
        WHERE 품번=:NEW.품번;
    ELSE
        UPDATE 재고 SET
        수량=수량-:NEW.수량,
        금액=금액-(:NEW.수량*:NEW.금액)
        WHERE 품번=:NEW.품번;
    END IF;
    
END;
/
 
SELECT * FROM 재고;
INSERT INTO 출고 VALUES(200, 1, 1000);
COMMIT;
 
DROP TRIGGER 입고_trigger;
DROP TRIGGER 출고_trigger;

 

PLS-00103 오류

PROCEDURE 을 슬래쉬('/')로 끝내면 된다

 

ORA-04098 오류

Trigger의 명이 중복되었기 때문에 삭제하고 다시 생성하면 된다

DROP TRIGGER 입고_trigger;

=> CREATE OR REPLACE TRIGGER 입고_trigger .......

 

---

자동 조회수 증가

DESC daum_movie;
 
ALTER TABLE daum_movie ADD hit NUMBER DEFAULT 0;
 
CREATE TABLE trigger_reply(
    no NUMBER,
    mno NUMBER,
    name VARCHAR2(34),
    msg CLOB,
    regdate DATE
);
 
CREATE OR REPLACE TRIGGER movie_trigger
AFTER INSERT ON trigger_reply
FOR EACH ROW
BEGIN
    UPDATE daum_movie SET
    hit=hit+1
    WHERE no=:NEW.mno;
END;
/
 
UPDATE daum_movie SET
hit=2
WHERE no=15;
COMMIT;
 
SELECT * FROM daum_movie WHERE no=15;