230807 SQL 강의

--<실습하기> 급여 정보를 자동 삭제하는 트리거 작성하기
--사원이 삭제되면 그 사원의 급여 정보도 자동 삭제되는 트리거를 작성해 보도록 합시다.
--1. 이번에는 사원 테이블의 로우를 삭제해보자.
DELETE FROM EMP01 WHERE EMPNO=2;

무결성 제약조건 위배 - 자식 레코드 발견

--2. 사원번호 2를 급여 테이블에서 참조하고 있기 때문에 삭제가 불가능하다. 
--사원이 삭제되려면 그 사원의 급여 정보도 급여 테이블에서 삭제되어야 합니다. 
--사원의 정보가 제거 될 때 그 사원의 급여 정보도 함께 삭제하는 내용을 트리거로 작성하도록 합시다. 
CREATE OR REPLACE TRIGGER TRG_03
AFTER DELETE ON EMP01
FOR EACH ROW
BEGIN
DELETE FROM SAL01 WHERE EMPNO=:old.EMPNO;
END;
/

--3. 사원 테이블에 로우를 삭제해 봅시다. 
DELETE FROM EMP01 WHERE EMPNO=2;
SELECT * FROM EMP01;

SELECT * FROM SAL01;

--DROP TIGGER 다음에 삭제할 트리거 명을 기술합니다. 
DROP TRIGGER TRG_03;

---

--<실습하기> 입고 트리거 작성하기
--입고 테이블에 상품이 입력되면 입고 수량을 상품 테이블의 재고 수량에 추가하는 트리거를 작성해 봅시다. 
--1. 상품 테이블을 생성합시다. 
CREATE TABLE PRODUCT(
	PROD_ID    VARCHAR2(6) PRIMARY KEY,	--상품코드
	PROD_NAME  VARCHAR2(12) NOT NULL,     --상품명
	PROD_JEJO  VARCHAR(12),               --제조사
	PROD_SALE  NUMBER(8),				--소비자가격
	PROD_JAEGO NUMBER DEFAULT 0		--재고수량
);

--2. 입고 테이블을 생성합시다. 
CREATE TABLE IPGO(
	IPGO_ID NUMBER(6) PRIMARY KEY,				  --입고번호
	PROD_ID VARCHAR2(6) REFERENCES PRODUCT(PROD_ID), --상품코드
	IPGO_DAY DATE DEFAULT SYSDATE,				  --입고일자
	IPGO_QTY NUMBER(6),						  --입고수량
	IPGO_COST NUMBER(8),						  --입고단가
	IPGO_AMOUNT NUMBER(8)					  --입고금액
);

--3. 상품테이블의 재고수량 컬럼을 통해서 실질적인 트리거의 적용 예를 살펴보도록 하겠습니다. 
--우선 상품 테이블에 다음과 같은 샘플 데이터를 입력해봅시다.
INSERT INTO PRODUCT(PROD_ID, PROD_NAME, PROD_JEJO, PROD_SALE)
	VALUES('A00001','세탁기', 'LG', 500); 
INSERT INTO PRODUCT(PROD_ID, PROD_NAME, PROD_JEJO, PROD_SALE)
	VALUES('A00002','컴퓨터', 'LG', 700);
INSERT INTO PRODUCT(PROD_ID, PROD_NAME, PROD_JEJO, PROD_SALE)
	VALUES('A00003','냉장고', '삼성', 600);
    
SELECT * FROM PRODUCT;

--4. 입고 테이블에 상품이 입력되면 입고 수량을 상품 테이블의 재고 수량에 추가하는 트리거 작성
-- 입고 트리거
CREATE OR REPLACE TRIGGER TRG_04
AFTER INSERT ON IPGO
FOR EACH ROW
BEGIN
UPDATE PRODUCT
SET PROD_JAEGO = PROD_JAEGO + :NEW.IPGO_QTY
WHERE PROD_ID = :NEW.PROD_ID;
END;
/

--5. 트리거를 실행시킨 후 입고 테이블에 행을 추가합니다. 
--입고 테이블에는 물론 상품 테이블의 재고 수량이 변경됨을 확인할 수 있습니다. 
INSERT INTO IPGO(IPGO_ID, PROD_ID, IPGO_QTY, IPGO_COST, IPGO_AMOUNT) 
    VALUES(1, 'A00001', 5, 320, 1600);

SELECT * FROM IPGO;

SELECT * FROM PRODUCT;

--6. 입고 테이블에 상품이 입력되면 자동으로 상품 테이블의 재고 수량이 증가하게 됩니다. 
--입고 테이블에 또 다른 상품을 입력합니다. 
INSERT INTO IPGO(IPGO_ID, PROD_ID, IPGO_QTY, IPGO_COST, IPGO_AMOUNT) 
     VALUES(2, 'A00002', 10, 680, 6800);

SELECT * FROM IPGO;

SELECT * FROM PRODUCT;

---

--이미 입고된 상품에 대해서 입고 수량이 변경되면 상품 테이블의 재고수량 역시 변경되어야 합니다. 
--이를 위한 갱신 트리거 작성해 봅시다. 
--1. ED 다음에 파일이름을 입력하여 다음과 같이 입력하시오.
-- 갱신 트리거
CREATE OR REPLACE TRIGGER TRG_05
AFTER UPDATE ON IPGO
FOR EACH ROW
BEGIN
UPDATE PRODUCT
SET PROD_JAEGO = PROD_JAEGO + (-:old.IPGO_QTY+:new.IPGO_QTY) 
WHERE PROD_ID = :new.PROD_ID;
END;
/

--2. 입고 번호 3번은 냉장고가 입고된 정보를 기록한 것으로서 입고 번호 3번의 입고수량을 10으로 변경하였더니 냉장고의 재고 수량 역시 15로 변경되었습니다.
INSERT INTO IPGO(IPGO_ID, PROD_ID, IPGO_QTY, IPGO_COST, IPGO_AMOUNT) 
     VALUES(3, 'A00003', 10, 220, 2200);
INSERT INTO IPGO(IPGO_ID, PROD_ID, IPGO_QTY, IPGO_COST, IPGO_AMOUNT) 
     VALUES(4, 'A00003', 15, 220, 3300);
SELECT * FROM IPGO;

SELECT * FROM PRODUCT;

UPDATE IPGO 
   SET IPGO_QTY = 5, IPGO_AMOUNT = 1100
 WHERE IPGO_ID = 3;

SELECT * FROM IPGO ORDER BY IPGO_ID;

SELECT * FROM PRODUCT;

---

--<실습하기> 삭제 트리거 작성하기
--입고 테이블에서 입고되었던 상황이 삭제되면 상품 테이블에 재고수량에서 삭제된 입고수량 만큼을 빼는 삭제 트리거 작성해 봅시다. 
--1. ED 다음에 파일이름을 입력하여 다음과 같이 입력하시오.
--삭제트리거
CREATE OR REPLACE TRIGGER TRG04
AFTER DELETE ON IPGO
FOR EACH ROW
BEGIN
UPDATE PRODUCT
SET PROD_JAEGO = PROD_JAEGO - :old.IPGO_QTY
WHERE PROD_ID = :old.PROD_ID;
END;
/

--2. 입고 번호 3번은 냉장고가 입고된 정보를 기록한 것으로서 입고 번호가 3번인 행을 삭제하였더니 냉장고의 재고 수량 역시 5로 변경되었습니다.
SELECT * FROM IPGO;

SELECT * FROM PRODUCT;

DELETE IPGO WHERE IPGO_ID=3;

SELECT * FROM IPGO ORDER BY IPGO_ID;

SELECT * FROM PRODUCT;

---